I'd like to know if it's possible to generate a
SELECT COUNT(*) FROM TABLE
SELECT count(*) AS count_1 FROM
(SELECT table.col1 as col1, table.col2 as col2, ... from table)
I managed to render the following SELECT with SQLAlchemy on both layers.
SELECT count(*) AS count_1 FROM "table"
from sqlalchemy import select, func, Integer, Table, Column, MetaData metadata = MetaData() table = Table("table", metadata, Column('primary_key', Integer), Column('other_column', Integer) # just to illustrate ) print select([func.count()]).select_from(table)
You just subclass
Query (you have probably anyway) and provide a specialized
count() method, like this one.
from sqlalchemy import func class BaseQuery(Query): def count_star(self): count_query = (self.statement.with_only_columns([func.count()]) .order_by(None)) return self.session.execute(count_query).scalar()
Please note that
order_by(None) resets the ordering of the query, which is irrelevant to the counting.
Using this method you can have a
count(*) on any ORM Query, that will honor all the
join conditions already specified.