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 <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+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.
