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