implement pyodbc + mssql + py3k

[Former user]

Using SQLAlchemy tip, pyodbc 3.0.1 beta 4, and Python 3.2.2 on Windows.

The following example program throws an exception on the last line.

#!/usr/bin/python3

from sqlalchemy import create_engine, MetaData

engine = create_engine('mssql+pyodbc://user:passwd@host/db')

meta = MetaData
meta.bind = engine
conn = engine.connect()

Stack trace:

Traceback (most recent call last):
  File "C:\Python32\lib\site-packages\sqlalchemy-0.7.5dev-py3.2.egg\sqlalchemy\pool.py", line 675, in _do_get
    return self._pool.get(wait, self._timeout)
  File "C:\Python32\lib\site-packages\sqlalchemy-0.7.5dev-py3.2.egg\sqlalchemy\util\queue.py", line 137, in get
    raise Empty
sqlalchemy.util.queue.Empty

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "./description_encoding_crasher.py", line 9, in <module>
    conn = engine.connect()
  File "C:\Python32\lib\site-packages\sqlalchemy-0.7.5dev-py3.2.egg\sqlalchemy\engine\base.py", line 2324, in connect
    return self._connection_cls(self, **kwargs)
  File "C:\Python32\lib\site-packages\sqlalchemy-0.7.5dev-py3.2.egg\sqlalchemy\engine\base.py", line 872, in __init__
    self.__connection = connection or engine.raw_connection()
  File "C:\Python32\lib\site-packages\sqlalchemy-0.7.5dev-py3.2.egg\sqlalchemy\engine\base.py", line 2410, in raw_connection
    return self.pool.unique_connection()
  File "C:\Python32\lib\site-packages\sqlalchemy-0.7.5dev-py3.2.egg\sqlalchemy\pool.py", line 169, in unique_connection
    return _ConnectionFairy(self).checkout()
  File "C:\Python32\lib\site-packages\sqlalchemy-0.7.5dev-py3.2.egg\sqlalchemy\pool.py", line 371, in __init__
    rec = self._connection_record = pool._do_get()
  File "C:\Python32\lib\site-packages\sqlalchemy-0.7.5dev-py3.2.egg\sqlalchemy\pool.py", line 697, in _do_get
    con = self._create_connection()
  File "C:\Python32\lib\site-packages\sqlalchemy-0.7.5dev-py3.2.egg\sqlalchemy\pool.py", line 174, in _create_connection
    return _ConnectionRecord(self)
  File "C:\Python32\lib\site-packages\sqlalchemy-0.7.5dev-py3.2.egg\sqlalchemy\pool.py", line 259, in __init__
    pool.dispatch.first_connect.exec_once(self.connection, self)
  File "C:\Python32\lib\site-packages\sqlalchemy-0.7.5dev-py3.2.egg\sqlalchemy\event.py", line 262, in exec_once
    self(*args, **kw)
  File "C:\Python32\lib\site-packages\sqlalchemy-0.7.5dev-py3.2.egg\sqlalchemy\event.py", line 271, in __call__
    fn(*args, **kw)
  File "C:\Python32\lib\site-packages\sqlalchemy-0.7.5dev-py3.2.egg\sqlalchemy\engine\strategies.py", line 167, in first_connect
    dialect.initialize(c)
  File "C:\Python32\lib\site-packages\sqlalchemy-0.7.5dev-py3.2.egg\sqlalchemy\connectors\pyodbc.py", line 127, in initialize
    super(PyODBCConnector, self).initialize(connection)
  File "C:\Python32\lib\site-packages\sqlalchemy-0.7.5dev-py3.2.egg\sqlalchemy\dialects\mssql\base.py", line 1130, in initialize
     super(MSDialect, self).initialize(connection)
  File "C:\Python32\lib\site-packages\sqlalchemy-0.7.5dev-py3.2.egg\sqlalchemy\engine\default.py", line 176, in initialize
    self._get_default_schema_name(connection)
  File "C:\Python32\lib\site-packages\sqlalchemy-0.7.5dev-py3.2.egg\sqlalchemy\dialects\mssql\base.py", line 1146, in _get_default_schema_name
    user_name = connection.scalar("SELECT user_name() as user_name;")
  File "C:\Python32\lib\site-packages\sqlalchemy-0.7.5dev-py3.2.egg\sqlalchemy\engine\base.py", line 1339, in scalar
    return self.execute(object, *multiparams, **params).scalar()
  File "C:\Python32\lib\site-packages\sqlalchemy-0.7.5dev-py3.2.egg\sqlalchemy\engine\base.py", line 1405, in execute
    params)
  File "C:\Python32\lib\site-packages\sqlalchemy-0.7.5dev-py3.2.egg\sqlalchemy\engine\base.py", line 1582, in _execute_text
    statement, parameters
  File "C:\Python32\lib\site-packages\sqlalchemy-0.7.5dev-py3.2.egg\sqlalchemy\engine\base.py", line 1665, in _execute_context
    result = context.get_result_proxy()
  File "C:\Python32\lib\site-packages\sqlalchemy-0.7.5dev-py3.2.egg\sqlalchemy\dialects\mssql\base.py", line 737, in get_result_proxy
    return base.ResultProxy(self)
  File "C:\Python32\lib\site-packages\sqlalchemy-0.7.5dev-py3.2.egg\sqlalchemy\engine\base.py", line 2723, in __init__
    self._init_metadata()
  File "C:\Python32\lib\site-packages\sqlalchemy-0.7.5dev-py3.2.egg\sqlalchemy\engine\base.py", line 2730, in _init_metadata
    self._metadata = ResultMetaData(self, metadata)
  File "C:\Python32\lib\site-packages\sqlalchemy-0.7.5dev-py3.2.egg\sqlalchemy\engine\base.py", line 2586, in __init__
    colname = dialect._description_decoder(colname)
