SQLAlchemy won't connect correctly but pyodbc and pymssql will

[Paul Morel]

Hi,

I have been trying to diagnose this issue in a Windows Python 2.7 (Anaconda installed) environment running SQLAlchemy=1.1.11, pyodbc=4.0.17, and pymssql=2.1.3.

Both pyodbc and pymssql connections will successfully connect and query a table correctly.  However, when I attempt the same connection and query through SQLAlchemy either using an ORM or direct SQL, it fails with the following error:

sqlalchemy.exc.NoSuchColumnError: "Could not locate column in row for column '0'

The connection string I'm using is the following:

    'mssql+pyodbc://:@CMPDSQL01:1433/CMP?driver=SQL+Server+Native+Client+10.0'

    

The connection and simple query through pyodbc uses the following:

    

    print "---- Testing pyodbc Directly"
    cnxn = pyodbc.connect(
        r'Trusted_Connection=yes;'
        r'DRIVER={SQL Server Native Client 10.0};'
        r'SERVER=CMPDSQL01;'
        r'DATABASE=CMP;'
        )
   
    print cnxn
    print "---- Complete ----"
   
    print "---- Running Direct SQL Query on pyodbc Direct Connection"
    cursor = cnxn.cursor()
    cursor.execute('SELECT * FROM EPO_MODELS')
    for r in cursor:
        print r
    print "---- Complete ----"

The connection and simple query through pymssql uses the following:

    print "---- Testing pymssql Directly"
    cnxn = pymssql.connect(server='CMPDSQL01', port='1433', database='CMP')
    print cnxn
    print "---- Complete ----"
   
    print "---- Running Direct SQL Query on pymssql Direct Connection"
    cursor = cnxn.cursor()
    cursor.execute('SELECT * FROM EPO_MODELS')
    for r in cursor:
        print r
    print "---- Complete ----"

What is even more perplexing is that the SQLAlchemy connection used to work but now no longer works.  Unfortunately I don't know what broke it due to a clean start install.

I don't think the EPO_MODELS object model comes into play with this error because even a direct SQL query fails in the same way.  However, for completeness the EPO_MODELS object model is very simple and looks like the following:

    class EPO_MODELS(Base):
        __tablename__ = 'EPO_MODELS'
   
        ID = Column(Integer, primary_key=True, autoincrement=False)
        MODELTYPE = Column(Integer, autoincrement=False)
        MODELNAME = Column(NVARCHAR(255))
        MEMO = Column(NVARCHAR(2000))
        NEXTUNIQUEID = Column(Integer, autoincrement=False)
        MODELSYNC = Column(Integer, autoincrement=False)
        MODELSTATUS = Column(Integer, autoincrement=False)
        AUDITUSERID = Column(Integer, autoincrement=False)
        DATEALTERED = Column(DateTime)
        CREATIONDATE = Column(DateTime)

The direct SQLAlchemy query looks like the following after getting the session using the connection string above:

    print "---- Running Direct SQL Query Through SQLAlchemy Connection"
    result = con.execute('SELECT * FROM EPO_MODELS')
    for r in result:
        print r
    print "---- Complete ----"

Very much appreciate any insight into what is going on here.  I can't seem to find the disconnect. Thanks in advance.

Stack Overflow Post is here:  https://stackoverflow.com/q/44893049/227542

-Paul

[Mike Bayer]

Can you send complete stack trace please?  That's the main thing that will show which query this is occurring on (there are several upon connect).  Also full detail on SQL server version, odbc driver, client operating system.  

--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
 
http://www.sqlalchemy.org/
 
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscribe@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Paul Morel]

Mike,

Sorry for the lack of information.  Please find the rest of what you wanted below.

Full Stack Trace:

---- Running Direct SQL Query

