python - 使用 SQLalchemy ORM 和 SQLite 通过跨数据库联接构建查询

Question

我有两个 SQLite 数据库，其中包含需要使用 SQLalchemy 连接的表。由于某些原因，我无法将所有表合并到一个 SQLite 数据库中。我正在使用 SQLalchemy ORM。我无法在网上找到任何符合我的具体情况的解决方案。

我的问题原则上与 SQLAlchemy error query join across database 相同，但原始发帖者的问题是使用与我的用例不匹配的不同解决方案解决的。

我向 Stackoverflow 的智者提出的问题:

我想模拟以下 SQL 查询:

SELECT DISTINCT g.gene_symbol, o.orthofinder_id FROM eukarya.genes AS g JOIN annotations.orthofinder AS o ON g.gene_id=o.gene_id;

使用附加了两个数据库文件的 SQliteStudio，此查询可以正常工作。

我当前用于描述元数据的代码:

eukarya_engine = create_engine('sqlite:///eukarya_db.sqlite3')
annotations_engine = create_engine('sqlite:///eukarya_annotations_db.sqlite3')

meta = MetaData()  # This allows me to define cross database foreign keys

Eukarya = declarative_base(bind=eukarya_engine, metadata=meta)
Annotations = declarative_base(bind=annotations_engine, metadata=meta)
# I did the above in the hopes that by binding the engines this way,
# would percolate through the schema, and sqlalchemy would be able
# figure out which engine to use for each table.

class Genes(Eukarya):
  """SQLalchemy object representing the Genes table in the Eukarya database."""
  __tablename__ = 'genes'
  gene_id = Column(Integer, primary_key=True, unique=True)
  gene_symbol = Column(String(16), index=True)
  taxonomy_id = Column(Integer, ForeignKey(Species.taxonomy_id), index=True)
  original_gene_id = Column(String)

class Orthofinder(Annotations):
  """SQLalchemy object representing the Orthofinder table in the Annotations database."""
  __tablename__ = 'orthofinder'
  id = Column(Integer,primary_key=True, autoincrement=True)
  gene_id = Column(Integer, ForeignKey(Genes.gene_id), index=True)
  orthofinder_id = Column(String(10), index=True)

Session = sessionmaker()
session = Session(bind=eukarya_engine)

print(session.query(Genes.gene_symbol,Orthofinder.orthofinder_id).
      join(Orthofinder).all().statement)

最后一个打印语句返回:

sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table: orthofinder [SQL: 'SELECT genes.gene_symbol AS genes_gene_symbol, orthofinder.orthofinder_id AS orthofinder_orthofinder_id \nFROM genes JOIN orthofinder ON genes.gene_id = orthofinder.gene_id']

我相信，如果我能够以某种方式将两个数据库引擎绑定(bind)到一个 session ，我的麻烦就结束了。但如何呢？对于附加到同一引擎的两个数据库(例如 MySQL 数据库中的两个数据库)，我可以添加 __table_args__ = {'schema': 'annotations'} (按照 Cross database join in sqlalchemy )，但我无法以 SQLite 为例来解决这个问题。

我更喜欢一种解决方案，允许我的代码的用户构建查询，而无需知道每个表驻留在哪个数据库中。

请帮忙!非常感谢!

Answer 1

回答我自己的问题(感谢 Ilja 找到解决方案):

我可以这样定义引擎:

engine  = create_engine('sqlite://',echo=True)  # generate in mem database to attach mutitple sqlite databases to.
engine.execute("attach database 'eukarya_db.sqlite3' as eukarya;")
engine.execute("attach database 'eukarya_annotations_db.sqlite3' as annotations;")

然后添加

__table_args__ = {'schema': 'eukarya'}

或

__table_args__ = {'schema': 'annotations'}

到我的表类。

工作起来就像一个魅力!