Fix Python – sqlalchemy IS NOT NULL select


Asked By – salamey

How can I add the filter as in SQL to select values that are NOT NULL from a certain column ?

FROM table 

How can I do the same with SQLAlchemy filters?

select = select(table).select_from(table).where(all_filters) 

Now we will see solution for issue: sqlalchemy IS NOT NULL select


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.


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

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

This question is answered By – Martijn Pieters

This answer is collected from stackoverflow and reviewed by FixPython community admins, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0