SQLAlchemy join syntax using multiple column names

Question

This is the following sql query I want to execute and works perfectly on the command line:

select * from table1 join table2 using (col1, col2)

I am cannot figure out how to make this execute using SQLAlchemy and any help would be appreciated.

There are no foreign keys between the tables. The table rows can only be paired up by values matching in multiple columns.

Thank you!

Answer 1

Considering Entity1 maps to table1, and Entity2 maps to table2:

rows = session
    .query(Entity1, Entity2)
    .join(Entity2, (Entity1.col1==Entity2.col1) & (Entity1.col2==Entity2.col2))
    .all()

rows will be a list of tuples where rows[][0] is Entity1 and rows[][1] is Entity2. You can use .join() and then specify the join condition with the second param. If you omit the join condition, then the .query() method alone generates a cross join between table1 and table2.