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
> <javascript:>> 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 <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.
