Proper “And” Syntax With SQLAlchemy

The Goal

To build this SQL Statement:

1
2
3
4
5


SELECT * FROM event
WHERE
status IN ('NEW', 'TODO') AND
confirmed_at IS NULL AND
created_at <= p_cutoff_datetime

How Not To Write the SQLAlchemy Query

You shouldn’t declare your SQLAlchemy query as follows:

1
2
3
4
5


            query = session.query(EventEntity).filter(
                    EventEntity.status.in_(statuses),
                    EventEntity.confirmed_at == None,
                    EventEntity.created_at <= cutoff_datetime
            )

It will output the following SQL statement:

1
2
3


SELECT * FROM event
WHERE
0 != 1 -- 🤔

The 0 != 1 is SQLAlchemy’s way of creating a condition that always returns false.

Proper Syntax

Instead, use SQLAlchemy like this, wrapping your conditions with the and_ operator :

1
2
3
4
5
6
7
8
9


            from sqlalchemy import and_

            query = session.query(EventEntity).filter(
                and_(
                    EventEntity.status.in_(statuses),
                    EventEntity.confirmed_at == None,
                    EventEntity.created_at <= cutoff_datetime
                )
            )

That’s it for this one!

Follow me

Thanks for reading this article. Make sure to follow me on X, subscribe to my Substack publication and bookmark my blog to read more in the future.

Photo by Juan Pablo Serrano