sqlite3, qmarks, and NULL values

[Mitchell L Model]

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.

 

[Peter Otten]

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

 

[Marco Mariani]

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.

With SQLAlchemy you could write:

table.select().where((table.c.col1==x) & (table.c.col2==y))

where x or y are None, the sql engine generates the appropriate "IS
NULL" clause.

I hope I'm missing something and that someone can tell me what it is.

Yes, you are missing SQLAlchemy

 

[Lawrence D'Oliveiro]

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.

In SQL, NULL is supposed to be a special case, by design. Perhaps you
shouldn't be using NULL in your data values here.