On May 20, 10:54 am, MRAB <goo...@mrabarnett.plus.com> wrote:
> 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.
>
> [snip]
> Have you tried None, ie lookupxy(x, None)?
Have *you* tried that? Here's another armchair philosopher's take on
the expected outcome:
EITHER:
the wrapper blindly causes this to appear like "... COL2 = NULL",
which won't/shouldn't work. "expression = NULL" is neither true nor
false, it's unknown. The WHERE clause will/should return no rows at
all.
Example of unknowableness of nullity using relops:
sqlite> select case when 1 = null then 'A' else 'B' end;
B
sqlite> select case when 1 <> null then 'A' else 'B' end;
B
Note that "1 = null" is not true AND "1 <> null" is not true either.
OR:
The wrapper cunningly but unadvertisedly is causing that to appear
like "... COL2 IS NULL"
Cheers,
John
--
http://mail.python.org/mailman/listinfo/python-list
