Menu
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
647
To access the column names we can use the method keys() on the result. It returns a list of column names. Since, we queried only three columns, we can view the same columns on the output as well.
SQLAlchemy Core The already created students table is referred which contains 4 columns, namely, first_name, last_name, course, score. But we will be only selecting a specific column. In the example, we have referred to the first_name and last_name columns. Other columns can also be provided in the entities list.
functions is a callable within the sqlalchemy. sql module of the SQLAlchemy project. ClauseElement, Select, column, expression, extract, operators, schema, select, sqltypes, and table are several other callables with code examples from the same sqlalchemy. sql package.
add a mapped entity to the list of result columns to be returned. method sqlalchemy.orm.Query. all() Return the results represented by this Query as a list. This results in an execution of the underlying SQL statement.
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
