Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

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

share|improve this question

3 Answers 3

up vote 19 down vote accepted

(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, instead of:

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

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.

share|improve this answer
1  
pylint does not like comparing None with == –  Greg Ennis Apr 30 '14 at 16:40
3  
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. –  Neil Parley Nov 24 '14 at 9:02
1  
This isn't a typo, is None is the right way to compare to None in Python. However, sqlalchemy relies on Python's "magic methods" to override operator implementations like == for query construction. There is no magic method for is, which is why the OP's query doesn't work. –  augurar Apr 21 at 18:32
    
@augurar: thank you, this is exactly right, i will update the wording –  van Apr 21 at 19:39

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.

share|improve this answer

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)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.