Python 如何使用 SQLAlchemy 使用 SELECT COUNT(*) 计算行数？

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

提问by tiho

I'd like to know if it's possible to generate a SELECT COUNT(*) FROM TABLEstatement in SQLAlchemy without explicitly asking for it with execute(). If I use:

我想知道是否可以SELECT COUNT(*) FROM TABLE在 SQLAlchemy 中生成语句而无需使用execute(). 如果我使用：

session.query(table).count()

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.

这在带有 InnoDB 的 MySQL 中要慢得多。我正在寻找一种不需要表具有已知主键的解决方案，如使用 SQLAlchemy 获取表中的行数中所建议的。

采纳答案by pi.

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

我设法在两个层上使用 SQLAlchemy 呈现以下 SELECT。

SELECT count(*) AS count_1
FROM "table"

Usage from the SQL Expression layer

SQL 表达式层的用法

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

ORM 层的使用

You just subclass Query(you have probably anyway) and provide a specialized count()method, like this one.

你只是子类化Query（你可能无论如何都有）并提供一个专门的count()方法，就像这个。

from sqlalchemy 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.

请注意，order_by(None)重置查询的顺序，这与计数无关。

Using this method you can have a count(*)on any ORM Query, that will honor all the filterandjoinconditions already specified.

使用此方法，您可以count(*)在任何 ORM 查询上使用 ，这将遵守所有已指定的filter和join条件。

回答by Nathan Villaescusa

Query for just a single known column:

仅查询单个已知列：

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

回答by melchtheitroad55

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:

我需要对具有许多连接的非常复杂的查询进行计数。我使用连接作为过滤器，所以我只想知道实际对象的数量。count() 是不够的，但我在这里的文档中找到了答案：

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

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() 

回答by Ole

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.

如果您正在使用 SQL 表达式样式方法，如果您已经拥有表对象，则还有另一种方法来构造计数语句。

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

发出计数查询 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)

回答by Boris Serebrov

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

除了接受的答案中ORM 层的Usage 之外：count(*) can be done for ORM using the query.with_entities(func.count())，如下所示：

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_entitiesafter joins, otherwise SQLAlchemy could raise the Don't know how to joinerror.

它也可以用于更复杂的情况，当我们有连接和过滤器时 - 这里重要的是放在with_entities连接之后，否则 SQLAlchemy 可能会引发Don't know how to join错误。

For example:

例如：

• we have Usermodel (id, name) and Songmodel (id, title, genre)
• we have user-song data - the UserSongmodel (user_id, song_id, is_liked) where user_id+ song_idis a primary key)

• 我们有User模型 ( id, name) 和Song模型 ( id, title, genre)
• 我们有用户歌曲数据 -UserSong模型 ( user_id, song_id, is_liked) 其中user_id+song_id是主键）

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.

完整的例子在这里。