181

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

SELECT * 
FROM table 
WHERE YourColumn IS NOT NULL;

How can I do the same with SQLAlchemy filters?

select = select(table).select_from(table).where(all_filters)
Alexander
  • 3,129
  • 2
  • 19
  • 33
salamey
  • 3,633
  • 10
  • 38
  • 71

3 Answers3

221

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.

Martijn Pieters
  • 1,048,767
  • 296
  • 4,058
  • 3,343
153

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

Josh
  • 1,306
  • 2
  • 17
  • 28
Filipe Spindola
  • 1,825
  • 1
  • 15
  • 17
  • 5
    Beyond making pep8 happy, I think this is a better solution because `NULL` is not a valid as the RHS of `!=` in SQL and using `isnot` better conveys your intentions for what you want the generated statement to look like. – Josh Sep 02 '16 at 16:04
  • 2
    @Josh: SQLAlchemy won't emit `!= NULL` however, even if you use `column != None` on the Python side; you get `IS NOT NULL`. Using `.isnot()` lets you *force* `IS NOT` on other types however (think `.isnot(True)` against boolean columns, for example). – Martijn Pieters Sep 15 '16 at 06:23
69

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
Ahmed
  • 2,825
  • 1
  • 25
  • 39
Matthew Moisen
  • 16,701
  • 27
  • 128
  • 231