Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
525 views
in Technique[技术] by (71.8m points)

python - Can we make correlated queries with SQLAlchemy

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 ?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

1.4m articles

1.4m replys

5 comments

57.0k users

...