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

User · Accepted Answer

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.

User · Answer

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

User · Answer

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

User · Answer

If you are using the SQL Expression Style approach there is another way to construct the count statement if you already have your table object   Preparations to get the table object  There are also different ways   import sqlalchemy  database engine   sqlalchemy create engine  connection string      Populate existing database via reflection into sqlalchemy objects database metadata   sqlalchemy MetaData   database metadata reflect bind database engine   table object   database metadata tables get  table name     This is just for illustration how to get the table object                       Issuing the count query on the table object  query   table object count     This will produce something like  where id is a primary key column in  table name  automatically selected by sqlalchemy    SELECT count table name id  AS tbl row count FROM table name   count result   database engine scalar query

User · Answer

Query for just a single known column   session query MyTable col1  count

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

Usage from the SQL Expression layer

Usage from the ORM layer

Examples related to python

Examples related to sql

Examples related to sqlalchemy