SQLAlchemy SELECT WITH子句/语句(pgsql) [英] SQLAlchemy SELECT WITH clause/statement (pgsql)
本文介绍了SQLAlchemy SELECT WITH子句/语句(pgsql)的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
如何执行在SQLalchemy中使用 WITH
的SQL查询?
How would one execute a SQL query that uses WITH
in sqlalchemy?
WITH foo AS (...), bar as (...) SELECT (...)
http://www.postgresql.org/docs/9.1 /static/queries-with.html
使用postgres。
Using postgres.
推荐答案
SQLAlchemy作者在此电子邮件中的这段代码线程可能有帮助
This piece of code from the author of SQLAlchemy in this email thread might be of help
q1 = s.query(distinct(Appl.refid), Appl).\
filter(Appl.lastname.ilike('Williamson%')).\
filter(Appl.firstname.ilike('d%')).\
group_by(Appl).\
order_by(Appl.refid, Appl.appldate.desc())
q1 = q1.cte('distinct_query')
q2 = s.query(q1).order_by(q1.c.lastname, q1.c.firstname)
q2.all()
输出为:
WITH distinct_query AS
(SELECT DISTINCT appl.refid AS anon_1, appl.id AS id, appl.firstname AS firstname, appl.lastname AS lastname, appl.refid AS refid, appl.appldate AS appldate
FROM appl
WHERE appl.lastname ILIKE %(lastname_1)s AND appl.firstname ILIKE %(firstname_1)s GROUP BY appl.id, appl.firstname, appl.lastname, appl.refid, appl.appldate ORDER BY appl.refid, appl.appldate DESC)
SELECT distinct_query.anon_1 AS distinct_query_anon_1, distinct_query.id AS distinct_query_id, distinct_query.firstname AS distinct_query_firstname, distinct_query.lastname AS distinct_query_lastname, distinct_query.refid AS distinct_query_refid, distinct_query.appldate AS distinct_query_appldate
FROM distinct_query ORDER BY distinct_query.lastname, distinct_query.firstname
这篇关于SQLAlchemy SELECT WITH子句/语句(pgsql)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文