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. On Tuesday, July 4, 2017 at 9:27:09 AM UTC-5, Mike Bayer wrote: > > 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. > > > > On Tue, Jul 4, 2017 at 9:59 AM, Simon King <si...@simonking.org.uk > <javascript:>> wrote: > > 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....@tartansolutions.com <javascript:>> wrote: > >> 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 > >> > >> > >> On Monday, July 3, 2017 at 8:33:54 PM UTC-5, Mike Bayer wrote: > >>> > >>> 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. > >>> > >>> On Jul 3, 2017 7:26 PM, "Paul Morel" <paul....@tartansolutions.com> > wrote: > >>> > >>> 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 > >>> > >>> -- > >>> 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+...@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. > >>> > >>> > >> -- > >> 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+...@googlegroups.com <javascript:>. > >> To post to this group, send email to sqlal...@googlegroups.com > <javascript:>. > >> Visit this group at https://groups.google.com/group/sqlalchemy. > >> For more options, visit https://groups.google.com/d/optout. > > > > -- > > 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+...@googlegroups.com <javascript:>. > > To post to this group, send email to sqlal...@googlegroups.com > <javascript:>. > > Visit this group at https://groups.google.com/group/sqlalchemy. > > For more options, visit https://groups.google.com/d/optout. > -- 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+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
