python - flask-sqlalchemy怎么操作postgresql中不同schema（默认是public）

Question

1.现在我的postgresql中有多个schema

现在使用sqlalchemy中URI
postgresql://postgres:111111@127.0.0.1:5432/db
连接上的数据库中默认是在public这个schema下的，
我怎么使用stage这个schema呢？

Answer 1

When querying the table, put the schema prefix, for example

 select xxx from stage.table_name where ...; 

Answer 2

2 situations:

1: Manually define the model

class Test(db.Model):
    __tablename__ = 'test'
    __table_args__ = {
        'schema': 'other_schema'
    }
    id = db.Column('test_id', db.String(12), primary_key=True, )
    name = db.Column('test_name', db.String(10))

When defining the model manually, add the __table_args__ parameter to specify the schema used by the model

2: Reflect a table in the schema into the Model

I use a factory function to generate Flask instances.

In factory function:

def create_app(config_name):
    app = Flask(__name__)
    app.config.from_object(config[config_name])
    CORS(app)
    config[config_name].init_app(app)
    db.init_app(app)
    # 总觉得这里的实现不是很好 有更好的解决方案请告知
    tables = [‘test’]
    op = getattr(db.Model.metadata, ‘reflect’)
    op(bind=db.get_engine(app), only=tables, schema=’other_schema’)

    login_manager.init_app(app)
    from .hello import hello
    from .auth import auth
    app.register_blueprint(hello)
    app.register_blueprint(auth)

    return app

Reflection model definition:

class Test(db.Model):
    __tablename__ = ‘other_schema.test’
    
    # 有时后数据表设计时没有主键，但SQLAlchemy需要有主键，对于这类表我们需要重新指定主键：
    # 这里有时候需要用 __mapper_args__ 指定这张表的primary_key是哪个
    __mapper_args__ = {
    ‘primary_key’: [db.Model.metadata.tables[‘other_schema.test’].c.id]
    }

Then you can use ORM to operate tables of other schemas. I have been struggling with this problem for most of the day. I hope it can help people in need.