postgresql SQLAlchemy 对 Postgres 模式的支持

SQLAlchemy support of Postgres Schemas

提问by eleddy

We host a multitenant app with SQLAlchemy and postgres. I am looking at moving from having separate databases for each tenant to a single database with multiple schemas. Does SQLAlchemy support this natively? I basically just want every query that comes out to be prefixed with a predetermined schema... e.g

我们使用 SQLAlchemy 和 postgres 托管了一个多租户应用程序。我正在考虑从为每个租户拥有单独的数据库转变为具有多个模式的单个数据库。SQLAlchemy 本身是否支持此功能？我基本上只希望出现的每个查询都以预先确定的模式为前缀......例如

select * from client1.users

instead of just

而不仅仅是

select * from users

Note that I want to switch the schema for all tables in a particular request/set of requests, not just a single table here and there.

请注意，我想切换特定请求/请求集中所有表的架构，而不仅仅是此处和那里的单个表。

I imagine that this could be accomplished with a custom query class as well but I can't imagine that something hasn't been done in this vein already.

我想这也可以通过自定义查询类来完成，但我无法想象在这方面还没有做些什么。

采纳答案by zzzeek

well there's a few ways to go at this and it depends on how your app is structured. Here is the most basic way:

好吧，有几种方法可以解决这个问题，这取决于您的应用程序的结构。这是最基本的方法：

meta = MetaData(schema="client1")

If the way your app runs is one "client" at a time within the whole application, you're done.

如果您的应用程序在整个应用程序中一次运行一个“客户端”，那么您就大功告成了。

But what may be wrong with that here is, every Table from that MetaData is on that schema. If you want one application to support multiple clients simultaneously (usually what "multitenant" means), this would be unwieldy since you'd need to create a copy of the MetaData and dupe out all the mappings for each client. This approach can be done, if you really want to, the way it works is you'd access each client with a particular mapped class like:

但这里可能有问题的是，来自该元数据的每个表都在该架构上。如果您希望一个应用程序同时支持多个客户端（通常是“多租户”的意思），这将是笨拙的，因为您需要创建 MetaData 的副本并复制每个客户端的所有映射。这种方法可以完成，如果你真的想要，它的工作方式是你使用特定的映射类访问每个客户端，例如：

client1_foo = Client1Foo()

and in that case you'd be working with the "entity name" recipe at http://www.sqlalchemy.org/trac/wiki/UsageRecipes/EntityNamein conjunction with sometable.tometadata()(see http://docs.sqlalchemy.org/en/latest/core/metadata.html#sqlalchemy.schema.Table.tometadata).

在这种情况下，你会与“实体名称”配方工作在http://www.sqlalchemy.org/trac/wiki/UsageRecipes/EntityName会同sometable.tometadata()（见http://docs.sqlalchemy.org/en /latest/core/metadata.html#sqlalchemy.schema.Table.tometadata）。

So let's say the way it really works is multiple clients within the app, but only one at a time per thread. Well actually, the easiest way to do that in Postgresql would be to set the search path when you start working with a connection:

所以让我们说它真正工作的方式是应用程序中的多个客户端，但每个线程一次只有一个。实际上，在 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()

The final approach would be to override the compiler using the @compiles extension to stick the "schema" name in within statements. This is doable, but would be tricky as there's not a consistent hook for everywhere "Table" is generated. Your best bet is probably setting the search path on each request.

最后一种方法是使用@compiles 扩展覆盖编译器以将“模式”名称粘贴在语句中。这是可行的，但会很棘手，因为对于生成“表”的所有地方都没有一致的钩子。您最好的选择可能是为每个请求设置搜索路径。

回答by PPrice

If you want to do this at the connection string level then use the following:

如果要在连接字符串级别执行此操作，请使用以下命令：

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

But, a better solution for a multi-client (multi-tenant) application is to configure a different db user for each client, and configure the relevant search_path for each user:

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

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

回答by Abhishek Jebaraj

It can now be done using schema translation map in Sqlalchemy 1.1.

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

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

    __table_args__ = {'schema': 'per_user'}

