sqlite3, qmarks, and NULL values

[Jean-Michel Pichavant]

I fall into the same issue when using postgres. Your main concern is 
that NULL = NULL will return False. Could seems strange but it has much 
advantages sometimes, however this is not the purpose.
I found your solution quite acceptable. Just replace 'if y:' by 'if y is 
None:', add a comment to point that #NULL <> NULL to help any reader 
that is not familiar with db and you got a perfectly readable code.

Of course if you have more complex queries to build you'll have to think 
about an elegant way to manage NULL values. I personally iterate trough 
all my COLs and add a 'COL# = ?' if the value queried is not None. It 
works, it's readable, it's simple, it's python :o)

JM


Mitchell L Model wrote:
> Suppose I have a simple query in sqlite3 in a function:
>
>     def lookupxy(x, y):
>         conn.execute("SELECT * FROM table WHERE COL1 = ? AND COL2 = ?",
>                      (x, y))
>
> However, COL2 might be NULL. I can't figure out a value for y that would retrieve rows for which COL2 is NULL. It seems to me that I have to perform an awkward test to determine whether to execute a query with one question mark or two.
>
>     def lookupxy(x, y):
>         if y:
>             conn.execute("SELECT * FROM table WHERE COL1 = ? AND COL2 = ?",
>                          (x, y))
>         else:
>             conn.execute("SELECT * FROM table WHERE COL1 = ? AND COL2 IS NULL",
>                          (x,))
>
> The more question marks involved the more complicated this would get, especially if question marks in the middle of several would sometimes need to be NULL. I hope I'm missing something and that someone can tell me what it is.
>