如何在 SQLAlchemy 中进行跨数据库查询连接? [英] How to do a cross-database-query join in SQLAlchemy?
本文介绍了如何在 SQLAlchemy 中进行跨数据库查询连接?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在 SQLAlchemy 中找不到这个简单查询的解决方案示例.SQLAlchemy 能否替代 T-SQL ETL 数据修改?
I could not find examples of solutions to this simple query in SQLAlchemy. Can SQLAlchemy replace T-SQL ETL data modify or not?
select a.field1, a.field2, b.field2
from database1.schema1.table_a as a
inner join database2.schema1.table_b as b
on a.fileld1 = b.fileld1
我将此连接用于 Windows 身份验证:
I use this connection with windows authentication:
engine = create_engine(
"mssql+pyodbc://@{Server}/{database}?driver=SQL+Server?trusted_connection=yes"
)
推荐答案
您需要的是 多部分模式名称.那和使用 __table_args__
– 如果您使用的是声明式 – 将允许您执行查询.由于您省略了表或模型定义,我将根据您的查询示例生成示例:
What you need is multipart schema names. That and using __table_args__
– in case you're using Declarative – will allow you to perform your query. Since you've omitted your table or model definitions, I'll produce samples based on your query example:
In [8]: class TableA(Base):
...: __tablename__ = 'table_a'
...: __table_args__ = {
...: 'schema': 'database1.schema1'
...: }
...: id = Column(Integer, primary_key=True)
...: field1 = Column(Integer)
...: field2 = Column(Integer)
...:
In [9]: class TableB(Base):
...: __tablename__ = 'table_b'
...: __table_args__ = {
...: 'schema': 'database2.schema1'
...: }
...: id = Column(Integer, primary_key=True)
...: field1 = Column(Integer)
...: field2 = Column(Integer)
...:
In [10]: q = session.query(TableA.field1, TableA.field2, TableB.field2).\
...: join(TableB, TableA.field1 == TableB.field1)
In [12]: q.statement.compile(dialect=mssql.dialect())
Out[12]: <sqlalchemy.dialects.mssql.base.MSSQLCompiler at 0x7fa3886027b8>
In [13]: print(_)
SELECT database1.schema1.table_a.field1, database1.schema1.table_a.field2, database2.schema1.table_b.field2
FROM database1.schema1.table_a JOIN database2.schema1.table_b ON database1.schema1.table_a.field1 = database2.schema1.table_b.field1
这篇关于如何在 SQLAlchemy 中进行跨数据库查询连接?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文