SQLAlchemy 对 Postgres 模式的支持 [英] 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 结合 sometable.tometadata()(参见 http://docs.sqlalchemy.org/en/latest/core/metadata.html#sqlalchemy.schema.Table.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 扩展覆盖编译器，将模式"名称粘贴在语句中.这是可行的，但会很棘手，因为对于生成表"的所有地方都没有一致的钩子.最好的办法可能是为每个请求设置搜索路径.

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.

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