Flask-SQLAlchemy如何在schema_translate_map中使用create_all [英] Flask-SQLAlchemy how to use create_all with schema_translate_map

问题描述

SQLAlchemy提供了 Connection.execution_options.schema_translate_map 来更改执行时的架构，如

The SQLAlchemy provides the Connection.execution_options.schema_translate_map for change the schemas in execution time, as said in docs.

示例中显示了如何使用它来执行查询，但是想知道如何将其与create_all()一起使用.

In the examples is shown how to use to perform queries, but want to know how to use it with create_all().

我正在使用Flask-Sqlaclhemy和postgresql作为数据库.假设我有这个:

I'm using Flask-Sqlaclhemy and postgresql as database. Let's say I have this:

from flask_sqlalchemy import SQLAlchemy
db = SQLAlchemy()

def create_app():
    app = Flask(...)
    ...
    db.init_app(app)
    ...
    return app

class User(db.Model):
    __tablename__ = 'user'
    __table_args__ = {'schema':'public'}
    company = db.Column(db.String(10))

class SomePublicModel(db.Model):
    __tablename__ = 'some_public'
    __table_args__ = {'schema':'public'}
    ...

class SomeModelByDynamicSchema(db.Model):
    __tablename__ = 'some_dynamic'
    __table_args__ = {'schema':'dynamic'}
    ...

动态 dynamic 模式将在执行时根据用户所在公司替换为其他值.

The dynamic schema will be replace for other value according the user's company in execution time.

假设我已经在数据库中拥有架构 public 和 dynamic ，并且我想使用表创建一个新架构，如下所示:

Assuming I already have in database the schemas public and dynamic and a I want to create a new schema with the tables, something like this:

def create_new():
    user = User(company='foo')
    db.session.execute("CREATE SCHEMA IF NOT EXISTS %s" % user.company)
    db.session.connection().execution_options(schema_translate_map={'dynamic':user.company})

    #I would like to do something of the kind
    db.create_all() 

我希望在 foo 模式中将表创建为 foo.some_dynamic ，但是SQLAlchemy仍然尝试在 dynamic 模式中创建

I expected the tables to be created in the foo schema as foo.some_dynamic, but the SQLAlchemy still try to create in dynamic schema.

有人可以帮助我吗?

推荐答案

设置执行选项时，将创建连接副本.这意味着create_all将在没有schema_translate_map的情况下运行.

When you set execution options, you create copy of connection. This mean what create_all run without schema_translate_map.

>>> c = Base.session.connection()
>>> w = c.execution_options(schema_translate_map={'dynamic':'kek'})
>>> c._execution_options
immutabledict({})
>>> w._execution_options
immutabledict({'schema_translate_map': {'dynamic': 'kek'}})

这篇关于Flask-SQLAlchemy如何在schema_translate_map中使用create_all的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！