Combining SQLAlchemy with different number of columns

Question

I need to execute 2 select in SQLAlchemy. For instance:

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

The problem is that. I do not know how to write the value "" in select2 using SQLAlchemy.

Thanks.

Answer 1

You probably need a combination of column , literal_column or null , which are in the sqlalchemy.sql module:

 >>> print Query((tccol1, ... tccol2, ... tccol3, ... tccol4)) \ ... .union( ... Query((tccol1, ... sqlalchemy.sql.null().label('c2'), ... sqlalchemy.sql.literal_column('""').label('c3'), ... tccol2)) ... ) 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 >>>