Traceback (most recent call last):
  File "test.py", line 45, in <module> result = con.execute('SELECT * FROM EPO_MODELS')
  File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\orm\session.py",line 1139, in execute bind, close_with_result=True).execute(clause, params or {})
  File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\orm\session.py",line 1003, in _connection_for_bind engine, execution_options)
  File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\orm\session.py",line 403, in _connection_for_bind conn = bind.contextual_connect()
  File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\engine\base.py",line 2112, in contextual_connect self._wrap_pool_connect(self.pool.connect, None),
  File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\engine\base.py",line 2147, in _wrap_pool_connect return fn()
  File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py", line 387, in connect return _ConnectionFairy._checkout(self)
  File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py", line 766, in _checkout fairy = _ConnectionRecord.checkout(pool)
  File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py", line 516, in checkout rec = pool._do_get()
  File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py", line 1138, in _do_get self._dec_overflow()
  File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\util\langhelpers.py", line 66, in __exit__ compat.reraise(exc_type, exc_value, exc_tb)
  File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py", line 1135, in _do_get return self._create_connection()
  File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py", line 333, in _create_connection return _ConnectionRecord(self)
  File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py", line 461, in __init__ self.__connect(first_connect_check=True)
  File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\pool.py", line 661, in __connect exec_once(self.connection, self)
  File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\event\attr.py",line 246, in exec_once self(*args, **kw)
  File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\event\attr.py",line 256, in __call__ fn(*args, **kw)
  File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\util\langhelpers.py", line 1331, in go return once_fn(*arg, **kw)
  File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\engine\strategies.py", line 181, in first_connect dialect.initialize(c)
  File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\connectors\pyodb c.py", line 165, in initialize super(PyODBCConnector, self).initialize(connection)
  File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\dialects\mssql\base.py", line 1742, in initialize super(MSDialect, self).initialize(connection)
  File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\engine\default.py", line 250, in initialize self._get_default_schema_name(connection)
  File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\dialects\mssql\base.py", line 1773, in _get_default_schema_name default_schema_name = connection.scalar(query)
  File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\engine\base.py",line 877, in scalar return self.execute(object, *multiparams, **params).scalar()
  File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\engine\result.py", line 1223, in scalar return row[0]
  File "c:\Program Files\Anaconda2\lib\site-packages\sqlalchemy\engine\result.py", line 563, in _key_fallback expression._string_or_unprintable(key)) 

  sqlalchemy.exc.NoSuchColumnError: "Could not locate column in row for column '0'"

ODBC Driver Versions:

SQL Server (SQLSRV32.DLL) = 6.00.6002.18005
SQL Server Native Client 10.0 (SQLNCLI10.DLL) = 2007.100.2531.00

SQL Server Version

SQL Server 2008
Microsoft SQL Server Management Studio = 10.0.6000.29
Microsoft Analysis Services Client Tools = 10.0.6000.29
Microsoft Data Access Components (MDAC) = 6.0.6002.18005
Microsoft MSXML = 3.0 6.0 
Microsoft Internet Explorer = 9.0.8112.16421
Microsoft .NET Framework = 2.0.50727.4253
Operating System = 6.0.6002

Server OS

Windows Server 2008 Enterprise
Service Pack 2 Installed

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

[Simon King]

The key part of the stack trace is:

File "c:\Program
Files\Anaconda2\lib\site-packages\sqlalchemy\dialects\mssql\base.py",
line 1773, in _get_default_schema_name default_schema_name =
connection.scalar(query)

...which is in this function:

https://bitbucket.org/zzzeek/sqlalchemy/src/8d740d6bd6b8bcc061713443120c67e611cdcb34/lib/sqlalchemy/dialects/mssql/base.py?at=rel_1_1_11&fileviewer=file-view-default#base.py-1768

It's failing to fetch the single value that ought to come back from
the query "SELECT schema_name()".

I don't know anything about MSSQL or ODBC, but you could try poking
around with pdb in the scalar() function:

https://bitbucket.org/zzzeek/sqlalchemy/src/8d740d6bd6b8bcc061713443120c67e611cdcb34/lib/sqlalchemy/engine/result.py?at=rel_1_1_11&fileviewer=file-view-default#result.py-1212

