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

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

here 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 trying is

from sqlalchemy import union_all u1 = x.union_all(y) # ----- 1

the column names in "u1" extracted follows

>>>[i['name'] in u1.column_descriptions] >>>['inc_type_md', 'cnt', 'crt_dt'] # column names

now if want utilize 'u1' in future do

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

to access column names "v1" this

>>>v1.c.keys() >>>[u'pvr_svc_inc_type_md', u'cnt', u'crt_dt']

now, if u see first key in 'u1' , 'v1'(which subquery of u1) different. expecting them same.

to avoid label column names in "x" , "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 steps 1 , 2, , works fine.

now problem is

i want union_all of "u1" 3rd query "z", have labeled 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 create union_all of 3 queries

>>>union_xyz = u1.union_all(z)

and want utilize "union_xyz" in future queries, create subquery out of this

>>>sub_xyz = union_xyz.subquery()

now problem is, column names sub_xyz prefixed integers

so,, getting,

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

how avoid prefixing of column names?

i went through sqlalchemy doc

i found similiar question @ how can prevent sqlalchemy prefixing column names of cte?

i don't know of way prevent prefixing, can convert c attribute of subquery list or tuple , access individual columns position. here illustration uses tuple unpacking:

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

inc_type_md_col, cnt_col, , ctr_dt_col valid column objects , can whatever them, e.g.

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

python sql sqlalchemy flask-sqlalchemy