Exception when connecting to mssql
Issue #3889
closed
In series 1.1 this code raises an exception, did not happen in 1.0 series:
import sqlalchemy as db
engine = db.create_engine('mssql+pyodbc://user:pass@server\mssqlserver:1433/dbname?driver=SQL+Server', echo=True)
engine.connect()
Traceback:
2017-01-13 15:41:26,990 INFO sqlalchemy.engine.base.Engine SELECT SERVERPROPERTY('ProductVersion')
2017-01-13 15:41:26,992 INFO sqlalchemy.engine.base.Engine ()
2017-01-13 15:41:26,994 INFO sqlalchemy.engine.base.Engine SELECT schema_name()
2017-01-13 15:41:26,997 INFO sqlalchemy.engine.base.Engine ()
---------------------------------------------------------------------------
NoSuchColumnError Traceback (most recent call last)
<ipython-input-1-3bc88aa9c09a> in <module>()
----> 1 import external_db as edb
2 import numpy as np
3 from struct import unpack
4
5 # def frombin(inbin, mlcsize=100):
C:\notebooks\chartalertpython\external_db.py in <module>()
210 engine = db.create_engine('mssql+pyodbc://username:password@HC-PFPMOSAIQ\mssqlserver:1433/MOSAIQ?driver=SQL+Server',
211 echo=True)
--> 212 engine.connect()
213 Session = sessionmaker(bind=engine)
214 s = Session()
C:\Users\me\AppData\Local\Continuum\Miniconda\lib\site-packages\sqlalchemy\engine\base.pyc in connect(self, **kwargs)
2080 """
2081
-> 2082 return self._connection_cls(self, **kwargs)
2083
2084 def contextual_connect(self, close_with_result=False, **kwargs):
C:\Users\me\AppData\Local\Continuum\Miniconda\lib\site-packages\sqlalchemy\engine\base.pyc in __init__(self, engine, connection, close_with_result, _branch_from, _execution_options, _dispatch, _has_events)
88 else:
89 self.__connection = connection \
---> 90 if connection is not None else engine.raw_connection()
91 self.__transaction = None
92 self.__savepoint_seq = 0
C:\Users\me\AppData\Local\Continuum\Miniconda\lib\site-packages\sqlalchemy\engine\base.pyc in raw_connection(self, _connection)
2166 """
2167 return self._wrap_pool_connect(
-> 2168 self.pool.unique_connection, _connection)
2169
2170
C:\Users\me\AppData\Local\Continuum\Miniconda\lib\site-packages\sqlalchemy\engine\base.pyc in _wrap_pool_connect(self, fn, connection)
2136 dialect = self.dialect
2137 try:
-> 2138 return fn()
2139 except dialect.dbapi.Error as e:
2140 if connection is None:
C:\Users\me\AppData\Local\Continuum\Miniconda\lib\site-packages\sqlalchemy\pool.pyc in unique_connection(self)
326
327 """
--> 328 return _ConnectionFairy._checkout(self)
329
330 def _create_connection(self):
C:\Users\me\AppData\Local\Continuum\Miniconda\lib\site-packages\sqlalchemy\pool.pyc in _checkout(cls, pool, threadconns, fairy)
764 def _checkout(cls, pool, threadconns=None, fairy=None):
765 if not fairy:
--> 766 fairy = _ConnectionRecord.checkout(pool)
767
768 fairy._pool = pool
C:\Users\me\AppData\Local\Continuum\Miniconda\lib\site-packages\sqlalchemy\pool.pyc in checkout(cls, pool)
514 @classmethod
515 def checkout(cls, pool):
--> 516 rec = pool._do_get()
517 try:
518 dbapi_connection = rec.get_connection()
C:\Users\me\AppData\Local\Continuum\Miniconda\lib\site-packages\sqlalchemy\pool.pyc in _do_get(self)
1136 except:
1137 with util.safe_reraise():
-> 1138 self._dec_overflow()
1139 else:
1140 return self._do_get()
C:\Users\me\AppData\Local\Continuum\Miniconda\lib\site-packages\sqlalchemy\util\langhelpers.pyc in __exit__(self, type_, value, traceback)
58 exc_type, exc_value, exc_tb = self._exc_info
59 self._exc_info = None # remove potential circular references
---> 60 compat.reraise(exc_type, exc_value, exc_tb)
61 else:
62 if not compat.py3k and self._exc_info and self._exc_info[1]:
C:\Users\me\AppData\Local\Continuum\Miniconda\lib\site-packages\sqlalchemy\pool.pyc in _do_get(self)
1133 if self._inc_overflow():
1134 try:
-> 1135 return self._create_connection()
1136 except:
1137 with util.safe_reraise():
C:\Users\me\AppData\Local\Continuum\Miniconda\lib\site-packages\sqlalchemy\pool.pyc in _create_connection(self)
331 """Called by subclasses to create a new ConnectionRecord."""
332
--> 333 return _ConnectionRecord(self)
334
335 def _invalidate(self, connection, exception=None):
C:\Users\me\AppData\Local\Continuum\Miniconda\lib\site-packages\sqlalchemy\pool.pyc in __init__(self, pool, connect)
459 self.__pool = pool
460 if connect:
--> 461 self.__connect(first_connect_check=True)
462 self.finalize_callback = deque()
463
C:\Users\me\AppData\Local\Continuum\Miniconda\lib\site-packages\sqlalchemy\pool.pyc in __connect(self, first_connect_check)
659 pool.dispatch.first_connect.\
660 for_modify(pool.dispatch).\
--> 661 exec_once(self.connection, self)
662 if pool.dispatch.connect:
663 pool.dispatch.connect(self.connection, self)
C:\Users\me\AppData\Local\Continuum\Miniconda\lib\site-packages\sqlalchemy\event\attr.pyc in exec_once(self, *args, **kw)
244 if not self._exec_once:
245 try:
--> 246 self(*args, **kw)
247 finally:
248 self._exec_once = True
C:\Users\me\AppData\Local\Continuum\Miniconda\lib\site-packages\sqlalchemy\event\attr.pyc in __call__(self, *args, **kw)
254 fn(*args, **kw)
255 for fn in self.listeners:
--> 256 fn(*args, **kw)
257
258 def __len__(self):
C:\Users\me\AppData\Local\Continuum\Miniconda\lib\site-packages\sqlalchemy\util\langhelpers.pyc in go(*arg, **kw)
1319 if once:
1320 once_fn = once.pop()
-> 1321 return once_fn(*arg, **kw)
1322
1323 return go
C:\Users\me\AppData\Local\Continuum\Miniconda\lib\site-packages\sqlalchemy\engine\strategies.pyc in first_connect(dbapi_connection, connection_record)
179 _has_events=False)
180 c._execution_options = util.immutabledict()
--> 181 dialect.initialize(c)
182 event.listen(pool, 'first_connect', first_connect, once=True)
183
C:\Users\me\AppData\Local\Continuum\Miniconda\lib\site-packages\sqlalchemy\connectors\pyodbc.pyc in initialize(self, connection)
163
164 # run other initialization which asks for user name, etc.
--> 165 super(PyODBCConnector, self).initialize(connection)
166
167 def _dbapi_version(self):
C:\Users\me\AppData\Local\Continuum\Miniconda\lib\site-packages\sqlalchemy\dialects\mssql\base.pyc in initialize(self, connection)
1702
1703 def initialize(self, connection):
-> 1704 super(MSDialect, self).initialize(connection)
1705 self._setup_version_attributes()
1706
C:\Users\me\AppData\Local\Continuum\Miniconda\lib\site-packages\sqlalchemy\engine\default.pyc in initialize(self, connection)
248 try:
249 self.default_schema_name = \
--> 250 self._get_default_schema_name(connection)
251 except NotImplementedError:
252 self.default_schema_name = None
C:\Users\me\AppData\Local\Continuum\Miniconda\lib\site-packages\sqlalchemy\dialects\mssql\base.pyc in _get_default_schema_name(self, connection)
1733 else:
1734 query = sql.text("SELECT schema_name()")
-> 1735 default_schema_name = connection.scalar(query)
1736 if default_schema_name is not None:
1737 return util.text_type(default_schema_name)
C:\Users\me\AppData\Local\Continuum\Miniconda\lib\site-packages\sqlalchemy\engine\base.pyc in scalar(self, object, *multiparams, **params)
875 """
876
--> 877 return self.execute(object, *multiparams, **params).scalar()
878
879 def execute(self, object, *multiparams, **params):
C:\Users\me\AppData\Local\Continuum\Miniconda\lib\site-packages\sqlalchemy\engine\result.pyc in scalar(self)
1222 row = self.first()
1223 if row is not None:
-> 1224 return row[0]
1225 else:
1226 return None
C:\Users\me\AppData\Local\Continuum\Miniconda\lib\site-packages\sqlalchemy\engine\result.pyc in _key_fallback(self, key, raiseerr)
561 raise exc.NoSuchColumnError(
562 "Could not locate column in row for column '%s'" %
--> 563 expression._string_or_unprintable(key))
564 else:
565 return None
NoSuchColumnError: "Could not locate column in row for column '0'"
Comments (2)
-
repo owner -
repo owner - changed status to closed
insufficient information
- Log in to comment
OK -
If you look in
#3820, you can see that we had a different problem in 1.1 with SQL Server / pyodbc, but it was fixed. So there is something specific on your setup that is different.Also to note, while the problem in
#3820involved a query that had changed, in this case the code your stack trace refers towards has not changed.For your setup, we would need the following information:
on your database, the output of
SELECT SERVERPROPERTY('ProductVersion')
if
#1fails, then the output ofselect @@version
The output of
select schema_name()
What is the known version / edition of this SQL Server ? Is it a special cloud edition or something similar?
What is the version of pyodbc in use?
I notice you are running miniconda. Was the upgrade to SQLAlchemy 1.1 part of a larger upgrade of many packages? Have there been changes to the ODBC driver, the configuration of the ODBC driver, changes to the pyodbc version?
are you able to try the pymssql driver?
thanks.