I have a SQL query which perfroms a series of left joins on a few tables:
SELECT <some attributes> FROM table1 t1 INNER JOIN table2 t2 ON attr = 1 AND attr2 = 1 LEFT JOIN table3 t3 ON t1.Code = t2.Code AND t3.Date_ = t1.Date_ LEFT JOIN tabl4 t4 ON t4.Code = t1.code AND t4.Date_ = t1.Date_ So far, I have:
(sa.select([idc.c.Code]) .select_from( t1.join(t2, and_(t1.c.attr == 1, t2.c.attr2 = 1)) .join(t3, t3.c.Code == t1.c.Code))) but I can't figure out how to make the join a LEFT JOIN.
3 Answers
The isouter=True flag will produce a LEFT OUTER JOIN which is the same as a LEFT JOIN.
With your code:
(sa.select([idc.c.Code]) .select_from( t1.join(t2, and_(t1.c.attr == 1, t2.c.attr2 = 1)) .join(t3, t3.c.Code == t1.c.Code, isouter=True))) Declarative example:
session = scoped_session(sessionmaker()) session.query(Model).join(AnotherModel, AnotherModel.model_id == Model.id, isouter=True) Here is how to use isouter:
select_from(db.join(Table1, Table2, isouter=True).join(Table3, isouter=True)) Option 1 - LEFT JOIN and select ALL columns from both tables
# Query the db results = db.session.query(Table_1, Table_2).join(Table_2, Table_2.column_name == Table_1.column_name, isouter=True).all() # Iterate results and do stuff for result in results: try: # Use [0] for accesing table_1 columns (left table) and use [1] for accesing table_2 columns (right table) print(result[0].column_name_x) print(result[0].column_name_y) print(result[1].column_name_x) print(result[1].column_name_y) except Exception as e: print(str(e)) Option 2 - LEFT JOIN and select SOME columns from both tables
# Query the db results = db.session.query(Table_1.column_name_x, Table_1.column_name_y Table_2.column_name_z).join(Table_2, Table_2.column_name == Table_1.column_name, isouter=True).all() # Iterate results and do stuff for result in results: try: # Use dot notation for accesing column from any table print(result.column_name_x) print(result.column_name_y) print(result.column_name_z) except Exception as e: print(str(e)) 
