SQLAlchemy 支持 Postgres Schema

Question

我们使用SQLAlchemy和postgres托管一个多租户应用程序。我正在考虑从为每个租户拥有单独的数据库转移到使用具有多个模式的单个数据库。SQLAlchemy本身支持这种方式吗？我基本上只希望每个查询都以预定模式作为前缀...例如，

我们使用SQLAlchemy和postgres托管一个多租户应用程序。我正在考虑从为每个租户拥有单独的数据库转移到使用具有多个模式的单个数据库。SQLAlchemy本身支持这种方式吗？我基本上只希望每个查询都以预定模式作为前缀...例如，

select * from client1.users

代替仅仅

select * from users

请注意，我希望在特定请求/一组请求中切换所有表的模式，而不仅仅是这里和那里的单个表。

我想，这也可以通过自定义查询类来实现，但我无法想象还没有以这种方式完成的事情。

- eleddy

Answer 1

如果你想在连接字符串级别上进行操作，可以使用以下方法：

dbschema='schema1,schema2,public' # Searches left-to-right
engine = create_engine(
    'postgresql+psycopg2://dbuser@dbhost:5432/dbname',
    connect_args={'options': '-csearch_path={}'.format(dbschema)})

然而，对于一个多客户端（多租户）的应用程序，更好的解决方案是为每个客户配置一个不同的数据库用户，并为每个用户配置相关的search_path：

alter role user1 set search_path = "$user", public

- PPrice

---

虽然这对于SQLAlchemy有效，但对于Alembic（本地迁移工具）则无法正常工作。如果使用search_path进行模式控制，则修订生成脚本会完全混乱。根据https://github.com/sqlalchemy/alembic/issues/569的建议，最好不要触及`search_path`，而是在您的模型和迁移中明确定义模式。 - alexykot

嗨，这种方法适用于MySQL吗？ - Espoir Murhabazi

Answer 2

其实有几种方法可以解决这个问题，具体取决于你的应用程序结构。以下是最基本的方法：

meta = MetaData(schema="client1")

如果您的应用程序运行方式是在整个应用程序中一次只有一个“客户端”，那么您完成了。

但这里可能存在问题，即来自该元数据的每个表都在该模式上。如果您想要一个应用程序同时支持多个客户端（通常意味着“多租户”），那么这将是棘手的，因为您需要为每个客户端创建元数据的副本并复制所有映射。如果您真的想这样做，可以采用这种方法，它的工作方式是您可以使用特定映射类访问每个客户端，例如：

client1_foo = Client1Foo()

如果是这种情况，你需要使用在http://www.sqlalchemy.org/trac/wiki/UsageRecipes/EntityName上的“实体名称”配方来配合sometable.tometadata()（参见http://docs.sqlalchemy.org/en/latest/core/metadata.html#sqlalchemy.schema.Table.tometadata）。

所以假设真实的情况是应用程序内有多个客户端，但每个线程一次只有一个客户端。那么，在Postgresql中最简单的方法就是在开始使用连接时设置搜索路径：

# start request

# new session
sess = Session()

# set the search path
sess.execute("SET search_path TO client1")

# do stuff with session

# close it.  if you're using connection pooling, the
# search path is still set up there, so you might want to 
# revert it first
sess.close()

最终的方法是使用@compiles扩展覆盖编译器，将"schema"名称插入到语句中。虽然这是可行的，但是由于没有一个统一的钩子可以用于生成"Table"的每个地方，所以这可能会很棘手。您最好的选择可能是在每个请求上设置搜索路径。

- zzzeek

---

谢谢！我会尝试几个方法，然后看哪个最好并回报，但我认为路径是最好的选择。 - eleddy

@zzzeek我有一个关于Alembic的镜像问题，真的需要您的意见：https://dev59.com/EmEi5IYBdhLWcg3wndZw - dtheodor

顺便提一下，我成功地为声明式语法完成了这个任务，类似于：“Base = declarative_base(); Base.metadata.schema = 'ebay'”。不过可能有更好的方法。 - Catherine Devlin

重要提示：回滚操作也会回滚 set search_path。随后的命令将会针对默认模式进行操作。执行设置后，建议显示提交以避免此情况发生。参考链接：http://www.postgresql.org/docs/current/static/sql-set.html - Doctor J

为了后人，有一种正确的方法可以做到像@CatherineDevlin上面所做的那样，它看起来像meta = Metadata(schema="ebay"); Base = declarative_base(metadata = meta); 它在https://docs.sqlalchemy.org/en/14/orm/declarative_tables.html#explicit-schema-name-with-declarative-table中指定。 - ketil

Answer 3

现在可以使用Sqlalchemy 1.1中的模式翻译映射来完成。

class User(Base):
    __tablename__ = 'user'
    id = Column(Integer, primary_key=True)

    __table_args__ = {'schema': 'per_user'}

每次请求时，会话可以被设置为引用不同的模式：

