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?

van · Accepted Answer · 2022-10-15 16:21:36Z

192

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.

edited Oct 15, 2022 at 16:21

answered Apr 12, 2011 at 8:13

van

77.6k13 gold badges174 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Greg Ennis Over a year ago

pylint does not like comparing None with ==

Neil Parley Over a year ago

If you want to get around pylint not liking comparing None with ==, you can use Table.column_name.is_(None). Also Table.column_name.isnot(None) replaces Table.column_name != None.

PartialOrder Over a year ago

I think in this particular instance, # noinspection PyComparisonWithNone,PyPep8 is just fine

Morteza Zakeri Over a year ago

is_(None) is a better way.

bhattraideb Over a year ago

As @MortezaZakeri mentioned I also recommend like: ...filter(or_(people.marriage_status!='married', people.marriage_status .is_(None))) in flask-sqlalchemy==2.4.1

jsnow · Accepted Answer · 2023-11-28 10:48:39Z

131

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

edited Nov 28, 2023 at 10:48

user8060120

answered Jun 21, 2017 at 14:45

jsnow

1,9082 gold badges14 silver badges13 bronze badges

Comments

Eds_k · Accepted Answer · 2021-10-21 08:44:07Z

35

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

answered Oct 21, 2021 at 8:44

Eds_k

1,21413 silver badges13 bronze badges

Comments

Yourstruly · Accepted Answer · 2022-08-05 08:19:14Z

15

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

edited Aug 5, 2022 at 8:19

answered Jul 5, 2022 at 8:30

Yourstruly

6995 silver badges12 bronze badges

1 Comment

Ilon Sjögren Over a year ago

I quite like this approach, as IDEs (at least IntelliJ) code inspection does not flag it as it does with the alternatives (depending on how you have things set up).

Jonathan Vanasco · Accepted Answer · 2012-08-18 00:33:46Z

1

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)

answered Aug 18, 2012 at 0:33

Jonathan Vanasco

15.8k11 gold badges53 silver badges75 bronze badges

2 Comments

Antti Haapala Over a year ago

And now there are .is_ and .isnot since 0.7.9, so this should be considered obsolete.

JGFMK Over a year ago

@AnttiHaapala - cheers needed to use .isnot(None) instead of is not None..

mitesh keswani · Accepted Answer · 2024-04-03 14:41:35Z

1

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

answered Apr 3, 2024 at 14:41

mitesh keswani

1934 bronze badges

Collectives™ on Stack Overflow

Select NULL Values in SQLAlchemy

6 Answers 6

5 Comments

Comments

Comments

1 Comment

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

5 Comments

Comments

Comments

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related