CODEWITHSUNDEEP
pythonsqlalchemy
Kirill Malovitsa
Kirill Malovitsa

Reputation: 123

Define name for column func.count in sqlalchemy

There are two tables 

Tbl1 = Table(
    'tbl_1', metadata,
    Column('id', Integer, primary_key=True),
    Column('user_id', Integer, ForeignKey('user.id'), nullable=False),
    ...other columns
)

and 

Tbl2 = Table(
    'tbl_2', metadata,
    Column('id', Integer, primary_key=True),
    Column('user_id', Integer, ForeignKey('user.id'), nullable=False),
    ...other columns
)

I want to count all data in both tables for one user.

q1 = Tbl1.count().where(Tbl1.c.user_id == some_id)
q2 = Tbl2.count().where(Tbl2.c.user_id == some_id)
union = q1.union(q2).alias('uni')
query = sqlalchemy.select([sqlalchemy.func.sum(union.c.tbl_row_count)], from_obj=union)

And question is - how to set a column name for union count column, instead of looking thrue the internals? union.c.tbl_row_count i saw only in debug mode.

Second try was: 

import sqlalchemy as sa

q1 = sa.select([sa.func.count(Tbl1.c.id).alias('cnt')]).where(Tbl1.c.user_id == some_id)
q2 = sa.select([sa.func.count(Tbl2.c.id).alias('cnt')]).where(Tbl2.c.user_id == some_id)
union = q1.union(q2).alias('uni')

But in that case internal name for column was uni.c.count_1

Upvotes: 12

Views: 6591

Answers (1)

van
van

Reputation: 77072

Indeed, it is better to be in control of naming in such cases. See code below for code which should produce SQL identical to what you have right now:

q1 = select([func.count(Tbl1.c.id).label("xxx")]
            ).where(Tbl1.c.user_id == some_id)
q2 = select([func.count(Tbl2.c.id).label("xxx")]
            ).where(Tbl2.c.user_id == some_id)
union = q1.union(q2).alias('uni')
query = select([func.sum(union.c.xxx).label("total_xxx")], from_obj=union)

Upvotes: 16

Related Questions