原始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)
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
我想这就是你想要的结果.