CODEWITHSUNDEEP
pythonsqldatabasesqlalchemy
Jerry
Jerry

Reputation: 2567

Select NULL Values in SQLAlchemy

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?

Upvotes: 103

Views: 183486

Answers (6)

mitesh keswani
mitesh keswani

Reputation: 153

Additionally, from sqlalchemy documentation, you could use below snippet https://docs.sqlalchemy.org/en/20/core/operators.html

from sqlalchemy import null
column.is_(null())
column.is_not(null())

Upvotes: 1

jsnow
jsnow

Reputation: 1858

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

Upvotes: 131

van
van

Reputation: 76962

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.

Upvotes: 190

Yourstruly
Yourstruly

Reputation: 699

The question is quite old, but for completeness, here the version I prefer for readability:

import sqlalchemy as sa

query.filter(people.marriage_status == sa.null()) 
query.filter(people.marriage_status != sa.null())

Upvotes: 15

Eds_k
Eds_k

Reputation: 1134

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))

Upvotes: 34

Jonathan Vanasco
Jonathan Vanasco

Reputation: 15680

i ran into a similar problem

https://groups.google.com/forum/?fromgroups#!topic/sqlalchemy/EVpxsNp5Ifg%5B1-25%5D

short answer: - there is not a column operator for IS (NOT) NULL now, but there will be

in the meantime you can use either:

filter(tablename.is_deleted.op("IS NOT")(True))

filter(coalesce(tablename.is_deleted, False) != True)

Upvotes: 1

Related Questions