Python sqlite3 how do I check equality with potentially null python value

Question

It appears that in SQL null = null returns false. I can see this making sense in some circumstances where null means unknown/unspecified, but often null is used very intentionally to mean known and well defined absence, in those situations I would love for null = null to be true.

What is the proper way to deal with this when using sqlite3? As currently doing anything like:

c.execute('select * from foo where bar = ?', [bar])

will break if bar is None, whereas I very much want it to return every row where bar is null. This all works seamlessly in Haskell's persistent which is what I am using on the other end, (FooBar ==. Nothing), so I was hoping for something similarly clean on the Python end.

Cubic · Accepted Answer · 2018-02-22 15:18:40Z

2

In SQL NULL = NULL is indeed false (NULL actually IIRC, but that's also false). What you need in your example instead is the is operator 'select * from foo where bar is ?'

I'm guessing the behavior in persistent is to choose = or is depending on whether or not the value is of type Maybe a or not, Python doesn't have that kind of information (at least not in the sqlite module of the standard library).

The IS and IS NOT operators work like = and != except when one or both of the operands are NULL. In this case, if both operands are NULL, then the IS operator evaluates to 1 (true) and the IS NOT operator evaluates to 0 (false). If one operand is NULL and the other is not, then the IS operator evaluates to 0 (false) and the IS NOT operator is 1 (true). It is not possible for an IS or IS NOT expression to evaluate to NULL. Operators IS and IS NOT have the same precedence as =.

Source: https://www.sqlite.org/lang_expr.html

edited Feb 22, 2018 at 15:18

answered Feb 22, 2018 at 13:30

Cubic

15.8k6 gold badges50 silver badges97 bronze badges

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

4 Comments

semicolon Over a year ago

Yes I'm fairly sure persistent switches to is for Nothing and uses = for Just / non-Maybe types. I was kind of worried that was the answer, I guess I'll just do some weird hacks with .format to insert the right operator. Thanks.

Cubic Over a year ago

Not sure what your use case is, but you normally shouldn't need any switches or formatting, just use the "right" operator. Unless you're automatically generating the queries that is, but it didn't sound like you were doing that

semicolon Over a year ago

Oops, I didn't properly read the part in yellow as I had assumed it was the same as what I read earlier. So it seems like I can just always use is and get nice behavior where null is considered equal to itself. Is this a sqlite specific thing? As I may end up using a different database if performance becomes an issue, and I read elsewhere that in SQL is can only be followed by null or not null.

Cubic Over a year ago

@semicolon I don't have the SQL standard on hand, but it seems like Oracle DB and Microsoft SQL Server indeed only support the is [not] null syntax; So this indeed seems like a nonstandard SQL extension. Note that SQLite is different in a few important ways from other mainstream Databases and those have significant differences between them too, so full portability between databases isn't something you should necessarily expect unless you restrict yourself to a very small subset of SQL.

Collectives™ on Stack Overflow

Python sqlite3 how do I check equality with potentially null python value

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related