python - Can we make correlated queries with SQLAlchemy

Question

I'm trying to translate this SQL query into a Flask-SQLAlchemy call:

SELECT *
FROM "ENVOI"
WHERE "ID_ENVOI" IN (SELECT d."ID_ENVOI"
                     FROM "DECLANCHEMENT" d
                     WHERE d."STATUS" = 0
                                    AND d."DATE" = (SELECT max("DECLANCHEMENT"."DATE")
                                    FROM "DECLANCHEMENT"
                                    WHERE "DECLANCHEMENT"."ID_ENVOI" = d."ID_ENVOI"))

As you can see, it uses subqueries and, most important part, one of the subqueries is a correlated query (it use d table defined in an outer query).

I know how to use subqueries with subquery() function, but I can't find documentation about correlated queries with SQLAlchemy. Do you know a way to do it ?

Answer 1

Yes, we can.

Have a look at the following example (especially the correlate method call):

from sqlalchemy import select, func, table, Column, Integer

table1 = table('table1', Column('col', Integer))
table2 = table('table2', Column('col', Integer))


subquery = select(
    [func.if_(table1.c.col == 1, table2.c.col, None)]
).correlate(table1)

query = (
    select([table1.c.col,
            subquery.label('subquery')])
    .select_from(table1)
)

if __name__ == '__main__':
    print(query)

will result in the following query

SELECT table1.col, (SELECT if(table1.col = :col_1, table2.col, NULL) AS if_1 
FROM table2) AS subquery 
FROM table1

As you can see, if you call correlate on a select, the given Table will not be added to it's FROM-clause. You have to do this even when you specify select_from directly, as SQLAlchemy will happily add any table it finds in the columns.