in your direct examples can you show me the output of "cursor.description" as well once you execute the query?
On Tue, Jul 4, 2017 at 11:17 AM, Paul Morel <paul.mo...@tartansolutions.com> wrote: > 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> 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> 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. >> >> 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. >> > 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+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. -- 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.
