sqlalchemy expression not_

Question

I have the following code:

 s = select([user.c.Title]).where(user.c.Action != 'delete')
 conn = engine.connect()
 rows = conn.execute(s).fetchall()
 print rows
 conn.close()

The code is supposed to fetch a list of titles where the column 'Action' is not equal to 'delete'. Everytime I run the code, I get back an empty list. The output in the console with echo set to True is:

2014-08-05 16:27:31,492 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1
2014-08-05 16:27:31,492 INFO sqlalchemy.engine.base.Engine ()
2014-08-05 16:27:31,492 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1
2014-08-05 16:27:31,492 INFO sqlalchemy.engine.base.Engine ()
2014-08-05 16:27:31,493 INFO sqlalchemy.engine.base.Engine SELECT "Notes"."Title" 
FROM "Notes" 
WHERE "Notes"."Action" != ?
2014-08-05 16:27:31,493 INFO sqlalchemy.engine.base.Engine ('delete',)
[]

I checked the database and there are rows which exist which do not have 'delete' in the 'Action' column.

When I use:

s = select([user.c.Title]).where(not_(user.c.Action != 'delete'))

I still get the same output.

Thanks in advance!

can `"Action"` be `NULL`? is it ever? Also, to be clear, does `user` refer to the table `"Notes"`, which is a simple table (not a join or anything)? — Dan Getz, Aug 05 '14 at 13:55
You need to give more information about your tables, data, and class definitions to give a definite answer, but if I had to guess, your answer lies in SQL's treatment of NULL. See http://stackoverflow.com/q/5658457/ — Dan Getz, Aug 05 '14 at 14:16
**user** only refers to the table 'Notes', nothing else. The value in the Action column is usually NULL when its not 'delete' — Michael, Aug 05 '14 at 15:03

score 4 · Accepted Answer · answered Aug 05 '14 at 19:19

NULL in SQL behaves differently from None in Python, and SQLAlchemy in general doesn't change that. The result of the SQL expression NULL != 'delete' is NULL, not TRUE. In a WHERE clause, NULL is treated as false.

To turn NULLs in an expression into TRUE or FALSE, you can use an IS expression. In your example, you want to return TRUE if "Action" is different from 'delete', even if it's null:

("Action" != 'delete') IS NOT FALSE

If the expression inside the parentheses is FALSE, the entire expression is FALSE. If it is TRUE or NULL, the entire expression is TRUE.

To create this expression in SQLAlchemy, use isnot():

(user.c.Action != 'delete').isnot(False)

It's easier for me to understand if we rewrite this to remove a double negative:

(user.c.Action == 'delete').isnot(True)

which should generate the SQL:

("Action" = 'delete') IS NOT TRUE

Thank you so much..... Your solution fixed it.. .. I tried changing the default value of the action column to 'no action' but I still got the same problem... Im not sure if it's bug.... Anyway thanks again — Michael, Aug 06 '14 at 09:35
Just remember that setting a default value of a column changes the value *for future inserts that do not define a value for that column*. It does not change the value of existing rows, nor does it prevent `NULL` values in that column. Those things are accomplished in different ways. — Dan Getz, Aug 06 '14 at 10:30

sqlalchemy expression not_

1 Answers1