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

I'm quite surprised to be stuck at this deceptively simple task, or is there anything fundamentally wrong with my table design?

Thanks in advance.

Jerry

van · Accepted Answer · 2011-04-12 08:13:04Z

You simply have a typo in your query. Instead of

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

you should write:

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

mu is too short · Answer 2 · 2011-04-09 04:49:38Z

I don't know much Python or SQLAlchemy but could you use COALESCE to coerce the NULLs into something easier to deal with? Say coalesce(people.marriage_status, '') where you currently have people.marriage_status?

Using COALESCE would be a bit of a hack but not a terribly ugly one.

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

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)

asked	2 years ago
viewed	4252 times
active	1 year ago

(PostgreSQL) Select NULL Values In SQLAlchemy

3 Answers

Your Answer

Not the answer you're looking for? Browse other questions tagged python sql database postgresql sqlalchemy or ask your own question.

(PostgreSQL) Select NULL Values In SQLAlchemy

3 Answers

Your Answer

Sign up or login

Post as a guest

Not the answer you're looking for? Browse other questions tagged python sql database postgresql sqlalchemy or ask your own question.

Related