I’d like to know if it’s possible to generate a
SELECT COUNT(*) FROM TABLE statement in SQLAlchemy without explicitly asking for it with
If I use:
then it generates something like:
SELECT count(*) AS count_1 FROM (SELECT table.col1 as col1, table.col2 as col2, ... from table)
which is significantly slower in MySQL with InnoDB. I am looking for a solution that doesn’t require the table to have a known primary key, as suggested in Get the number of rows in table using SQLAlchemy.
I managed to render the following SELECT with SQLAlchemy on both layers.
SELECT count(*) AS count_1 FROM "table"
Usage from the SQL Expression layer
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)
Usage from the ORM layer
You just subclass
Query (you have probably anyway) and provide a specialized
count() method, like this one.
from sqlalchemy.sql.expression 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.