sqlalchemy IS NOT NULL select
PythonSqlalchemyPython Problem Overview
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)
Python Solutions
Solution 1 - Python
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.
Solution 2 - Python
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
Solution 3 - Python
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