SQLAlchemy : column name prefixed on the subquery of union_all of 3 tables

Question

Here is my mssql code snippet

cnt = func.count(pvr_svc.ic_idn).label('cnt')

x = session.query(pvr_svc.inc_type_md, cnt, cast(pvr_svc.crt_dt,DATE)
         .label('crt_dt'))
         .filter(pvr_svc.inc_type_md.in_(['PM','OM','OP-HU']))
         .group_by(cast(pvr_svc.crt_dt, DATE), pvr_svc.inc_type_md)

y = session.query(pvr_svc.inc_type_md, cnt, cast(pvr_svc.crt_dt,DATE)
         .label('crt_dt'))
         .filter(pvr_svc.gold_idn==2)
         .group_by(cast(pvr_svc.crt_dt, DATE), pvr_svc.inc_type_md)

and this what i am trying to do is

from sqlalchemy import union_all

u1 = x.union_all(y)     # ----- 1

the column names in "u1" are extracted as follows

 >>>[i['name'] for i in u1.column_descriptions]  

 >>>['inc_type_md', 'cnt', 'crt_dt']   # column names

now if i want to use 'u1' in future this is what i do

>>>v1 = u1.subquery()    #------ 2

to access the column names from "v1" i do this

>>>v1.c.keys()

>>>[u'pvr_svc_inc_type_md', u'cnt', u'crt_dt']

Now, if u see the first key in 'u1' and 'v1'(which is subquery of u1) are different. I am expecting them to be same.

to avoid this i would label the column names in "x" and "y" queries

x = session.query(pvr_svc.inc_type_md.label('inc_type_md'), cnt, cast(pvr_svc.crt_dt,DATE).label('crt_dt')
         .label('crt_dt'))
         .filter(pvr_svc.inc_type_md.in_(['PM','OM','OP-HU']))
         .group_by(cast(pvr_svc.crt_dt, DATE), pvr_svc.inc_type_md)

y = session.query(pvr_svc.inc_type_md.label('inc_type_md'), cnt, cast(pvr_svc.crt_dt,DATE).label('crt_dt')
         .label('crt_dt'))
         .filter(pvr_svc.gold_idn==2)
         .group_by(cast(pvr_svc.crt_dt, DATE), pvr_svc.inc_type_md)

and repeat the steps 1 and 2, and it works fine.

Now my problem is

I want to do a union_all of "u1" with the third query "z", I have labeled the column names in "z"

z = session.query(pvr_svc.inc_type_md.label('inc_type_md'), cnt, cast(pvr_svc.crt_dt,DATE).label('crt_dt')
         .label('crt_dt'))
         .filter(pvr_svc.gold_idn==4)
         .group_by(cast(pvr_svc.crt_dt, DATE), pvr_svc.inc_type_md)

i would do this to make union_all of 3 queries

>>>union_xyz = u1.union_all(z)

and now i want use "union_xyz" in future queries, so i create a subquery out of this

>>>sub_xyz = union_xyz.subquery()

now the problem is, the column names from sub_xyz are prefixed with some integers

so,, this what i am getting,

>>>sub_xyz.c.keys()
>>>[u'%(2911061292 anon)s_inc_type_md', u'%(2911061292 anon)s_cnt', u'%(2911061292 anon)s_crt_dt']

How to avoid the prefixing of the column names?

I went through the sqlalchemy doc

I found a similiar question at How can I prevent sqlalchemy from prefixing the column names of a CTE?

Answer 1

I don't know of any way to prevent the prefixing, but you can convert the c attribute of your subquery into a list or tuple and then access the individual columns by position. Here an example that uses tuple unpacking:

>>> inc_type_md_col, cnt_col, ctr_dt_col = tuple(subq_xyz.c)

inc_type_md_col, cnt_col, and ctr_dt_col are then valid column objects and you can do whatever you like with them, e.g.

>>> session.query(inc_type_md_col).filter(cnt_col > 0)