How to make the following SQLAlchemy queries?

Hello! I would like to migrate much of their code written in T-SQL Pitone direction and stumbled upon a basic problem of working with data that was not able to solve searching in the documentation of SQLAlchemy and other forums.

Question:
How you can implement the following queries using SQLAlchemy (example to test below)?

1. select a.field1, a.field2, b.field2
 from server1.database1.schema1.table_a as a
 inner server2.database1.schema1.table_b as b
 on a.fileld1 = b.fileld1

2. select a.field1, a.field2, b.field2
 from server1.database1.schema1.table_a as a
 inner dataset_variable (**) as b
 on a.fileld1 = b.fileld1


3. The result of the query - print(select) (see below in the test example) returns the error:
sqlalchemy.exc.ProgrammingError: (pyodbc.ProgrammingError) ('42S02', "[42S02] [M icrosoft][ODBC SQL Server Driver][SQL Server]Invalid object name server2.database1schema1.table_b'.

**Data obtained from another session, SQLAlchemy (server2.database1.schema1.table_b) and stored in a variable dataset_variable

Test connection example:
There are two linked sql servers.

engine = create_engine("mssql+pyodbc://@{server1}/{}?driver=SQL+Server?trusted_connection=yes", echo=True)

metadata = MetaData(engine)
Base = declarative_base(metadata=metadata)

class table_a(Base):
 __tablename__ = 'table_a '
 __table_args__ = {
 'schema': 'database1.schema1'
}
 id = Column(Integer, primary_key=True)
 f1 = Column(String(100))
 f2 = Column(String(100))

class table_b(Base):
 __tablename__ = 'table_b '
 __table_args__ = {
 'schema': 'server2.database1.schema1'
}
 id = Column(Integer, primary_key=True)
 f1 = Column(String(100))
 f2 = Column(String(100))

Session = sessionmaker(bind=engine)
session = session()

select = session.query(table_b.id).first() 
print(select)
June 10th 19 at 15:51
0 answer

Find more questions by tags SQLAlchemySQL ServerPython