跨数据库加入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:

架构

1. db1.entity1

1. db1.entity1

1. entity1_id:主键
2. entity2_id:db2.entity2.entity2_id的外键

db2.entity2

db2.entity2

1. 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屋！