代码之家 › 专栏 › 技术社区 › Wombatz

SQLAlchemy group_by SQLite与PostgreSQL

sqlalchemy group-by sqlite postgresql python

Wombatz · 技术社区 · 10 年前

对于我们正在构建的web应用程序,我们使用SQLite进行测试。最近我们想迁移到PostgreSQL。这就是问题的根源:

我们有这个SQLAlchemy模型(简化)

class Entity(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    i_want_this = db.Column(db.String)
    some_value = db.Column(db.Integer)

我想分组所有 Entity s由 some_value 我这样做(简化):

db.session.query(Entity, db.func.count()).group_by(Entity.some_value)

在SQLite中,这起了作用。现在回想起来,我认为这没有意义,但SQLite确实有意义。我无法确定哪些实体被退回。

现在在PostgrSQL中,我们得到了以下错误:

sqlalchemy.exc.ProgrammingError: (psycopg2.ProgrammingError) column "entity.id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT entity.id AS entity_id, entity.i_want_this AS entity_not...
^
[SQL: 'SELECT entity.id AS entity_id, entity.i_want_this AS entity_i_want_this, count(*) AS count_1 \nFROM entity GROUP BY entity.some_value']

这个错误完全有道理。

所以我的第一个问题是:为什么SQLite允许这样做,它是如何做到的(使用了什么隐藏聚合)?

我的第二个问题很明显:如何使用PostgreSQL?

我实际上只对伯爵和第一个感兴趣 i_want_this 价值所以我可以这样做:

groups = db.session.query(db.func.min(Entity.id), db.func.count()).group_by(Entity.some_value)

[(Entity.query.get(id_), count) for id_, count in groups]

但我不想要这些额外的 get 查询。

因此,我想选择第一个实体(id最小的实体)和分组的实体数 some_value(一些值) 或第一个 我想要这个 计数分组依据 some_value(一些值)

编辑要明确:

我想分组依据 some_value(一些值) ( 多恩 )
我想得到每组中的实体数( 多恩 )
我想得到最低的实体 id 在每组中( 需要帮助 )
或者我想得到 我想要这个 最低实体的值 身份证件 在每组中( 需要帮助 )

1 回复 | 直到 10 年前

sebastian 10 年前

关于第一个问题,请查看 documentation :

然后对结果集中的每个表达式进行一次求值行组。如果表达式是聚合表达式,则为对组中的所有行进行评估。否则,将对其进行评估针对组内任意选择的单个行。如果结果集中存在不止一个非聚集表达式, 则针对同一行计算所有这样的表达式。

关于第二个问题,考虑到SQLite中的当前查询也会返回或多或少的随机结果,您可能需要解释您实际想要实现什么。

编辑: 要获取每个组具有最小id的实体,可以使用 Query.select_from 构造:

import sqlalchemy.sql as sa_sql

# create the aggregate/grouped query
grouped = sa_sql.select([sa_sql.func.min(Entity.id).label('min_id')])\
                .group_by(Entity.some_value)\
                .alias('grouped')

# join it with the full entities table
joined = sa_sql.join(Entity, grouped, grouped.c.min_id == Entity.id)
# and let sqlalchemy pull the entities from this statement:
session.query(Entity).select_from(joined)

这将生成以下SQL:

SELECT entities.id AS entities_id,
       entities.i_want_this AS entities_i_want_this,
       entities.some_value AS entities_some_value 
FROM entities JOIN (SELECT min(entities.id) AS min_id
                    FROM entities GROUP BY entities.some_value) AS grouped
ON grouped.min_id = entities.id