具有不同列数的 SQLAlchemy 联合

Question

我需要在 SQLAlchemy 中执行 2 选择。例如:

select1 = Session.query(col1, col2, col3, col4).filter(...)
select2 = Session.query(col1, "", "", col4).filter(...)
result = select1.union(select2).all()

问题是这样的。我不知道如何用 SQLAlchemy 在 select2 中写入“”值。

谢谢。

Answer 1

您可能想要 column 的某种组合, literal_column或 null ，位于 sqlalchemy.sql模块:

>>> print Query((t.c.col1, 
...              t.c.col2, 
...              t.c.col3,
...              t.c.col4)) \
...   .union(
...       Query((t.c.col1,
...              sqlalchemy.sql.null().label('c2'), 
...              sqlalchemy.sql.literal_column('""').label('c3'),
...              t.c.col2))
...         )
SELECT anon_1.table_col1 AS anon_1_table_col1, anon_1.table_col2 AS anon_1_table_col2, anon_1.table_col3 AS anon_1_table_col3, anon_1.table_col4 AS anon_1_table_col4 
FROM (SELECT "table".col1 AS table_col1, "table".col2 AS table_col2, "table".col3 AS table_col3, "table".col4 AS table_col4 
FROM "table" UNION SELECT "table".col1 AS table_col1, NULL AS c2, "" AS c3, "table".col2 AS table_col2 
FROM "table") AS anon_1
>>>