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
--
http://mail.python.org/mailman/listinfo/python-list