On each request, the Session can be set up to refer to a different schema each time:

对于每个请求，Session 可以设置为每次引用不同的模式：

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)

Referred it from the SO answer here.

从此处的 SO 答案中引用它。

Link to the Sqlalchemy docs.

链接到Sqlalchemy 文档。

回答by Chris Crook

You may be able to manage this using the sqlalchemy event interface. So before you create the first connection, set up a listener along the lines of

您可以使用 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 )

Obviously this needs to be executed before the first connection is created (eg in the application initiallization)

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

The problem I see with the session.execute(...) solution is that this executes on a specific connection used by the session. However I cannot see anything in sqlalchemy that guarantees that the session will continue to use the same connection indefinitely. If it picks up a new connection from the connection pool, then it will lose the search path setting.

我在 session.execute(...) 解决方案中看到的问题是它在会话使用的特定连接上执行。但是，我在 sqlalchemy 中看不到任何保证会话将无限期地继续使用相同连接的内容。如果它从连接池中选择了一个新连接，那么它将丢失搜索路径设置。

I am needing an approach like this in order to set the application search_path, which is different to the database or user search path. I'd like to be able to set this in the engine configuration, but cannot see a way to do this. Using the connect event does work. I'd be interested in a simpler solution if anyone has one.

我需要这样的方法来设置应用程序 search_path，它与数据库或用户搜索路径不同。我希望能够在引擎配置中设置它，但看不到这样做的方法。使用连接事件确实有效。如果有人有一个更简单的解决方案，我会对它感兴趣。

On the other hand, if you are wanting to handle multiple clients within an application, then this won't work - and I guess the session.execute(...) approach may be the best approach.

另一方面，如果您想在一个应用程序中处理多个客户端，那么这将不起作用 - 我猜 session.execute(...) 方法可能是最好的方法。

回答by christian

There is a schema property in Table definitions

表定义中有一个架构属性

I'm not sure if it works but you can try:

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

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

回答by Richard Huxton

You can just change your search_path. Issue

你可以改变你的search_path。问题

set search_path=client9;

at the start of your session and then just keep your tables unqualified.

在您的会话开始时，然后保持您的桌子不合格。

You can also set a default search_path at a per-database or per-user level. I'd be tempted to set it to an empty schema by default so you can easily catch any failure to set it.

您还可以在每个数据库或每个用户级别设置默认 search_path。默认情况下，我很想将它设置为空模式，这样您就可以轻松地发现设置它的任何失败。

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

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

回答by Joseph Lust

I found none of the above answers worked with SqlAlchmeny 1.2.4. This is the solution that worked for me.

我发现上述答案都不适用于 SqlAlchmeny 1.2.4。这是对我有用的解决方案。

from sqlalchemy import MetaData, Table
from sqlalchemy import create_engine    

def table_schemato_psql(schema_name, table_name):

        conn_str = 'postgresql://{username}:{password}@localhost:5432/{database}'.format(
            username='<username>',
            password='<password>',
            database='<database name>'
        )

        engine = create_engine(conn_str)

        with engine.connect() as conn:
            conn.execute('SET search_path TO {schema}'.format(schema=schema_name))

            meta = MetaData()

            table_data = Table(table_name, meta,
                              autoload=True,
                              autoload_with=conn,
                              postgresql_ignore_search_path=True)

            for column in table_data.columns:
                print column.name

回答by crankymax

I tried:

我试过：

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

and that didn't work for me. I then used the schema= parameter in the init function:

这对我不起作用。然后我在 init 函数中使用了 schema= 参数：

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

Then I qualified the table with the schema name

然后我用模式名称限定了表

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

and everything worked.

一切正常。

回答by echo

from sqlalchemy 1.1, this can be done easily using using schema_translation_map.

从 sqlalchemy 1.1 开始，这可以使用 schema_translation_map 轻松完成。

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

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())

Here is a detailed reviews of all options available: https://github.com/sqlalchemy/sqlalchemy/issues/4081

以下是所有可用选项的详细评论：https: //github.com/sqlalchemy/sqlalchemy/issues/4081