["python","sql","sqlalchemy"]
How to count rows with SELECT COUNT(*) with SQLAlchemy?

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 execute(). If I use: session.query(table).count() 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 filter andjoin conditions already specified.