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

Addition to the Usage from the ORM layer in the accepted answer: count(*) can be done for ORM using the query.with_entities(func.count()), like this:

session.query(MyModel).with_entities(func.count()).scalar()

It can also be used in more complex cases, when we have joins and filters - the important thing here is to place with_entities after joins, otherwise SQLAlchemy could raise the Don't know how to join error.

For example:

• we have User model (id, name) and Song model (id, title, genre)
• we have user-song data - the UserSong model (user_id, song_id, is_liked) where user_id + song_id is a primary key)

We want to get a number of user's liked rock songs:

SELECT count(*) 
  FROM user_song
  JOIN song ON user_song.song_id = song.id 
 WHERE user_song.user_id = %(user_id)
   AND user_song.is_liked IS 1
   AND song.genre = 'rock'

This query can be generated in a following way:

user_id = 1

query = session.query(UserSong)
query = query.join(Song, Song.id == UserSong.song_id)
query = query.filter(
    and_(
        UserSong.user_id == user_id, 
        UserSong.is_liked.is_(True),
        Song.genre == 'rock'
    )
)
# Note: important to place `with_entities` after the join
query = query.with_entities(func.count())
liked_count = query.scalar()

Complete example is here.

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.

Query for just a single known column:

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

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 func

session.query(func.count(User.id)).scalar()