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.
On Tue, Jul 4, 2017 at 10:25 AM, Paul Morel
<paul.mo...@tartansolutions.com> wrote:
> 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
>
> On Tuesday, July 4, 2017 at 9:00:22 AM UTC-5, Simon King 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+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.