AttributeError: 'MSDialect_pyodbc' object has no attribute '_description_decoder'

Two similar things:

1. colname is a string just before the crash. Knowing this, if I add the parameter "description_encoding=None" to create_engine, there is no exception. However, IMHO, I wouldn't expect that I should have to do this.

2. Whilst finding the workaround for the above, I saw the following code at the bottom of lib/sqlalchemy/dialects/mssql/pyodbc.py (and similar code elsewhere):

   def __init__(self, description_encoding='latin-1', **params):
       super(MSDialect_pyodbc, self).__init__(**params)
       self.description_encoding = description_encoding
       [...](...)
   

May be a silly question, but is it possible that description_encoding should instead be set before the call to super (DefaultDialect)'s init? I ask because super's init does something with self.description_encoding, namely conditionally set self._description_decoder.

[Mike Bayer]

I'd note that we have no official support for pyodbc on Python 3 as of yet (meaning, we've never tried it. Pyodbc is actually undergoing a lot of development right now as well). Things like description_encoding and such generally need to be bypassed with py3K in effect as DBAPIs typically send everything back as plain Python strings which are unicode, so that would be part of the change here.

That said, I'll agree I'm a little puzzled by the description_encoding thing I'm seeing - we've observed in the past that pyodbc hardcodes the encoding of cursor.description to "latin-1" regardless of any other encoding settings. Perhaps Pyodbc fixed this at some point, the code here is in fact totally in error, but things "work out" because using the same encoding "works" now so it never became apparent that it should be changed. So I'll agree that perhaps just taking out that block entirely would alleviate this issue.

[Former user]

Well, if it were supported, it would be no fun! Somebody has to be mad enough to try being an early adaptor... maybe.

I have done some simple pyodbc tests with Unicode (in particular, non-ASCII, non-Latin-1, non-Windows-1252) characters. Unfortunately, despite pyodbc returning what claim to be (py3k) strings, it or Python throws exceptions if either the column names contain Unicode characters, or if a field in the row contains Unicode characters. So clearly pyodbc will need fixed first. I /intend/ to raise bugs for that.

Until pyodbc is fixed, "latin-1" I think is still the correct SQLAlchemy behaviour for Python 2. It's pyodbc not encoding the colinfo strings as UTF-8 which is both relevant here, and is what leads to these exceptions when pulling back Unicode data on Python 3.

FWIW, there's a similar SQLAlchemy decode issue to the description_encoding one, in process_bind_param() of dialects/mssql/information_schema.py. Removing that is sufficient to let me successfully init an autoloaded Table(), and do a .select().execute.fetchall() on it---at least with ASCII only queries and data. Success (naively) seems so close. But it's never so simple.

[Mike Bayer]

Unless Pyodbc can ever get a working unicode setup on OSX, I really can't fix this issue. See http://code.google.com/p/pyodbc/issues/detail?id=247&thanks=247&ts=1331704925 for the latest basic round trip that's failing.

[Mike Bayer]

give it a try for 0.8, probably use a linux vm

[Mike Bayer]

I've spent half the weekend coming up with more test cases and info for issue 247. Things work OK on linux, but I'd really like to get pyodbc 100% supporting unicode everywhere before I jump into reorganizing the dialect here. It's a huge PITA having to have it switch behaviors all the time.

[Mike Bayer]

threre's a lot of problems here, but 0790efcf87a268fd8bc810b711fe2b9760bcf1e6 addresses the issue of the NVARCHAR/NTEXT comparison issue.

[Mike Bayer]

test change

[Mike Bayer]

peopel are using pyodbc + py3k successfully now in 0.9, I'm sure there's still issues but we can open new tickets as they are reported.