bindparam() does not support None

[Former user]

I am creating a select query s with say bindparam('x') in the where clause. Later, call: s.execute(x=None). It seems that SA does not replace the None with NULL once it generates the SQL.

[Mike Bayer]

Python's "None" singleton already compiles to the NULL value at the DBAPI level. From the DBAPI spec:

SQL NULL values are represented by the Python None singleton on input and output.

However, while "None" can be used as the value in an INSERT or UPDATE statement just fine, you usually cant use NULL as an arbitrary bind parameter value in a select statement where you would otherwise be comparing to values. This is because when comparing to NULL in SQL, you have to use the "IS" operator...saying "where x=NULL" produces somewhat unpredictable results.

normally, when you say table.select(table.c.foo==None), SA doesnt use a bind parameter; it compiles the phrase "IS NULL" into the SQL statement where it would normally say "=:bindparam". theres no way for SA to change the operator from "=" to "IS" based on a bind parameter value...it has to be determined at statement construction time, not execution time.

additionally, various DBAPIs have varied support for using the IS operator with a bind param. Mysql can do it, it appears that SQLite cannot.

as an example, if you do something like this:

users.select(users.c.name.op('is')(bindparam('foo'))).execute(foo=None).fetchall()

youll see that the comparison to NULL works fine in mysql. throws an error in sqlite.