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?
Advertisement
Answer
For SQLAlchemy 0.7.9 and newer Please use the answer from @jsnow. !!!
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.