Simon


On Tue, Jul 4, 2017 at 2:39 PM, Paul Morel

[Paul Morel]

I don't know the downstream implications of doing this but would it make sense to wrap lines 1773-1777 in a try/except with the except block returning self.schema_name.

Like this:

def _get_default_schema_name(self, connection):
    if self.server_version_info < MS_2005_VERSION:
        return self.schema_name
    else:
        query = sql.text("SELECT schema_name()")

        try:
          default_schema_name = connection.scalar(query)
          if default_schema_name is not None:
              return util.text_type(default_schema_name)
          else:
              return self.schema_name
        except:
              return self.schema_name

me

[Mike Bayer]

can you run this query please?

SELECT schema_name()

the issue is, that query is returning a result, there is a row, but it
no columns, which is nonsensical. Did you try running with the
mssql+pymssql:// driver? Looks like a pyodbc bug so far but need
more info.

[Mike Bayer]

It's never a good idea to just blanket ignore all exceptions. There
are a few cases where we except for dbapi.Error in order to catch
unsupported calls such as looking for sys.dm_exec_sessions.

However here, SQL Server is not complaining at all. A result is
returned. The result has a row. Then the row has no columns. That
should never happen. Looks like a pyodbc driver bug so far.

[Paul Morel]

I ran that select statement under both the pyodbc and pymssql direct connections and it appears to have returned a legitimate row of data.  This is the excerpt from the test output:

---- Testing pymssql Directly
<pymssql.Connection object at 0x0000000003F64D48>
---- Complete ----
---- Testing Get Schema Name with pymssql
(u'dbo',)
---- Complete ----
---- Testing pyodbc Directly
<pyodbc.Connection object at 0x0000000003F049D0>
---- Complete ----
---- Testing Get Schema Name with pyodbc
(u'dbo', )
---- Complete ----
---- Testing SQLAlchemy Connection using pymssql
<sqlalchemy.orm.session.Session object at 0x000000000518BEF0>
---- Complete ----

I switched over the SQLAlchemy connection string to use pymssql.  It now looks like this:

'mssql+pymssql://:@CMPDSQL01:1433/CMP'

The same error is produced.  I attempted to run the SELECT schema_name() query through the SQLAlchemy connection to see what was coming back but it failed in the same way because it is attempting its internal schema_name() query first.

This is indeed quite strange since both the pyodbc and pymssql direct connections did return a legit row when asking for the schema name.

[Mike Bayer]

in your direct examples can you show me the output of
"cursor.description" as well once you execute the query?

[Paul Morel]

---- Testing pymssql Directly
<pymssql.Connection object at 0x0000000003F36D48>

---- Complete ----
---- Testing Get Schema Name with pymssql
(u'dbo',)

Cursor Description is:
((u'', 1, None, None, None, None, None),)

---- Complete ----
---- Testing pyodbc Directly

<pyodbc.Connection object at 0x0000000003ECE8F0>

---- Complete ----
---- Testing Get Schema Name with pyodbc
(u'dbo', )

Cursor Description is:
((u'', <type 'unicode'>, None, 128, 128, 0, True),)
---- Complete ----

[Paul Morel]

Sorry, I gave you the cursor.description of the schema_name() query.  This is the output for the cursor.description for the SELECT * FROM EPO_MODELS query:

pymssql Cursor Description is:

((u'ID', 3, None, None, None, None, None), (u'MODELTYPE', 3, None, None, None, N
one, None), (u'MODELNAME', 1, None, None, None, None, None), (u'MEMO', 1, None,
None, None, None, None), (u'NEXTUNIQUEID', 3, None, None, None, None, None), (u'
MODELSYNC', 3, None, None, None, None, None), (u'MODELSTATUS', 3, None, None, No
ne, None, None), (u'AUDITUSERID', 3, None, None, None, None, None), (u'DATEALTER
ED', 4, None, None, None, None, None), (u'CREATIONDATE', 4, None, None, None, No
ne, None))

