如何在SQLAlchemy和postgresql中结合两个子查询

概述原始SQL需要： SELECT idFROM (SELECT some_table.id FROM some_table WHERE some_table.some_field IS NULL) AS subq1 UNION (SELECT some_table.id WHERE some_table.some_field IS NOT NULL)LIMIT 10; 原始sql需要：

SELECT    IDFROM   (SELECT some_table.ID FROM some_table WHERE some_table.some_fIEld IS NulL) AS subq1   UNION   (SELECT some_table.ID WHERE some_table.some_fIEld IS NOT NulL)liMIT 10;

这是python代码：

import sqlalchemySOME_table = sqlalchemy.table( 'some_table',sqlalchemy.MetaData(),sqlalchemy.Column('ID',sqlalchemy.Integer,primary_key=True),sqlalchemy.Column('some_fIEld',sqlalchemy.Text))stmt_1 = sqlalchemy.sql.select(SOME_table.columns).where(SOME_table.columns.some_fIEld != None)stmt_2 = sqlalchemy.sql.select(SOME_table.columns).where(SOME_table.columns.some_fIEld == None)# This gets a programming error.stmt_1.union(stmt_2).limit(10);

这是输出的sql(带有参数交换),它会收到此错误：
错误：“UNION”处或附近的语法错误：

SELECT some_table.ID,some_table.some_fIEldFROM some_tableWHERE some_table.some_fIEld IS NOT NulL liMIT 10 UNION SELECT some_table.ID,some_table.some_fIEldFROM some_tableWHERE some_table.some_fIEld IS NulL liMIT 10 liMIT 10

我如何为子查询添加别名？

我用了一点点不同的方法：

# the first subquery,select all IDs from SOME_table where some_fIEld is not NulLs1 = select([SOME_table.c.ID]).where(SOME_table.c.some_fIEld != None)# the second subquery,select all IDs from SOME_table where some_fIEld is NulLs2 = select([SOME_table.c.ID]).where(SOME_table.c.some_fIEld != None)# union s1 and s2 subquerIEs together and alias the result as "alias_name"q = s1.union(s2).alias('alias_name')# run the query and limit the aliased result to 10session.query(q).limit(10)

这是生成的sql：

SELECT alias_name.ID AS alias_name_ID FROM (SELECT some_table.ID AS ID FROM some_table WHERE some_table.some_fIEld IS NOT NulL UNION SELECT some_table.ID AS ID FROM some_table WHERE some_table.some_fIEld IS NulL) AS alias_name liMIT 10

我想这就是你想要的结果.

总结

以上是内存溢出为你收集整理的如何在SQLAlchemy和postgresql中结合两个子查询全部内容，希望文章能够帮你解决如何在SQLAlchemy和postgresql中结合两个子查询所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。