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?

Answer 1

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.

Answer 2

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