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.

outerjoin is not a replacement for join. I need a left join. — user1742188, Sep 21 '16 at 18:56

score 124 · Accepted Answer · edited Jan 29 '19 at 08:57

124

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)

edited Jan 29 '19 at 08:57

moodh

2,661
28
42

answered Sep 23 '16 at 20:53

user1742188

4,563
8
35
60

2

I used the second solution(Declarative example), and works like a charm, love it! – iconique Jul 22 '21 at 03:02

score 10 · Answer 2 · answered May 12 '17 at 18:51

10

Here is how to use isouter:

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

answered May 12 '17 at 18:51

melchoir55

6,842
7
60
106

score 10 · Answer 3 · edited Jun 30 '22 at 11:32

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))

Option 3 - LEFT JOIN and select only SOME columns from right table `db.query(RightTable.column).select_from(LeftTable).join(RightTable, LeftTable.column=RightTable.column, isouter=True).filter(condition).all()` — Meet, Jul 12 '22 at 07:48

How to perform a left join in SQLALchemy?

3 Answers3

Linked