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?

ljmc · Answer 1 · 2023-01-08T23:31:27.740

2

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.

edited Jan 08 '23 at 23:31

answered Jan 03 '23 at 20:49

ljmc

4,830
2
7
26

while this works without a search path, this errors when I include `connect_args={'options': '-csearch_path={}'.format(dbschema)}` because pg8000 does not recognize the `options` key. – kamykam Jan 04 '23 at 15:52

score 1 · Answer 2 · answered Feb 18 '23 at 09:32

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

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

2 Answers2