Tag Archives: postgresql

Printing actual SQLAlchemy queries

Hi there. Long time no post.

I just spent a couple hours debugging a query with a DISTINCT ON clause. The DISTINCT ON clause is a PostgreSQL-specific extension to the standard DISTINCT clause, which lets you specify the columns on which you want PostgreSQL to operate for filtering out duplicate rows.

With the SQLAlchemy ORM (using the declarative extension,) here is one way to write a query to retrieve all unique names from a name table:

>>> q = DBSession.query(model.Name).distinct(model.Name.value) \
                 .order_by(model.Name.value)

Now, if you’d want to see what kind of SQL query the ORM will generate you’d print it like so:

>>> print str(q)
SELECT DISTINCT name.id AS name_id, name.value AS name_value
FROM name ORDER BY name.value

But as you can see, there’s no DISTINCT ON here. Dang. Continue reading Printing actual SQLAlchemy queries

Share