Returning distinct rows in SQLAlchemy with SQLite

Question

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?

starball · Accepted Answer · 2022-12-25 00:44:33Z

92

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

edited Dec 25, 2022 at 0:44

starball♦

59k52 gold badges310 silver badges1k bronze badges

answered Jun 20, 2013 at 21:15

alecxe

476k127 gold badges1.1k silver badges1.2k bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Eli Courtwright Over a year ago

Thanks; I saw that comment in the docs about DISTINCT ON but since the docs didn't explicitly say 'and that's the only way that this works' or anything like it, I didn't realize that was implied.

Ilja Everilä Over a year ago

Downvoted because promoting the SQLite specific extension, which allows selecting non-aggregate columns that are not determined by the GROUP BY clause, producing indeterminate results (well not exactly here because of the count(), but correct solutions exist, like in the other answer).

alecxe Over a year ago

@IljaEverilä nice duplet downvote, totally by an accident.

alecxe Over a year ago

@IljaEverilä thanks for the comment though, it's not often that happens with a downvote, but certainly a desired behavior. I didn't really promote anything here, just tried to help answering an sqlite-specific question.

Ilja Everilä Over a year ago

Though this is horribly hindsighted, given you're answering an sqlite-specific question, why not drop the distinct(Tag.name) altogether, because it's useless in SQLite and the query as it is will be rejected by most SQL DBMS due to the grouping. On the double dv, I do share links to interesting q/a (one way or the other). This would make a nice dupe target, if not for the accepted answer, though you nicely explain why distinct() even accepts expressions as args.

mata · Accepted Answer · 2013-06-20 21:25:09Z

When you use session.query(Tag) you alway query for the whole Tag object, so if your table contains other columns it won't work.

Let's assume there is an id column, then the query

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.

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

Collectives™ on Stack Overflow

Returning distinct rows in SQLAlchemy with SQLite

2 Answers 2

5 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Linked

Related