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

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?

Continue reading...