在sqlalchemy中使用数据库名称限定表名称 [英] Qualifying table names with database names in sqlalchemy
问题描述
我在多个数据库上将sqlalchemy与elixir结合使用。当前，所有会话都可以很好地工作-一个会话绑定到另一个数据库。但是，在某些情况下，我想使用一个会话来查询另一个数据库（这是为了支持具有不同复制数据库的多个服务器）。
问题是在针对一个会话进行查询时，表名称不符合正确的数据库名称，而且我不知道如何告诉查询以哪个数据库名称为前缀。有什么方法可以实现？
这可能很困难，因为您已经按照不同的绑定映射了所有内容。 Table的 schema参数是如何呈现 schemaname.tablename语法的，但是，这意味着您将使用与没有模式名称映射到表的普通类不同的映射类。 / p>
因此，首先使用一些特定于平台的技术，这会使此操作变得容易得多。如果您使用的是Oracle，请使用Oracle CREATE SYNONYM将 somedb.sometable映射到远程模式中的 sometable。如果您使用的是Postgresql，请操作search_path，以便在多个架构中搜索给定名称（请参阅 http://www.postgresql.org/docs/8.1/static/ddl-schemas.html#DDL-SCHEMAS-PATH ）。
这些都不起作用吗？好的，那么您需要在 http：// www处按照食谱的方式进行操作。 sqlalchemy.org/trac/wiki/UsageRecipes/EntityName （对不起，这不是使用Elixir，我不确定Elixir的功能到底是什么）。根据具体情况，有不同的实现方法。这是将新的匿名类映射到原始表的一种方法：
＃常规类
类ClassOne （基数）：
__tablename__ ='一个'
id =列（整数，primary_key = True）
class ClassTwo（Base）：
__tablename__ ='two'
id = Column（整数，primary_key = True）
def map_to_alt_tb（cls，元数据，架构名称）：
m = class_mapper（cls）
cls2 = type（
AltClass，
（对象，），
{}
）
表= m.local_table.tometadata（元数据，架构名称）
mapper（cls2 ，table）
return cls2
alt_cls = map_to_alt_db（ClassTwo，somemetadata， alt_schema）
map_to_alt_db（）将拉出 ClassTwo 映射到的表，将其更改为架构为 alt_schema，然后将其映射到新类。
这种方法会丢失有关 ClassTwo 不过。如果需要，您可以执行该Wiki页面上的更多特定方法。
I'm using sqlalchemy with elixir on multiple databases. Currently everything works well with multiple sessions -- one bind to a different database. However, there are cases where I want to use one session to query against the other database (this is to support multiple servers with different replicated databases).
The problem is that when making a query against one session, the table names are not qualified with the proper database name, and I don't know how I can tell the query which database name to prefix. Is there any way to accomplish this?
This may be difficult as you already have everything mapped in terms of different binds. The "schema" argument to Table is how the "schemaname.tablename" syntax is rendered, however this would imply that you'd be using mapped classes that are distinct from the normal ones that are mapped to the table without the schema name.
So first some platform-specific techniques that would make this a lot easier. If you're on Oracle, use Oracle CREATE SYNONYM to map "somedb.sometable" to just "sometable" in the remote schema. If you're on Postgresql, manipulate the search_path so that multiple schemas are searched for a given name (see http://www.postgresql.org/docs/8.1/static/ddl-schemas.html#DDL-SCHEMAS-PATH).
Neither of those work ? OK then you need to do something along the lines of the recipe here at http://www.sqlalchemy.org/trac/wiki/UsageRecipes/EntityName (sorry, this isn't using Elixir, I'm not sure what Elixir's capabilities are as far as that). There's different ways to achieve it depending on specifics. Here's one way that will make a new, anonymous class mapped to the original table:
# regular class
class ClassOne(Base):
__tablename__ = 'one'
id = Column(Integer, primary_key=True)
class ClassTwo(Base):
__tablename__ = 'two'
id = Column(Integer, primary_key=True)
def map_to_alt_tb(cls, metadata, schemaname):
m = class_mapper(cls)
cls2 = type(
"AltClass",
(object, ),
{}
)
table = m.local_table.tometadata(metadata, schemaname)
mapper(cls2, table)
return cls2
alt_cls = map_to_alt_db(ClassTwo, somemetadata, "alt_schema")
map_to_alt_db() will pull out the table that ClassTwo is mapped to, change it's schema to "alt_schema", then map it to a new class.
This approach loses whatever else is special about ClassTwo, though. If you needed that you could do more of the specific approach that's on that wiki page.
这篇关于在sqlalchemy中使用数据库名称限定表名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
