sqlalchemy 中的跨数据库连接 [英] Cross database join in sqlalchemy
问题描述
在 SQLAlchemy 中是否有一种方法可以进行跨数据库连接.具体来说,这是我的用例:
Is there a way in SQLAlchemy to do cross-database joins. To be specific, here is my use case:
架构
- db1.entity1
- db1.entity1
- entity1_id:主键
- entity2_id:db2.entity2.entity2_id 的外键
- entity2_id:主键
模型
我对模型使用声明式.
class Entity1(Base):
__tablename__ = 'entity1' ## I tried combination of <db>.<table> with no success
entity1_id = Column(Integer, primary_key=True)
entity2_id = Column(Integer, ForeignKey('db2.entity2.entity2_id'))
entity2 = relationship('Entity2')
class Entity2(Base):
__tablename__ = 'entity2' ## I tried combination of <db>.<table> with no success
entity2_id = Column(Integer, primary_key=True)
现在,正如预期的那样,我对 Entity1 的查询失败,并显示 MySQL 错误消息,指出未找到表 entity2.我为 __tablename__
尝试了许多不同的组合,但没有成功.所以我想知道在 SQLAlchemy 中是否可行.
Now, as expected, my queries for Entity1 is failing with MySQL error messages saying table entity2 not found. I tried many different combination for __tablename__
with no success. So i was wondering if it is possible in SQLAlchemy.
推荐答案
您可能需要将 schema
参数传递给 sqlalchemy.schema.Table
.当使用声明性基础进行 ORM 映射时,您可以通过您的类的 __table_args__
属性提供这个额外的参数.
You probably need to pass the schema
parameter to sqlalchemy.schema.Table
. When using declarative base for ORM mapping, you can provide this extra parameter through the __table_args__
property on your classes.
class Entity2(Base):
__tablename__ = 'entity2' ## I tried combination of <db>.<table> with no success
__table_args__ = {'schema': 'db2'}
entity2_id = Column(Integer, primary_key=True)
class Entity1(Base):
__tablename__ = 'entity1' ## I tried combination of <db>.<table> with no success
__table_args__ = {'schema': 'db1'}
entity1_id = Column(Integer, primary_key=True)
entity2_id = Column(Integer, ForeignKey(Entity2.entity2_id))
entity2 = relationship('Entity2')
这篇关于sqlalchemy 中的跨数据库连接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!