SQLAlchemy filter for None/NULL Value - WORKS - but how to have it in_ a list?

Question

I want to filter my data for None or in MySQL dialect NULL values in a column.

This is easily done by:

db.query(models.Data).filter(models.Data.multiplier == None).all()

Well, but I am building a kind of advanced filter & sort function. Therefore, I have a list with values I want to filter for, e.g.:

[1,2,5]

Filtering my data for this is easy, too:

db.query(models.Data).filter(models.Data.multiplier.in_([1,2,5])).all()

But now I also want to allow a None value in my list:

[1,2,5,None]

Filtering:

db.query(models.Data).filter(models.Data.multiplier.in_([1,2,5,None])).all()

However, all rows having NULL as value in the multiplier column are NOT returned. Why is the usage of None not working with the in_ function? How to solve this?

Answer 1

NULL doesn't compare as equal to NULL, so

SELECT bar 
  FROM foo
  WHERE bar IN (1, 2, 3 , NULL)

will return rows where bar is 1 or 2 or 3 but not if bar is NULL.

Instead you need offer the NULL case as an alternative to the IN case.

SELECT bar FROM foo WHERE bar IN (1, 2, 3) OR bar IS NULL

or in SQLAlchemy terms

import sqlalchemy as sa

...

result = (session.query(Foo)
                 .filter(
                     sa.or_(
                         Foo.bar.in_([1, 2, 3]),
                         Foo.bar == None
                     )
                 )
)

Answer 2

You can make comparisons using None (to match NULL) using the is_() column operator. To check that a column is not NULL, you can use is_not.

As in:

result = Foo.filter(
    sa.or_(
        Foo.bar.in_([1,2,3]), 
        Foo.bar.is_(None)
    )
)

But yes, listen to snakecharmerb when you're filtering with in_.