Postgres模式的SQLAlchemy支持 [英] SQLAlchemy support of Postgres Schemas

问题描述

我们托管了一个带有SQLAlchemy和postgres的多租户应用程序.我正在考虑从为每个租户使用单独的数据库迁移到具有多个架构的单个数据库. SQLAlchemy是否原生支持此功能?我基本上只是希望每个查询都以预定的模式作为前缀...例如

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

select * from client1.users

而不是

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.

推荐答案

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

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.

但是这里可能有问题，该MetaData中的每个表都在该架构上.如果您希望一个应用程序同时支持多个客户端(通常是多租户"的意思)，这将很麻烦，因为您需要创建元数据的副本并为每个客户端复制所有映射.如果您确实愿意，可以采用这种方法，它的工作方式是使用特定的映射类访问每个客户端，例如:

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:

client1_foo = Client1Foo()

，在这种情况下，您将使用 http://www.sqlalchemy.org/trac/wiki/UsageRecipes/EntityName ://www.sqlalchemy.org/trac/wiki/UsageRecipes/EntityName 与sometable.tometadata()结合使用(请参见

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

因此，我们可以说它真正起作用的方式是应用程序中有多个客户端，但是每个线程一次只能有一个.好吧，实际上，在Postgresql中执行此操作的最简单方法是在开始使用连接时设置搜索路径:

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:

# 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"名称保留在内部语句中.这是可行的，但是会很棘手，因为在每个生成表"的地方都没有一致的钩子.最好的选择可能是为每个请求设置搜索路径.

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.

这篇关于Postgres模式的SQLAlchemy支持的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！