如何使用 SQLAlchemy 使用 SELECT COUNT(*) 计算行数? [英] How to count rows with SELECT COUNT(*) with SQLAlchemy?

问题描述

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

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)

在带有 InnoDB 的 MySQL 中明显变慢.我正在寻找一种不需要表具有已知主键的解决方案，如 使用 SQLAlchemy 获取表中的行数.

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.

推荐答案

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

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

SELECT count(*) AS count_1
FROM "table"

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)

ORM 层的使用

你只是将 Query 子类化(你可能已经有了)并提供一个专门的 count() 方法，就像这个.

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

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

Please note that order_by(None) resets the ordering of the query, which is irrelevant to the counting.

使用此方法，您可以在任何 ORM 查询上使用 count(*)，这将满足所有 filter 和 join 条件指定.

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

这篇关于如何使用 SQLAlchemy 使用 SELECT COUNT(*) 计算行数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！