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

发布时间：2020-12-13 16:31:38 所属栏目：百科来源：网络整理

导读：原始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 = sq

原始SQL需要：

SELECT
    id
FROM
   (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 sqlalchemy

SOME_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_field
FROM some_table
WHERE some_table.some_field IS NOT NULL
 LIMIT 10 UNION SELECT some_table.id,some_table.some_field
FROM some_table
WHERE some_table.some_field IS NULL
 LIMIT 10
 LIMIT 10

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

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

# the first subquery,select all ids from SOME_TABLE where some_field is not NULL
s1 = 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 NULL
s2 = 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 10
session.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

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

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!