5
votes

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"
)
1
It can do a cross database join, just not across various connections. A related question is perhaps stackoverflow.com/questions/46277437/… - pi.
I just need get use more than 1 database and their objects in one connection. In your example its different from me question. Anyway thanks! - zs_vasily
A list of reasons to use SQLAlchemy would be long, as would a list of things it does not support out of the box. Could you please clarify your question regarding what have you actually tried and what you're even trying to accomplish etc. How's ETL related to your question? By its nature SQLA is not meant to replace anything. It's a tool for using SQL databases. You could run your "simple query" just like it is, by passing an SQL string to methods like engine.execute(). - Ilja Everilä
I try to do this query in one session with engine who upstairs, but it dont work. I think something with "abstract class" + "engines.append" can help me, bud i cannot find good example for it - zs_vasily

1 Answers

4
votes

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