Problem using PYODBC in ArcGIS script

[JessicaBhalerao]

I am having problems using a python script running as a tool in ArcToolbox (running from ArcMap 9.3).  The script uses the PYODBC connector to use SQL to do summarization of data on an input geodataset.  When I run it through IDLE, it works perfectly.  When I use it as a tool, loading the inputs through the ArcToolbox, the script runs through several of my loops (for various zones, then classes), but then simply stops. 

I have many gp.AddMessage messages added to track when the script crashes.  The SQL that operates just before the crash works fine if I use it outside the script, so it isn't a problem with the SQL statement.  I have changed it to explicitly open and close the connection in each 'zone' loop, just in case it was a memory leak problem, but that has not changed the outcome at all.

Unfortunately, when it crashes, it leaves the pyodbc connection open, which means I have to logout to close it down. 

Is there some programming convention in python or pyodbc that I am missing that might solve this? 
Thanks for your help.
Jessie

Script (pyodbc connection stuff starts on line 203):

# ---------------------------------------------------------------------------

import sys, string, os, arcgisscripting
import pyodbc

gp = arcgisscripting.create()
gp.CheckOutExtension("spatial")
gp.AddToolbox("C:/Program Files (x86)/ArcGIS/ArcToolbox/Toolboxes/Spatial Analyst Tools.tbx")
gp.AddToolbox("C:/Program Files (x86)/ArcGIS/ArcToolbox/Toolboxes/Data Management Tools.tbx")

# What features you want to tabulate
FeaturesToTabulate = 'X:\\TableofFeatures.mdb\\FeatureTable'         
# Must be a valid classification table for this raster (all fields in the SQL statements need to be valid)
Classification_Table = 'X:\\ClassificationTables\\SimpleClassTbl.dbf'
Results_Geodatabase = 'U:\\CRUNCH\\TestTabProgram.mdb'
Tabulated_Area_Table = "TblTestSimple"

FieldtoTabulate = 'COUNT'       #This the field that will be summarized
TabulationMultiplier = 100      # This is a float(!) multiplier in case of a raster COUNT - * Cell size for example

TabByZoneField = "True"
Zone_field = 'WTRSHD_ID'
WholeZoneName = 'WholeTable'


# GUI entries  (shortened for forum posting)
FeatToTab  = FeaturesToTabulate
ClassTbl  = Classification_Table
TabGeoDB  = Results_Geodatabase
TabTbl  = Tabulated_Area_Table
FldtoTab  = FieldtoTabulate
TabMult  = float(TabulationMultiplier)
TabByZone  = TabByZoneField
ZoneFld  = Zone_field
AltZone  = WholeZoneName

# Make new tabulated table
geodbname = TabGeoDB.split("\\")[len(TabGeoDB.split("\\"))-1]
geodbpath = TabGeoDB[:len(TabGeoDB)-len(geodbname)]
if not(os.path.exists(TabGeoDB)):
    gp.CreatePersonalGDB_management(geodbpath, geodbname)   
try:
    gp.CreateTable(TabGeoDB,TabTbl)
except:
    gp.AddMessage("Table already exists, please rename the output table or delete the existing table.")
    print "Output table already exists."
gp.GetMessages()
TabGeoDBTbl = TabGeoDB + "/" + TabTbl

# Get ClassName and SQL Expression lists
rows = gp.SearchCursor(ClassTbl,"","","","")
row = rows.next()

ClassNmList = [row.GetValue("ClassNm")]
SQLExprList = [row.GetValue("SQLExpr")]
row = rows.next()

while row:
    theclass = row.GetValue("ClassNm")
    SQLexp = row.GetValue("SQLExpr")
    ClassNmList.append(theclass)
    SQLExprList.append(SQLexp)
    row = rows.next()
del row, rows

# Copy the attribute table or stand-alone table that is being tabulated into the Tabulation Geodatabase.
fullname = gp.ParseTableName(os.path.basename(FeatToTab))
nameList = fullname.split(",")
FeatToTabTbl = nameList[2].split(".")[0].strip()
#print FeatToTabTbl

if FldtoTab.lower() == "count":
    FldtoTab = "COUNT_"

