How to execute “left outer join” in SqlAlchemy

q = session.query(Table1.field1, Table1.field2)\
    .outerjoin(Table2)\ # use in case you have relationship defined
    # .outerjoin(Table2, == Table2.table_id)\ # use if you do not have relationship defined
    .filter(Table2.tbl2_id == None)

should do it, assuming that field1 and field2 are from Table1, and that you define a relationship:

class Table2(Base):
    # ...
    table1 = relationship(Table1, backref="table2s")

Leave a Comment