SQLAlchemy : Column name on union_all

Question

Here is the mssql code snippet

(Select column_name_1 from table_name_1  with(nolock)  Where column_name_2='Y'
UNION ALL
Select column_name_1 from table_name_2  with(nolock)  Where column_name_2='Y'
)ae ON ae.column_name_1 = '1234'

I want to implement this in sqlalchemy and here is how i would approach it

q1 = session.query(table_name_1.column_name_1).filter(table_name_1.column_name_2=='Y')
q2 = session.query(table_name_2.column_name_1).filter(table_name_2.column_name_2=='Y')

q3 = q1.union_all(q2)

but

How would i get the column_name_1 from q3. How would i do this?

q3.column_name_1 == '1234'

went through the sqlalchemy doc

Found similar questions asked here

Answer 1

Code below should do it. Few notes:

• specify a label for a column, else sqlalchemy will create own unique name
• use sqlalchemy.sql.expression.union_all, which will produce Selectable and not a Query

Code:

q1 = session.query(table_name_1.column_name_1.label("column_name_1")).filter(table_name_1.column_name_2=='Y')
q2 = session.query(table_name_2.column_name_1.label("column_name_1")).filter(table_name_2.column_name_2=='Y')

q3 = union_all(q1, q2)
q3 = select([q3.c.column_name_1]).where(q3.c.column_name_1 == '1234')

Given your example, you could have actually added the filter directly to the original queries.