sqlalchemy IS NOT NULL select

Question

column_obj != None will produce a IS NOT NULL constraint:

In a column context, produces the clause a != b. If the target is None, produces a IS NOT NULL.

or use is_not()^*:

Implement the IS NOT operator.

Normally, IS NOT is generated automatically when comparing to a value of None, which resolves to NULL. However, explicit usage of IS NOT may be desirable if comparing to boolean values on certain platforms.

Demo:

>>> from sqlalchemy.sql import column
>>> column('YourColumn') != None
<sqlalchemy.sql.elements.BinaryExpression object at 0x10f81aa90>
>>> print(column('YourColumn') != None)
"YourColumn" IS NOT NULL
>>> column('YourColumn').is_not(None)
<sqlalchemy.sql.elements.BinaryExpression object at 0x11081edf0>
>>> print(column('YourColumn').is_not(None))
"YourColumn" IS NOT NULL

You can't use is not None here, because the is not object identity inequality test can't be overloaded the way != can; you'll just get True instead as a ColumnClause instance is not the same object as the None singleton:

>>> column('YourColumn') is not None
True

---

^*) The method was formerly named isnot() and was renamed in SQLAlchemy 1.4. The old name is still available for backwards compatibility.

Starting in version 0.7.9 you can use the filter operator .isnot instead of comparing constraints, like this:

query.filter(User.name.isnot(None))

This method is only necessary if pep8 is a concern.

source: sqlalchemy documentation

In case anyone else is wondering, you can use is_ to generate foo IS NULL:

>>> from sqlalchemy.sql import column
>>> print(column('foo').is_(None))
foo IS NULL
>>> print(column('foo').isnot(None))
foo IS NOT NULL