sqlalchemy 中的跨数据库连接

发布于 2024-11-16 11:00:17 字数 894 浏览 3 评论 0原文

SQLAlchemy 有没有办法进行跨数据库连接。具体来说，这是我的用例：

Schema

db1.entity1
1. entity1_id：主键
2. entity2_id：db2.entity2.entity2_id 的外键
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 错误消息，指出未找到表实体2。我为 __tablename__ 尝试了许多不同的组合，但没有成功。所以我想知道在 SQLAlchemy 中是否可能。

原文

Is there a way in SQLAlchemy to do cross-database joins. To be specific, here is my use case:

Schema

db1.entity1
1. entity1_id: Primary Key
2. entity2_id: Foreign Key to db2.entity2.entity2_id
db2.entity2
1. entity2_id: Primary Key

Model

I'm using declarative style for models.

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)

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

傲娇萝莉攻 2024-11-23 11:00:17

您可能需要将 schema 参数传递给 sqlalchemy.schema.Table。当使用声明性基础进行 ORM 映射时，您可以通过类上的 __table_args__ 属性提供此额外参数。

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')

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')

回复收藏 0 原文

~没有更多了~