Trying to connect to SQL server using SQLAlchemy with pyodbc(freeTDS) as the driver; The connections succeeds if I use pyodbc directly:
>>> import pyodbc >>> conn = pyodbc.connect('DSN=serverdsn;UID=user;PWD=password') >>> crsr = conn.cursor() >>> rows = crsr.execute("select @@VERSION").fetchall() >>> print(rows) [('Microsoft Azure SQL Data Warehouse - 10.0.9248.28 Sep 12 2017 01:08:55 Copyright (c) Microsoft Corporation', )] >>> crsr.close() >>> conn.close() But when I use SQLAlchemy, it fails with a mysterious error:
>>> from sqlalchemy import create_engine >>> e = create_engine("mssql+pyodbc://user:password@serverdsn") >>> with e.connect() as con: ... rs = con.execute('select * from users') ... for row in rs: ... print(row) ... Here is the full stack trace:
Traceback (most recent call last): File "/Users/purecarscomputer/anaconda/envs/tensorflow/lib/python3.5/site-packages/sqlalchemy/pool.py", line 1122, in _do_get return self._pool.get(wait, self._timeout) File "/Users/purecarscomputer/anaconda/envs/tensorflow/lib/python3.5/site-packages/sqlalchemy/util/queue.py", line 145, in get raise Empty sqlalchemy.util.queue.Empty
During handling of the above exception, another exception occurred:
Traceback (most recent call last): File "", line 1, in File "/Users/purecarscomputer/anaconda/envs/tensorflow/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 2091, in connect return self._connection_cls(self, **kwargs) File "/Users/purecarscomputer/anaconda/envs/tensorflow/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 90, in init if connection is not None else engine.raw_connection() File "/Users/purecarscomputer/anaconda/envs/tensorflow/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 2177, in raw_connection self.pool.unique_connection, _connection) File "/Users/purecarscomputer/anaconda/envs/tensorflow/lib/python3.5/site-packages/sqlalchemy/engine/base.py", line 2147, in _wrap_pool_connect return fn() File "/Users/purecarscomputer/anaconda/envs/tensorflow/lib/python3.5/site-packages/sqlalchemy/pool.py", line 328, in unique_connection return _ConnectionFairy._checkout(self) File "/Users/purecarscomputer/anaconda/envs/tensorflow/lib/python3.5/site-packages/sqlalchemy/pool.py", line 766, in _checkout fairy = _ConnectionRecord.checkout(pool) File "/Users/purecarscomputer/anaconda/envs/tensorflow/lib/python3.5/site-packages/sqlalchemy/pool.py", line 516, in checkout rec = pool._do_get() File "/Users/purecarscomputer/anaconda/envs/tensorflow/lib/python3.5/site-packages/sqlalchemy/pool.py", line 1138, in _do_get self._dec_overflow() File "/Users/purecarscomputer/anaconda/envs/tensorflow/lib/python3.5/site-packages/sqlalchemy/util/langhelpers.py", line 66, in exit compat.reraise(exc_type, exc_value, exc_tb) File "/Users/purecarscomputer/anaconda/envs/tensorflow/lib/python3.5/site-packages/sqlalchemy/util/compat.py", line 187, in reraise raise value File "/Users/purecarscomputer/anaconda/envs/tensorflow/lib/python3.5/site-packages/sqlalchemy/pool.py", line 1135, in _do_get return self._create_connection() File "/Users/purecarscomputer/anaconda/envs/tensorflow/lib/python3.5/site-packages/sqlalchemy/pool.py", line 333, in _create_connection return _ConnectionRecord(self) File "/Users/purecarscomputer/anaconda/envs/tensorflow/lib/python3.5/site-packages/sqlalchemy/pool.py", line 461, in init self.connect(first_connect_check=True) File "/Users/purecarscomputer/anaconda/envs/tensorflow/lib/python3.5/site-packages/sqlalchemy/pool.py", line 661, in __connect exec_once(self.connection, self) File "/Users/purecarscomputer/anaconda/envs/tensorflow/lib/python3.5/site-packages/sqlalchemy/event/attr.py", line 246, in exec_once self(*args, **kw) File "/Users/purecarscomputer/anaconda/envs/tensorflow/lib/python3.5/site-packages/sqlalchemy/event/attr.py", line 256, in __call fn(*args, **kw) File "/Users/purecarscomputer/anaconda/envs/tensorflow/lib/python3.5/site-packages/sqlalchemy/util/langhelpers.py", line 1331, in go return once_fn(*arg, **kw) File "/Users/purecarscomputer/anaconda/envs/tensorflow/lib/python3.5/site-packages/sqlalchemy/engine/strategies.py", line 181, in first_connect dialect.initialize(c) File "/Users/purecarscomputer/anaconda/envs/tensorflow/lib/python3.5/site-packages/sqlalchemy/connectors/pyodbc.py", line 165, in initialize super(PyODBCConnector, self).initialize(connection) File "/Users/purecarscomputer/anaconda/envs/tensorflow/lib/python3.5/site-packages/sqlalchemy/dialects/mssql/base.py", line 1742, in initialize super(MSDialect, self).initialize(connection) File "/Users/purecarscomputer/anaconda/envs/tensorflow/lib/python3.5/site-packages/sqlalchemy/engine/default.py", line 256, in initialize self.get_isolation_level(connection.connection) File "/Users/purecarscomputer/anaconda/envs/tensorflow/lib/python3.5/site-packages/sqlalchemy/dialects/mssql/base.py", line 1735, in get_isolation_level "tried views: %s; final error was: %s" % (views, err)) UnboundLocalError: local variable 'err' referenced before assignment
I've tried install and uninstall sqlalchemy and searched around on google, but not find a solution. Does anyone have similar problems and have a clue about what is happening?
OS information:
ProductName: Mac OS X ProductVersion: 10.12.6 BuildVersion: 16G29 
