sqlite3, qmarks, and NULL values

[Peter Otten]

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.

You could create a custom function

def equals(a, b):
    return a == b

conn.create_function("equals", 2, equals)

cursor.execute("select * from table where equals(col1, ?) and ...", (x,...))

Or you do some gymnastics in Python:

class Expr(object):
    def __init__(self, dict):
        self.dict = dict
    def __getitem__(self, key):
        value = self.dict[key]
        if value is None:
            return "(%s is null)" % key
        return "(%s = :%s)" % (key, key)

def lookup(col1, col2):
    lookup = locals()
    sql = "SELECT * FROM table WHERE %(col1)s AND %(col2)s" % Expr(lookup)
    return conn.execute(sql, lookup)

I think these are both more readable than

"... where case when :col1 is null then (col1 is null) else (col1 = :col1) end ..."

Peter