gp.OverwriteOutput = 1
#May Need to make a table view for raster feature sets
lt = ["rasterdataset","rasterband"]
try:
    dsc = gp.describe(FeatToTab)
    if string.lower(dsc.DatasetType) in lt:
        TblView = gp.MakeTableView_management(FeatToTab,FeatToTabTbl) 
        gp.CopyRows_management(TblView, TabGeoDB + '\\' + FeatToTabTbl)
    else:
        TblView = TabGeoDB + '\\' + FeatToTabTbl
        gp.CopyRows_management(FeatToTab, TabGeoDB + '\\' + FeatToTabTbl)
    del dsc
except:
    gp.GetMessages()
gp.OverwriteOutput = 0
gp.GetMessages()
gp.AddMessage("")

try:
    if TabByZone == "True" or TabByZone == "true":
        # Get Zone List
        zrows = gp.SearchCursor(TblView,"","","",str(ZoneFld) + " A")
        zrow = zrows.next()
        zonelast = zrow.GetValue(ZoneFld)
        ZoneList = [zonelast]
        zrow = zrows.next()
        maxlen = len(zonelast)
        while zrow:
            zone = zrow.GetValue(ZoneFld)
            if zone == zonelast:
                zrow = zrows.next()
            else:
                ZoneList.append(zone)
                zonelast = zone
                zrow = zrows.next()
                if len(zone)>maxlen:
                    maxlen = len(zone)
        del zrow, zrows
       
        fields = gp.ListFields(TblView)
        field = fields.next()
        while field:
            if field.Name == ZoneFld:
                zonefldtype = field.Type
                break
            field = fields.next()

        # Begin the tabulation file with a zone ID and remove the auto-generated "Field1"
        try:
            if zonefldtype == "String":
                gp.AddField_management(TabGeoDBTbl,"ZoneID","Text",maxlen+4)
            else:
                gp.AddField_management(TabGeoDBTbl,"ZoneID",zonefldtype)
            gp.DeleteField_management(TabGeoDBTbl,"Field1")
            gp.AddField_management(TabGeoDBTbl,"TOTTABAREA","Long")
        except:
            gp.GetMessages()

        # Add field for Class Name in the Classification Table
        for theclass in ClassNmList:
            try:
                gp.AddField_management(TabGeoDBTbl,theclass,"Long")
            except: gp.GetMessages()  #Try-Except for debug, if already added

       
        # Open the ODBC Connection to the Personal Geodatabase
        try:
            DBfile = TabGeoDB  #'/data/MSAccess/Music_Library.mdb'
            conn = pyodbc.connect('DRIVER={Microsoft Access Driver (*.mdb)};DBQ='+DBfile)
            cursor = conn.cursor()
            gp.AddMessage("Connection opened")
        except:
            print gp.GetMessages()
            gp.AddMessage("Problem Occurred with the ODBC connection.")
            del gp
            exit
    
        i = 0
        classedtotarea = 0
        # Begin the tabulation table by inserting the ZoneID's
        SQL = "SELECT * FROM " + TabTbl
        tblcnt = cursor.execute(SQL).rowcount
        if tblcnt >0:
            print "Tab Table already populated with zones."
        else:
            SQL = "INSERT INTO " + TabTbl + " ( ZoneID ) " \
                  "SELECT " + FeatToTabTbl + "." + ZoneFld + " " \
                  "FROM " + FeatToTabTbl + " " \
                  "GROUP BY " + FeatToTabTbl + "." + ZoneFld + " " \
                  "ORDER BY " + FeatToTabTbl + "." + ZoneFld + ";"
            cursor.execute(SQL)
            conn.commit()
        gp.AddMessage("Select Zones into Tab Table Complete.\n")
       
        # Process the features to tabulate, looping through each Class/SQL Expression
        for zone in ZoneList:
            print "Working on zone:  " + str(zone)
            gp.AddMessage("Working on zone:  " + str(zone))
            if zonefldtype == "String":
                zone = "'" + zone + "'"
    
            #Get the total area for the zone  
            whereclause = ZoneFld + ' = ' + zone
            totSQL = "SELECT Sum(" + FldtoTab + ") as TOTCOUNT " + \
                  "FROM " + FeatToTabTbl + " " + \
                  "WHERE " + whereclause + ";"
            print totSQL
            gp.AddMessage(str(totSQL))
            tottabarea = cursor.execute(totSQL).fetchone()[0]
            if tottabarea is None:
                tottabarea = 0
            else:
                tottabarea = tottabarea * TabMult

            insertSQL = "UPDATE " + TabTbl + " " + \
                        "SET TOTTABAREA =" + str(tottabarea) + " " + \
                        "WHERE ZoneID = " + zone + ";"
            cursor.execute(insertSQL)
            conn.commit()

            i = 0
            for theclass in ClassNmList:
                SQL = SQLExprList
                i = i+1
                #Create the summary by this class           
                whereclause = "(" + SQL.replace('"','') + ") AND " + ZoneFld + ' = ' + zone
                totSQL = "SELECT Sum(" + FldtoTab + ") as TOTCOUNT " + \
                      "FROM " + FeatToTabTbl + " " + \
                      "WHERE " + whereclause + ";"
                gp.AddMessage(str(totSQL))
                totarea = cursor.execute(totSQL).fetchone()[0]
                gp.AddMessage("Class area is = " + str(totarea))
                if totarea is None:
                    totarea = 0
                else:
                    totarea = totarea * TabMult
                   
                #Add the insert SQL to put the results into the Tabulation Table
                insertSQL = "UPDATE " + TabTbl + " " + \
                            "SET " + theclass + "=" + str(totarea) + " " + \
                            "WHERE ZoneID = " + zone + ";"
                cursor.execute(insertSQL)
                conn.commit()
                gp.AddMessage("Class complete\n")

        gp.AddMessage("Completed tabulation table.")

