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