Cannot Dynamically Set PostGres Schema at Runtime

Issue #4081 closed
Charles Cliff
created an issue

The Library is unable to Dynamically Set PostGres Schema at Runtime.

This is pretty basic functionality.

Comments (2)

  1. Michael Bayer repo owner

    Postgresql doesn't have a "schema" that is set, it has a "schema search path" (https://www.postgresql.org/docs/current/static/ddl-schemas.html#DDL-SCHEMAS-PATH). this is a postgresql-specific concept so requires that you use the Postgresql commands directly.

    to achieve this for all new connections immediately, use a connect event:

    from sqlalchemy import event
    from sqlalchemy import create_engine
    
    engine = create_engine("postgresql://...")
    
    @event.listens_for(engine, "connect")
    def set_schema(dbapi_connection, connection_record):
        with dbapi_connection.cursor() as cursor:
            cursor.execute("SET search_path TO myschema,public")
    

    to achieve it for an arbitrary connection from the engine:

    conn = engine.connect()
    conn.execute("SET search_path TO myschema,public")
    

    keep in mind if you are using a connection pool, that search path doesn't get automatically reset.

  2. Log in to comment