Python 使用 SQLite 在 SQLAlchemy 中返回不同的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17223174/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Returning distinct rows in SQLAlchemy with SQLite
提问by Eli Courtwright
SQLAlchemy's Query.distinctmethod is behaving inconsistently:
SQLAlchemy 的Query.distinct方法行为不一致：
>>> [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 distinctclause 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?
所以第二种形式给出了正确的结果，但第一种形式没有。这似乎发生在 SQLite 上，但不是发生在 Postgres 上。我有一个函数，它传递了一个查询对象以对其distinct应用一个子句，因此使用上面的第二种方法重写所有内容是非常困难的。有什么明显的东西我失踪了吗？
采纳答案by alecxe
According to the docs:
根据文档：
When present, the Postgresql dialect will render a DISTINCT ON (>) construct.
当存在时，Postgresql 方言将呈现一个 DISTINCT ON (>) 构造。
So, passing column expressions to distinct()works for PostgreSQL only (because there is DISTINCT ON).
因此，将列表达式传递给distinct()仅适用于 PostgreSQL（因为有DISTINCT ON）。
In the expression session.query(Tag).distinct(Tag.name).count()sqlalchemy ignores Tag.nameand produces the query (distinct on all fields):
在表达式中session.query(Tag).distinct(Tag.name).count()sqlalchemy 忽略Tag.name并生成查询（在所有字段上都不同）：
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:
正如你所说，在你的情况下distinct(Tag.name)被应用 - 所以不要只count()考虑使用这个：
session.query(Tag).distinct(Tag.name).group_by(Tag.name).count()
Hope that helps.
希望有帮助。
回答by mata
When you use session.query(Tag)you alway query for the whole Tagobject, so if your table contains other columns it won't work.
当您使用时，session.query(Tag)您总是查询整个Tag对象，因此如果您的表包含其他列，它将不起作用。
Let's assume there is an idcolumn, then the query
假设有一id列，那么查询
sess.query(Tag).distinct(Tag.name)
will produce:
将产生：
SELECT DISTINCT tag.id AS tag_id, tag.name AS tag_name FROM tag
The argument to the distinct clause is ignored completely.
完全忽略了distinct 子句的参数。
If you really only want the distinct names from the table, you must explicitly select only the names:
如果您真的只想要表中不同的名称，则必须明确地仅选择名称：
sess.query(Tag.name).distinct()
produces:
产生：
SELECT DISTINCT tag.name AS tag_name FROM tag
![如果数字介于两个值之间，则返回 [Python]](/res/img/loading.gif)