How to count rows with SELECT COUNT(*) with SQLAlchemy?

Question

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.

Answer 1

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.

Answer 2

Query for just a single known column:

session.query(MyTable.col1).count()