Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
446 views
in Technique[技术] by (71.8m points)

python - How to do a cross-database-query join in SQLAlchemy?

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

I use this connection with windows authentication:

engine = create_engine(
    "mssql+pyodbc://@{Server}/{database}?driver=SQL+Server?trusted_connection=yes"
)
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...