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 · Answer

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

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

I managed to render the following SELECT with SQLAlchemy on both layers  SELECT count    AS count 1 FROM  quot table quot   Usage from the SQL Expression layer from sqlalchemy import select  func  Integer  Table  Column  MetaData  metadata   MetaData    table   Table  quot table quot   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

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

Examples related to python

Examples related to sql

Examples related to sqlalchemy