SQLAlchemy select from two tables with null LEFT JOIN returns empty result

Question

I have two sql tables. Table 1 (id, name) and Table 2 with (id, name, table1_id)

 sql = text('SELECT t1.*, t2.* FROM table1 t1 '
               'LEFT JOIN table2 t2 ON t1.id=t2.table1_id ')
query = db.session.query(Table1, Table2).from_statement(sql).params()
table1_table2_tuple_list = query.all()

If the result of the sql query is

67, 'some name', 1, 'some name in table 2', 67

Then everything is OK and I have [(<Table1>, <Table2>)] for table1_table2_tuple_list

But If the result of the sql query is: (also all other code is for this result)

67, 'some name', Null, Null, Null

Then instead of receiving [(<Table1>, None)] I receive [(None, None)]

If I change my code a little bit:

sql = text('SELECT t1.*, t2.id, t2.name,t1.id FROM table1 t1 '
               'LEFT JOIN table2 t2 ON t1.id=t2.table1_id ')
query = db.session.query(Table1, Table2, Table1.id).from_statement(sql).params()
table1_table2_tuple_list = query.all()

Then I receive:

[<Table1>, <Table2>, 1]

But in this case I'm not even sure if this is correct because I match two columns from the sql to model Table2 that has three columns. Not sure at all why this is working, but everything seems on place. Still is not what I want, because I don't want to return to this query and to specify again and again new columns if there are such for Table2

What I need is a way to select from two tables with pure sql , and to match the result to my models. Also I want to have result even in the cases when one of the tables doesn't have value in it's result columns. Hope I am clear. My goal is to receive

[(<Table1>, None)]

when the query is for all colums (SELECT t1.*, t2.*) and there is LEFT JOIN with null added.

Answer 1

The problem is that your tables have columns with the same names. SqlAlchemy after executing your query tries to match resulting columns by names (not by positions) and choose some matching which fits (in this case SA matched columns of table2 with fields of Table1 and vice versa). There is a way to specify matching with .columns() method but I would suggest you to consider using orm-functions of SqlAlchemy instead whenever possible unless you have strong reasons against it.