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

Query for just a single known column:

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

I managed to render the following SELECT with SQLAlchemy on both layers.

SELECT count(*) AS count_1FROM "table"

Usage from the SQL Expression layer

from sqlalchemy import select, func, Integer, Table, Column, MetaDatametadata = 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 funcclass 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.

I needed to do a count of a very complex query with many joins. I was using the joins as filters, so I only wanted to know the count of the actual objects. count() was insufficient, but I found the answer in the docs here:

http://docs.sqlalchemy.org/en/latest/orm/tutorial.html

The code would look something like this (to count user objects):

from sqlalchemy import funcsession.query(func.count(User.id)).scalar()