How to specify a search path with SQL Alchemy and pg8000?

Question

I'm trying to connect to a postgres db using SQL Alchemy and the pg8000 driver. I'd like to specify a search path for this connection. With the Psycopg driver, I could do this by doing something like

engine = create_engine(
    'postgresql+psycopg2://dbuser@dbhost:5432/dbname',
    connect_args={'options': '-csearch_path={}'.format(dbschema)})

However, this does not work for the pg8000 driver. Is there a good way to do this?

Answer 1

You can use pg8000 pretty much in the same way as psycopg2, just need to swap scheme from postgresql+psycopg2 to postgresql+pg8000.

The full connection string definition is in the SQLAlchemy pg8000 docs:

postgresql+pg8000://user:password@host:port/dbname[?key=value&key=value...]

But while psycopg2.connect will pass kwargs to the server (like options and its content), pg8000.connect will not, so there is no setting search_path with pg8000.

Answer 2

The SQLAlchemy docs describe how to do this. For example:

from sqlalchemy import create_engine, event, text


engine = create_engine("postgresql+pg8000://postgres:postgres@localhost/postgres")

@event.listens_for(engine, "connect", insert=True)
def set_search_path(dbapi_connection, connection_record):
    existing_autocommit = dbapi_connection.autocommit
    dbapi_connection.autocommit = True
    cursor = dbapi_connection.cursor()
    cursor.execute("SET SESSION search_path='myschema'")
    cursor.close()
    dbapi_connection.autocommit = existing_autocommit


with engine.connect() as connection:
    result = connection.execute(text("SHOW search_path"))
    for row in result:
        print(row)

However, as it says in the docs:

SQLAlchemy is generally organized around the concept of keeping this variable at its default value of public