session = Session()
session.connection(execution_options={
    "schema_translate_map": {"per_user": "account_one"}})

# will query from the ``account_one.user`` table

session.query(User).get(5)

我从这里的stackoverflow答案中引用了它 here.

链接到Sqlalchemy文档.

- Abhishek J

---

如果一个应用程序需要使用多个模式并在类级别上定义它们，这将特别有用——我曾经为此问题苦恼，直到找到了这个答案，感谢。 - Bartek Maciejewski

父类Base和Column来自哪里？ - RightmireM

@RightmireM，https://docs.sqlalchemy.org/en/14/orm/quickstart.html#declare-models - dangel

Answer 4

您可以使用sqlalchemy事件接口来管理此操作。因此，在创建第一个连接之前，设置类似以下内容的监听器：

from sqlalchemy import event
from sqlalchemy.pool import Pool

def set_search_path( db_conn, conn_proxy ):
    print "Setting search path..."
    db_conn.cursor().execute('set search_path=client9, public')

event.listen(Pool,'connect', set_search_path )

显然，这需要在创建第一个连接之前执行（例如在应用程序初始化中）。

我认为使用session.execute（...）解决方案的问题在于，它在会话使用的特定连接上执行。但是，我没有看到sqlalchemy中保证会话将无限期地继续使用相同的连接的任何内容。如果它从连接池中获取新连接，则会失去搜索路径设置。

我需要采用这种方法来设置应用程序的搜索路径，这与数据库或用户搜索路径不同。我希望能够在引擎配置中设置此选项，但找不到方法。使用连接事件可以工作。如果有更简单的解决方案，我会很感兴趣。

另一方面，如果您想在应用程序中处理多个客户端，则此方法将无法使用 - 我想session.execute（...）方法可能是最佳方法。

- Chris Crook

---

你有没有一种优雅的方法来传递'client9'作为参数，而不是硬编码它？我目前（hacky）的解决方法是将application_name查询参数传递到db-url（?application_name=bla），然后在set_search_path中使用db_conn.dsn.split('application_name=')[1]进行检查。 - rkrzr

Answer 5

从 SQLAlchemy 1.1 开始，可以使用 schema_translation_map 轻松地完成此操作。

https://docs.sqlalchemy.org/en/11/changelog/migration_11.html#multi-tenancy-schema-translation-for-table-objects

connection = engine.connect().execution_options(
    schema_translate_map={None: "user_schema_one"})

result = connection.execute(user_table.select())

这里是所有选项的详细评论： https://github.com/sqlalchemy/sqlalchemy/issues/4081

- echo

---

与 set search_path 不同，这适用于 create table。无需更改每个表模型的 __table_args__。 - Ben

Answer 6

可以在数据库层面解决这个问题。我猜想您的应用程序有一个专门的用户，被授予了一些模式权限。只需为该用户设置search_path为此模式：

ALTER ROLE your_user IN DATABASE your_db SET search_path TO your_schema;

- Konstantin Smolyanin

Answer 7

表定义中有一个模式属性。

我不确定它是否有效，但你可以尝试：

Table(CP.get('users', metadata, schema='client1',....)

- christian

---

我正在寻找一些更全局的东西，以便我可以将所有表中的所有查询切换为单个请求。我将更新问题以反映这一点。 - eleddy

Answer 8

对于任何前来此处的人，如果需要支持MYSQL或Oracle的更通用解决方案，请参考this guide。

基本上，它在第一次连接到数据库时为引擎设置模式。

engine = create_engine("engine_url")

@event.listens_for(engine, "connect", insert=True)
def set_current_schema(dbapi_connection, connection_record):
    cursor_obj = dbapi_connection.cursor()
    cursor_obj.execute(f"USE {self.schemas_name}")
    cursor_obj.close()

要执行的查询取决于您使用的数据库，因此对于PSQL，您将有不同的查询，对于ORACLE，您将有不同的查询等。

- Espoir Murhabazi

Answer 9

您可以直接更改您的 search_path。问题解决。

set search_path=client9;

在会话开始时，只需保持表未限定。

您还可以在每个数据库或每个用户级别上设置默认的search_path。我建议将其默认设置为空模式，以便您可以轻松地捕获任何未设置的故障。

http://www.postgresql.org/docs/current/static/ddl-schemas.html#DDL-SCHEMAS-PATH

- Richard Huxton

---

那真是个好主意。击掌！ - eleddy

请记住，在 session.commit() 之后会启动一个新的事务，因此 search_path 将被重置。 SA 会话事件非常适合为每个新事务设置 search_path。 - Brett

Answer 10

我尝试了：

con.execute('SET search_path TO {schema}'.format(schema='myschema'))

但对我来说没有用。然后我在init函数中使用了schema=参数：

# We then bind the connection to MetaData()
meta = sqlalchemy.MetaData(bind=con, reflect=True, schema='myschema')

然后，我使用模式名称对表进行了限定。

house_table = meta.tables['myschema.houses']

一切都正常工作。

- crankymax