pyodbc database connection does not appear to fully close

[odon...@yahoo.com]

I am using pyodbc to write SQL statements for SELECT, UPDATE, and
INSERT functions for an MS Access database. Everything works, but it
appears that the connection to the database does not close properly.
Basically this is what happens. I open a DB connection, query the
tables to make sure specific tables exist, close the database, and
then I use a different module (an ESRI arcpy GIS mapping module). When
I use this module, python crashes and I get the following error:
Message File Name Line Position
Traceback
__call__ C:\Program Files\PyScripter\Lib\rpyc-python2x.zip\rpyc
\core\netref.py 123
syncreq C:\Program Files\PyScripter\Lib\rpyc-python2x.zip\rpyc\core
\netref.py 45
sync_request C:\Program Files\PyScripter\Lib\rpyc-python2x.zip\rpyc
\core\protocol.py 343
serve C:\Program Files\PyScripter\Lib\rpyc-python2x.zip\rpyc\core
\protocol.py 305
_recv C:\Program Files\PyScripter\Lib\rpyc-python2x.zip\rpyc\core
\protocol.py 265
recv C:\Program Files\PyScripter\Lib\rpyc-python2x.zip\rpyc\core
\channel.py 36
read C:\Program Files\PyScripter\Lib\rpyc-python2x.zip\rpyc\core
\stream.py 105
exceptions.EOFError: [Errno 10054] An existing connection was forcibly
closed by the remote host

It seems that this has occurred in the past with pyodbc and the fix
was to disable pooling, but this did not work.I am using python 2.6
and pyodbc-3.0.2.win32-py2.6. I tried several older versions of pyodbc
but I get the same error message.

Any thoughts or has anyone else had this problem? It seems that
sqlalchemy no longer supports MS Access DB connections and pyodbc is
the best supported module currently available.

Also, I am actually able to make changes to the database and I use
this package alone it seems to work fine (e.g., I can loop through the
below code 100 times and I do not get an error). But I can also run my
other code successfully as long as I do not use pyodbc. There seems to
be some kind of conflict. I tried to use performance monitor to look
at the odbc connections, but I am not familiar with tracking this info
for Access and not sure how to determine if there is a thread or
socket connection to the database after I close.

Example of pyodbc code:
import pyodbc
pyodbc.pooling = False
conn = pyodbc.connect("Driver={Microsoft Access Driver (*.mdb,
*.accdb)};DBQ=" + OutputDB, autocommit = True)
cur = conn.cursor()
for iTbl in Tbl_List:
if cur.tables(table=iTbl).fetchone():
pass
else:
print "Table in DB for Archive Project does not match
table scheme in python script--need to investigate.", iTbl
sys.exit(1)
conn.commit()
conn.close()
del conn

[Michael Kleehammer]

Not trying to pass the buck, but the issue will be in the Access driver if anything.  This is something that is constantly tested with pyodbc and I use in 24x7 system critical servers, so I'd notice there also.

I'll look into it, but Access isn't a very good database.  If at all possible, consider using one of the free versions of SQL Server.

[ryank...@gmail.com]

I know this is long past the original discussion, but I too ran into the same issue reported by the original poster.  All I had to do was *open* a connection to the MS Access DB, and then on the next line run an ESRI arcpy command and I would get the "EOFError: [Errno 10054] An existing connection was forcibly closed by the remote host."  If I simply changed the connection to an MSSQLSERVER connection, the code would run without failure.

My novice assessment would agree with Michael, that the issue is the MS Access driver.  I am using "pyodbc-3.0.6.win32-py2.6.exe" and ArcGIS 10.0 SP5 against an MS Access *.accdb file.

[ryank...@gmail.com]

Could this perhaps be a conflict of using the 32-bit Python, the 32-bit pyodbc and (possibly) a 64-bit Windows 7 MS Access ODBC driver?  Just another thought that I had today...

[Michael Kleehammer]

The example code above looks fine, but do note that keeping a Cursor around may keep the connection around.  If you call Connection.close(), I would expect it to actually close everything, but perhaps the driver keeps track of the number of HSTMTs.  I don't think this is your issue, but something to think about.

cur = None

conn = None