except:
    gp.GetMessages()
  
try:
    cursor.close()
    conn.close()
    del conn, cursor
except: pass
del gp

[JoseZambrana]

I have the same problem, any news about a possible bug for this pyodbc behavior??

Also I tried to use odbc module, it works fine, but don't have .rowcount() function and the documentation for odbc is minimun vs pyodbc.

help!!!!!!!

Regards.

[JakubOrálek]

I have similar problems with pyodbc module in ArcGIS Desktop and Server 10.1 SP1, but tried module pypyodbc (http://code.google.com/p/pypyodbc) and it seems ok even in GP service.

Kuba

[DorothyMortenson]

Thanks for the tip on the pypyodbc. Who would have guessed the pyodbc wouldn't work even for a simple select statement.

[DorothyMortenson]

I have used the pypyodbc for one project and it seems to be ok.

Next project - script works fine as a python script. Works fine as a custom ArcTool pointing to the script. Turn it into a geoprocessing service and it fails at the cursor.execute line.

[NicholasBraaksma]

Hi, we had a similar issue when running a python toolbox tool in ArcMap that used pyodbc to run a stored procedure on Sql Server then present the results into ArcMap. What was happening was ArcMap was holding open the sql processes that the stored procs ran on which created schema locks and stopped the tool running subsequent times in the same ArcMap session (the tool would just hang because of the schema lock).
The fix for us was to type RETURN 0 at the end of our stored proc which I guess tells ArcMap that the stored proc has completed and the process can be closed.
If you are not running stored procs then the pyodbc documentation suggests closing your connection and deleting the variable.
for example

>> cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=testdb;UID=me;PWD=pass')

>> # Run your sql query

>> cnxn.close()

>> del cnxn

[AndrewHughes]

I had many of the symptoms as described above on 10.3 - in particular every 2nd GPService exec would crash.

I resolved this issue by switching from pyodbc to pypyodbc (i.e. a pure python implementation pypyodbc - A Pure Python ODBC Library based on ctypes - Google Project Hosting ). Note that there are differences between these implementations. Good luck.

[ChadStidham1]

I ran into this issue as well with pyodbc when running a tool script within a "CURRENT" map document.  The tool script would work fine the first time but any additional attempt to run would cause ArcMap to hang and crash.  I made sure to close the cursor and connection but it didn't make a difference.

In my case I only had to change two minimal lines of code to go with pypyodbc instead.  All I had to change was the module import to pypyodbc and then the connect method from pyodbc.connect() to pypyodbc.connect().  The cursor, execute, and fetchall methods are the same.

[ScottDavis]

I was experiencing almost the exact same situation as Detaille Octave‌. A GP task that ran successfully the first time and then failed on subsequent runs. Switching to `pypyodbc` fixed the issue for me. THANKS!