Select NULL Values in SQLAlchemy

Question

Here's my (PostgreSQL) table --

test=> create table people (name varchar primary key,                             marriage_status varchar) ;   test=> insert into people values ('Ken', 'married'); test=> insert into people values ('May', 'single'); test=> insert into people values ('Joe', NULL); 

I want to select all people that are not known to be married, i.e., including those with NULL marriage_status.

This does not work --

test=> select * from people where marriage_status != 'married' ;   name | marriage_status  ------+-----------------  May  | single (1 row) 

Of course this does --

test=> select * from people where marriage_status != 'married'        or marriage_status is NULL ;   name | marriage_status  ------+-----------------  May  | single  Joe  |  

The problem is that I'm accessing it from SQLAlchemy with --

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

which gets translated to --

SELECT people.name as name,        people.marriage_status as marriage_status FROM people  WHERE people.marriage_status != %(status_1)s OR False sqlalchemy.engine.base.Engine.... {'status_1': 'married'} 

And does not work --

test=> select * from people where marriage_status != 'married'        or False;   name | marriage_status  ------+-----------------  May  | single (1 row) 

neither does --

test=> select * from people where marriage_status != 'married'        or NULL;   name | marriage_status  ------+-----------------  May  | single (1 row) 

How should I select NULL values through SQLAlchemy?

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

Answer 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