Handling null values in a SQLAlchemy query - equivalent of isnull, nullif or coalesce

SQLAlchemy allows you to use the equivalent of SQL's COALESCE function to handle NULL values. I didn't find the documentation for this easy to understand, so here's a quick tutorial...


Depending on which database management system you're familiar with, you might have used ISNULL, NULLIF or COALESCE in a query to return a specific value in case the database column contains NULL.

My example concerns a PostgreSQL database with a table named CONFIG. I want to find a record that was least recently processed, according to its LAST_PROCESSED_AT column, or was never processed, indicated by LAST_PROCESSED_AT = NULL.

Here's the result of the query

select name, last_processed_at from config:

Note that the third row has a null value for LAST_PROCESSED_AT. Here's a revised query using SQL's coalesce function to map NULLs to the earliest date recognized by PostgreSQL:

select name, coalesce(last_processed_at, to_timestamp(0)) from config

Note the effect on the third row:

Armed with COALESCE, I can find the record I'm looking for with this query:

select name from config order by coalesce(last_processed_at, to_timestamp(0)) limit 1

The question is how to do the same thing with Python and SQLAlchemy. And the answer is this:

import datetime

from sqlalchemy.sql.functions import coalesce

my_config = session.query(Config).order_by(coalesce(Config.last_processed_at, datetime.date.min)).first()