12

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

Kirill Malovitsa
  • 123
  • 1
  • 5

1 Answers1

16

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)
van
  • 74,297
  • 13
  • 168
  • 171
  • 1
    Yes, it's what i mean - more control over name. My mistake was that i haven't seen that sqlalchemy.sql.functions.FunctionElement is descendant of sqlalchemy.sql.expression.ColumnElement, and it has label() – Kirill Malovitsa Sep 10 '15 at 20:44