使用SQLalchemy ORM和SQLite构建具有跨数据库联接的查询

Question

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

原则上，我的问题与SQLAlchemy跨数据库错误查询联接相同，但最初发布者的问题是使用与我的用例不匹配的不同解决方案解决的。

我向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']

我相信，如果我能够以某种方式将两个数据库引擎绑定到一个会话，我的麻烦就结束了。但是怎么做呢？对于连接到同一引擎的两个数据库(例如MySQL数据库中的两个)，我可以添加__table_args__ = {'schema': 'annotations'}(根据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'}

到我的餐桌上上课。

工作起来很有魅力！