jbhalerao

Problem using PYODBC in ArcGIS script

Discussion created by jbhalerao on Jun 24, 2010
Latest reply on Jul 1, 2015 by cstidham
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 = 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

Outcomes