Exception when connecting to mssql

[adenbley]

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'"

[Mike Bayer]

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 #3820 involved 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:

1. on your database, the output of SELECT SERVERPROPERTY('ProductVersion')

2. if #1 fails, then the output of select @@version

3. The output of select schema_name()

4. What is the known version / edition of this SQL Server ? Is it a special cloud edition or something similar?

5. What is the version of pyodbc in use?

6. 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?

7. are you able to try the pymssql driver?

thanks.

[Mike Bayer]

insufficient information