使用 SQLite 在 SQLAlchemy 中返回不同的行 [英] Returning distinct rows in SQLAlchemy with SQLite

问题描述

SQLAlchemy 的 Query.distinct 方法正在运行不一致:

<预><代码>>>>[session.query(Tag).all() 中标签的tag.name][u'Male', u'Male', u'Ninja', u'Pirate']>>>session.query(Tag).distinct(Tag.name).count()4>>>session.query(Tag.name).distinct().count()3

所以第二种形式给出了正确的结果，但第一种形式没有.这似乎发生在 SQLite 上，但不是发生在 Postgres 上.我有一个函数，它传递一个查询对象来应用 distinct 子句，因此使用上面的第二种方法重写所有内容是非常困难的.有什么明显我遗漏的地方吗?

解决方案

根据文档:

<块引用>

当存在时，Postgresql 方言将呈现一个 DISTINCT ON(>) 构造.

因此，将列表达式传递给 distinct() 仅适用于 PostgreSQL(因为有 DISTINCT ON).

在表达式 session.query(Tag).distinct(Tag.name).count() 中，sqlalchemy 忽略 Tag.name 并生成查询(在所有字段):

SELECT DISTINCT tag.country_id AS tag_country_id, tag.name AS tag_nameFROM 标签

正如您所说，在您的情况下应用了 distinct(Tag.name) - 因此，而不是仅仅 count() 考虑使用这个:

session.query(Tag).distinct(Tag.name).group_by(Tag.name).count()

希望有所帮助.

SQLAlchemy's Query.distinct method is behaving inconsistently:

>>> [tag.name for tag in session.query(Tag).all()]
[u'Male', u'Male', u'Ninja', u'Pirate']
>>> session.query(Tag).distinct(Tag.name).count()
4
>>> session.query(Tag.name).distinct().count()
3

So the second form gives the correct result but the first form does not. This appears to happen with SQLite but NOT with Postgres. I have a function which is passed a query object to have a distinct clause applied to it, so it would be highly difficult to rewrite everything top use the second approach above. Is there something obvious that I'm missing?

解决方案

According to the docs:

When present, the Postgresql dialect will render a DISTINCT ON (>) construct.

So, passing column expressions to distinct() works for PostgreSQL only (because there is DISTINCT ON).

In the expression session.query(Tag).distinct(Tag.name).count() sqlalchemy ignores Tag.name and produces the query (distinct on all fields):

SELECT DISTINCT tag.country_id AS tag_country_id, tag.name AS tag_name 
FROM tag

As you said, in your case distinct(Tag.name) is applied - so instead of just count() consider using this:

session.query(Tag).distinct(Tag.name).group_by(Tag.name).count()

Hope that helps.

这篇关于使用 SQLite 在 SQLAlchemy 中返回不同的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！