flask-sqlalchemy

flask-sqlalchemy copied to clipboard

I am unable to set the search_path and have it persist, when using binds.

This used to work fine

       test_application = create_app(config=IntegrationTestConfig, create_first_user=False)
        with test_application.app_context():
            db.session.execute('CREATE SCHEMA IF NOT EXISTS global;')
            db.session.execute('CREATE SCHEMA IF NOT EXISTS z_account;')
            db.session.execute('CREATE SCHEMA IF NOT EXISTS z_vocab_release;')
            db.session.commit()
            db.session.execute('SET search_path TO z_account, global;')
            db.session.commit()
            db.create_all(bind=None)  # create all the db tables

Running pg_dump after this executed shows the tables created in the correct schema (tables not defined with a schema in the model were created in z_account schema).

When I added bind_key to my models, and changed the code to use db.create_all(bind='all'), the tables with no schema defined in the model were created in public.

EG:

_BIND_KEY = "api"
_BASE_TABLE_ARGS = {'info': {'table_namespace': ACCOUNT_SCHEMA_NAMESPACE}}

class AccountCodeNamespace(db.Model):
    __tablename__ = "account_code_namespace"
    __bind_key__ = _BIND_KEY
    __table_args__ = _BASE_TABLE_ARGS
    code_namespace_id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(50))
    description = db.Column(db.String(255))

Debug log shows:

DEBUG:sqlalchemy.pool.QueuePool:Connection <connection object at 0x10c2d1510; dsn: 'dbname=zdon_2_qa_temp_01 user=sapuser password=xxxxxxxxxxxxxxxx host=localhost port=5432', closed: 0> checked out from pool
INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit)
INFO:sqlalchemy.engine.base.Engine:CREATE SCHEMA IF NOT EXISTS global;
INFO:sqlalchemy.engine.base.Engine:{}
INFO:sqlalchemy.engine.base.Engine:CREATE SCHEMA IF NOT EXISTS z_account;
INFO:sqlalchemy.engine.base.Engine:{}
INFO:sqlalchemy.engine.base.Engine:CREATE SCHEMA IF NOT EXISTS z_vocab_release;
INFO:sqlalchemy.engine.base.Engine:{}
INFO:sqlalchemy.engine.base.Engine:COMMIT
DEBUG:sqlalchemy.pool.QueuePool:Connection <connection object at 0x10c2d1510; dsn: 'dbname=zdon_2_qa_temp_01 user=sapuser password=xxxxxxxxxxxxxxxx host=localhost port=5432', closed: 0> being returned to pool
DEBUG:sqlalchemy.pool.QueuePool:Connection <connection object at 0x10c2d1510; dsn: 'dbname=zdon_2_qa_temp_01 user=sapuser password=xxxxxxxxxxxxxxxx host=localhost port=5432', closed: 0> rollback-on-return
DEBUG:sqlalchemy.pool.QueuePool:Connection <connection object at 0x10c2d1510; dsn: 'dbname=zdon_2_qa_temp_01 user=sapuser password=xxxxxxxxxxxxxxxx host=localhost port=5432', closed: 0> checked out from pool
INFO:sqlalchemy.engine.base.Engine:BEGIN (implicit)
INFO:sqlalchemy.engine.base.Engine:SET SEARCH_PATH TO z_account;
INFO:sqlalchemy.engine.base.Engine:{}
INFO:sqlalchemy.engine.base.Engine:COMMIT
DEBUG:sqlalchemy.pool.QueuePool:Connection <connection object at 0x10c2d1510; dsn: 'dbname=zdon_2_qa_temp_01 user=sapuser password=xxxxxxxxxxxxxxxx host=localhost port=5432', closed: 0> being returned to pool
DEBUG:sqlalchemy.pool.QueuePool:Connection <connection object at 0x10c2d1510; dsn: 'dbname=zdon_2_qa_temp_01 user=sapuser password=xxxxxxxxxxxxxxxx host=localhost port=5432', closed: 0> rollback-on-return
DEBUG:sqlalchemy.pool.QueuePool:Connection <connection object at 0x10c2d1510; dsn: 'dbname=zdon_2_qa_temp_01 user=sapuser password=xxxxxxxxxxxxxxxx host=localhost port=5432', closed: 0> checked out from pool
DEBUG:sqlalchemy.pool.QueuePool:Connection <connection object at 0x10c2d1510; dsn: 'dbname=zdon_2_qa_temp_01 user=sapuser password=xxxxxxxxxxxxxxxx host=localhost port=5432', closed: 0> being returned to pool
DEBUG:sqlalchemy.pool.QueuePool:Connection <connection object at 0x10c2d1510; dsn: 'dbname=zdon_2_qa_temp_01 user=sapuser password=xxxxxxxxxxxxxxxx host=localhost port=5432', closed: 0> rollback-on-return
DEBUG:sqlalchemy.pool.QueuePool:Created new connection <connection object at 0x10c2d1640; dsn: 'dbname=zdon_2_qa_temp_01 user=sapuser password=xxxxxxxxxxxxxxxx host=localhost port=5432', closed: 0>
INFO:sqlalchemy.engine.base.Engine:select version()
INFO:sqlalchemy.engine.base.Engine:{}
DEBUG:sqlalchemy.engine.base.Engine:Col ('version',)
DEBUG:sqlalchemy.engine.base.Engine:Row (u'PostgreSQL 9.4.5 on x86_64-apple-darwin15.0.0, compiled by Apple LLVM version 7.0.0 (clang-700.0.72), 64-bit',)
INFO:sqlalchemy.engine.base.Engine:select current_schema()
INFO:sqlalchemy.engine.base.Engine:{}
DEBUG:sqlalchemy.engine.base.Engine:Col ('current_schema',)
DEBUG:sqlalchemy.engine.base.Engine:Row (u'public',)

Any ideas where to look next?

So what you're saying is you wanted db.session.execute('SET search_path TO z_account, global;') to apply to all the binds, not just the default one?

That would do it. Did I miss a parm somewhere?

Stupid question, but why change the search path instead of simply using qualified (schema.table) names?

I don't plan to make suggested change of applying this to db.session.execute. Most likely this can be done with engine_options or session_options. If not, it might be possible to use a custom session subclass to do what you need.