Python 3, MSSQL 2008R2 exception in dialect when using sqltap

[Marek Baczyński]

Traceback (most recent call last):
  File "C:\Python34\lib\runpy.py", line 170, in _run_module_as_main
    "__main__", mod_spec)
  File "C:\Python34\lib\runpy.py", line 85, in _run_code
    exec(code, run_globals)
  File "C:\Python34\lib\cProfile.py", line 160, in <module>
    main()
  File "C:\Python34\lib\cProfile.py", line 153, in main
    runctx(code, globs, None, options.outfile, options.sort)
  File "C:\Python34\lib\cProfile.py", line 20, in runctx
    filename, sort)
  File "C:\Python34\lib\profile.py", line 64, in runctx
    prof.runctx(statement, globals, locals)
  File "C:\Python34\lib\cProfile.py", line 100, in runctx
    exec(cmd, globals, locals)
  File "tests/benchmark.py", line 207, in <module>
    sqltap.report(statistics, "report.html")
  File "...\venv\lib\site-packages\sqltap\sqltap.py", line 277, in report
    group = query_groups[str(qstats.text)]
  File "...\venv\lib\site-packages\sqlalchemy\sql\elements.py", line 502, in __str__
    return str(self.compile())
  File "<string>", line 1, in <lambda>
  File "...\venv\lib\site-packages\sqlalchemy\sql\elements.py", line 492, in compile
    return self._compiler(dialect, bind=bind, **kw)
  File "...\venv\lib\site-packages\sqlalchemy\sql\elements.py", line 498, in _compiler
    return dialect.statement_compiler(dialect, self, **kw)
  File "...\venv\lib\site-packages\sqlalchemy\sql\compiler.py", line 395, in __init__
    Compiled.__init__(self, dialect, statement, **kwargs)
  File "...\venv\lib\site-packages\sqlalchemy\sql\compiler.py", line 199, in __init__
    self.string = self.process(self.statement, **compile_kwargs)
  File "...\venv\lib\site-packages\sqlalchemy\sql\compiler.py", line 222, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "...\venv\lib\site-packages\sqlalchemy\sql\visitors.py", line 80, in _compiler_dispatch
    return meth(self, **kw)
  File "...\venv\lib\site-packages\sqlalchemy\sql\compiler.py", line 1544, in visit_select
    t = select._whereclause._compiler_dispatch(self, **kwargs)
  File "...\venv\lib\site-packages\sqlalchemy\sql\visitors.py", line 80, in _compiler_dispatch
    return meth(self, **kw)
  File "...\venv\lib\site-packages\sqlalchemy\sql\compiler.py", line 662, in visit_clauselist
    for c in clauselist.clauses)
  File "...\venv\lib\site-packages\sqlalchemy\sql\compiler.py", line 659, in <genexpr>
    s for s in
  File "...\venv\lib\site-packages\sqlalchemy\sql\compiler.py", line 662, in <genexpr>
    for c in clauselist.clauses)
  File "...\venv\lib\site-packages\sqlalchemy\sql\visitors.py", line 80, in _compiler_dispatch
    return meth(self, **kw)
  File "...\venv\lib\site-packages\sqlalchemy\sql\compiler.py", line 847, in visit_binary
    return self._generate_generic_binary(binary, opstring, **kw)
  File "...\venv\lib\site-packages\sqlalchemy\sql\compiler.py", line 864, in _generate_generic_binary
    binary.right._compiler_dispatch(self, **kw)
  File "...\venv\lib\site-packages\sqlalchemy\sql\visitors.py", line 80, in _compiler_dispatch
    return meth(self, **kw)
  File "...\venv\lib\site-packages\sqlalchemy\sql\compiler.py", line 981, in visit_bindparam
    skip_bind_expression=True)
  File "...\venv\lib\site-packages\sqlalchemy\sql\compiler.py", line 222, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "...\venv\lib\site-packages\sqlalchemy\ext\compiler.py", line 413, in <lambda>
    lambda *arg, **kw: existing(*arg, **kw))
  File "...\venv\lib\site-packages\sqlalchemy\ext\compiler.py", line 451, in __call__
    return fn(element, compiler, **kw)
  File "...\venv\lib\site-packages\sqlalchemy\dialects\mssql\information_schema.py", line 41, in _compile
    if compiler.dialect.server_version_info < base.MS_2005_VERSION:
TypeError: unorderable types: NoneType() < tuple()

I worked around it by hacking in venv\lib\site-packages\sqlalchemy\dialects\mssql\information_schema.py:

@compiles(_cast_on_2005)
def _compile(element, compiler, **kw):
    from . import base
    try:
        if compiler.dialect.server_version_info < base.MS_2005_VERSION:
            return compiler.process(element.bindvalue, **kw)
        else:
            return compiler.process(cast(element.bindvalue, Unicode), **kw)
    except TypeError:
        return compiler.process(cast(element.bindvalue, Unicode), **kw)

(added try/except)

I don't really know if it's a sqlalchemy bug or a sqltap bug; reporting it here for now.

[Mike Bayer]

what's sqltap?

[Marek Baczyński]

A quite useful piece of profiling software - https://github.com/inconshreveable/sqltap. This is how I use it:

# tests/benchmark.py

if __name__ == '__main__':
    import pytest
    import sqltap
    profiler = sqltap.start()
    try:
        pytest.main(__file__)
    finally:
        statistics = profiler.collect()
        sqltap.report(statistics, "report.html")

[Mike Bayer]

OK well it seems to be blowing away the ability for the dialect to get at server_version_info, that's the origin of the bug here. I can't see in the stack trace what dialect this is.

[Mike Bayer]

it seems to be calling str() on some expression elements that aren't public, e.g. elements within the information schema package, which assume they are compiled against an MSSQL dialect.

[Marek Baczyński]

Added some prints() in the except clause, they look like this:

##### dialect <sqlalchemy.engine.default.DefaultDialect object at 0x0345C510>
##### element <sqlalchemy.dialects.mssql.information_schema._cast_on_2005 object at 0x051235B0> BindParameter('%(94673296 TABLE_NAME)s', 'table_name', type_=CoerceUnicode())

[Mike Bayer]

sqltap should fix this: https://github.com/inconshreveable/sqltap/commit/9c83fe806fb304d96cde6d712751e2b911f6bd24#commitcomment-7627053