pyodbc Cursor Description is:

((u'ID', 3, None, None, None, None, None), (u'MODELTYPE', 3, None, None, None, N
one, None), (u'MODELNAME', 1, None, None, None, None, None), (u'MEMO', 1, None,
None, None, None, None), (u'NEXTUNIQUEID', 3, None, None, None, None, None), (u'
MODELSYNC', 3, None, None, None, None, None), (u'MODELSTATUS', 3, None, None, No
ne, None, None), (u'AUDITUSERID', 3, None, None, None, None, None), (u'DATEALTER
ED', 4, None, None, None, None, None), (u'CREATIONDATE', 4, None, None, None, No
ne, None))

[Mike Bayer]

it's the select schema_name() query that's failing and that
cursor.description is fine.

did you build the SQLAlchemy C extensions on windows? or does your
anaconda build somehow deliver sqlalchemy/cresultproxy.dll to your
installation ? my last theory here is that you have C extensions
set up and somehow they built such that PyLong_CheckExact(0) is
returning false. if you have any .dll's (or whatever Windows uses
for native Python extensions these days), try blowing those away for
SQLAlchemy.


On Tue, Jul 4, 2017 at 2:24 PM, Paul Morel

[Paul Morel]

BAM!  That was it.  There must be some type of incompatibility between the c extensions and the Windows 2008 Server configuration.  I pip uninstalled/installed and found the c extensions where rebuilt.  After removing cresultproxy.pyd from the sqlalchemy site-packages directory the queries started working again.

Many thanks...

[Mike Bayer]

Ok how did the pyd file get there in the first place, was that pre-built as part of anaconda or did it compile on your local machine?   This is kind of a serious issue that a broken shared library got created 

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscribe@googlegroups.com.

[Jonathan Vanasco]

Is there a chance you updated the Python binary as part of your process?

Weird stuff like this often happens if you forget to trash the site-packages or virtualenv after a python version upgrade (even on a micro version upgrade)

[Paul Morel]

It appears that the Anaconda installer placed the .pyd files there.  I discovered that pip uninstall doesn't remove those files like I had assumed.  Therefore, when I pip uninstalled/installed the original .pyd files were still there.

It looks like deleting the .pyd files and pip installing again does not recreate the pyd files.  So it appears the Anaconda distribution of SQLAlchemy included may be the culprit here.

[Mike Bayer]

On Jul 5, 2017 12:59 AM, "Paul Morel" <paul....@tartansolutions.com> wrote:

It appears that the Anaconda installer placed the .pyd files there.  I discovered that pip uninstall doesn't remove those files like I had assumed.  Therefore, when I pip uninstalled/installed the original .pyd files were still there.

Ok, would this be simple issue of old version of .pyd files not matching a pip updated version of the python code ?   That is, you manually updated SQLAlchemy from 1.0.x to 1.1.x.   the c extension from 1.0 would not be compatible as there were some changes.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscribe@googlegroups.com.

[Paul Morel]

It is possible that in the end that was the case, however, the reason I even attempted the pip uninstall/install originally was because of the error I was getting.  I found some SO posts that recommended rolling back to an earlier version of SQLAlchemy.  Rolling back had no impact.  I then rolled forward to the latest, also with no impact.  Presumably those roll-back/roll-forward attempts didn't work because they were still using the 1.1.9 version C extensions that ship with Anaconda.

https://docs.continuum.io/anaconda/packages/pkg-docs

I am trying to get my hands on another similar environment but it appears I don't have installation rights on that machine at the moment.  I will see if I can reproduce this problem if I can get install privileges.

[Mike Bayer]

OK thanks for looking into this and I'm glad we found the issue,
mostly thanks to your prompt and accurate replies so thanks!

On Wed, Jul 5, 2017 at 9:46 AM, Paul Morel