How to perform a left join in SQLALchemy?

Question

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.

Answer 1

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) 

Answer 2

Here is how to use isouter:

select_from(db.join(Table1, Table2, isouter=True).join(Table3, isouter=True))