sqlite3, qmarks, and NULL values

[Jean-Michel Pichavant]

Erratum:

please read 'if y is *not* None:'. (think that if y = 0, you won't 
execute the proper code block with 'if y:').

JM

Jean-Michel Pichavant wrote:
> 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.
>>   
>