Checking None With SQLAlchemy

Checking for None occurs often enough that you need to know this little tip.

The SQL

To build this SQL Statement:

1
2

SELECT * FROM event
WHERE confirmed_at IS NULL

Not How To Write It

You shouldn’t declare your SQLAlchemy query as follows:

1
2
3

query = session.query(EventEntity).filter(
    EventEntity.confirmed_at is None,
)

It feels natural if you have written Python code before, but it won’t work.

Correct Syntax

Instead, use SqlAlchemy like this, using == operator :

1
2
3

query = session.query(EventEntity).filter(
    EventEntity.confirmed_at == None,
)

Why

Because SQLAlchemy uses magic methods (or operator overloading) to create SQL constructs, it can only handle operators such as != or ==, but isn’t able to work with is (which is a very valid Python construct).

Source: Stackoverflow

Photo by Pixabay