Michael Bayer (@zzzeek) wrote:

There is absolutely no reason you need the MSSQLStrictCompiler with pyodbc, it was designed for mxodbc which uses a particular API of ODBC that allows binds in far fewer places.

The bug here is that in_() is not expecting "None" at all, which would normally be coerced to null().

Workaround is to, well first off don't bother with the "strict" compiler, no idea what purpose that is solving, then for now just pass null() instead of None.

from sqlalchemy.dialects.mssql.base import MSSQLStrictCompiler, dialect, MSSQLCompiler
from sqlalchemy.sql import column, select, null

expr = select([1](1)).where(column("x").in_([null()](null())))
comp = MSSQLStrictCompiler(dialect(), expr)
print comp

patch:

--- a/lib/sqlalchemy/sql/expression.py	Fri Jun 01 16:31:10 2012 -0400
+++ b/lib/sqlalchemy/sql/expression.py	Mon Jun 04 23:12:13 2012 -0400
@@ -1971,6 +1971,8 @@
                     raise exc.InvalidRequestError('in() function accept'
                             's either a list of non-selectable values, '
                             'or a selectable: %r' % o)
+            elif o is None:
+                o = null()
             else:
                 o = self._bind_param(op, o)
             args.append(o)

Changes by Michael Bayer (@zzzeek):

• set milestone to "0.7.8"

Sok Ann Yap (@sayap) wrote:

Well, I kind of abuse column_property, resulting in queries with 100+ bind parameters in the columns clause, which makes SQL Server Profiler output rather difficult to read. So using the strict compiler is more of a cosmetic fix for something I regret doing but too late to be changed.

Patch works. Thanks :)

Sok Ann Yap (@sayap) wrote:

On further thought, setting ansi_bind_rules to True looks like a more fitting workaround for me, as I do want to have IN clause and NOT IN clause to be parameterized.

Michael Bayer (@zzzeek) wrote:

cb19f22

Changes by Michael Bayer (@zzzeek):

• removed labels: mssql
• added labels: sql
• changed milestone from "0.7.10" to "0.8.0final"
• changed status to closed