Geek Answers Handbook

SQLAlchemy: column name prefixed in union_all 3-table subquery

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 is what I'm trying to do is

from sqlalchemy import union_all

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

column names in "u1" are retrieved 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 the 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 sees the first key in 'u1' and 'v1' (which is a subquery of u1), are different. I expect them to be the same.

to avoid this, I would name the column names in the queries "x" and "y"

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 and 2 and it works fine.

Now my problem

I want to make union_all from "u1" with the third query "z", I marked 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 requests

>>>union_xyz = u1.union_all(z)

"union_xyz" ,

>>>sub_xyz = union_xyz.subquery()

, sub_xyz

, , ,

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

?

sqlalchemy doc

sqlalchemy CTE?

+4
1

, , c , . , :

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

inc_type_md_col, cnt_col ctr_dt_col , , ,

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

More articles: