Select NULL Values in SQLAlchemy

Solution 1:

For SQLAlchemy 0.7.8 and older

(as indicated by @augurar): Because sqlalchemy uses magic methods (operator overloading) to create SQL constructs, it can only handle operator such as != or ==, but is not able to work with is (which is a very valid Python construct).

Therefore, to make it work with sqlalchemy, you should use:

...filter(or_(people.marriage_status!='married', people.marriage_status == None))

, basically replace the is None with == None. In this case your query will be translated properly to the following SQL:

SELECT people.name AS people_name, people.marriage_status AS people_marriage_status 
FROM people 
WHERE people.marriage_status IS NULL OR people.marriage_status != ?

See IS NULL in the documentation.

Solution 2:

Since SQLAlchemy 0.7.9 you may use the is_ (or is_not) method of the column.

A filter expression like:

filter(or_(people.marriage_status!='married', people.marriage_status.is_(None)))

will generate the parameterized SQL:

WHERE people.marriage_status != %(status_1)s OR people.marriage_status IS NULL

Solution 3:

An elegant way to write it would be to use is_ and is_not, as shown in the following example:

query.filter(people.student.is_not(None)) 
query.filter(people.student.is_(None))