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

[KimOllivier]

Regrettably there are some differences in running scripts in IDLE, Pythonwin, ArcCatalog and ArcMap that cause some things to just not run. I just open up my scripts in Pythonwin and run them from there, since that is where they were debugged.

It may be worth changing to 9.3 format if you have later versions. It may be related to the gp processor not releasing locks, especially the AddField tool.

There used to be a gp.RefreshCatalog(database) command that might help, this has mysteriously disappeared in documentation in later version because its 'not needed', but you might try that before you open the ODBC cursor.

There are bugs in Python modules too, so upgrading to the latest version of ArcGIS forces an upgrade of Python as well.

[JessicaBhalerao]

Dear kimo - Thanks for your reply.  I did try adding the RefreshCatalog command, which is good advice that I will use in other scripts.  Unfortunately that didn't solve the problem, so I guess we can just commiserate on that!  The script still runs perfectly when operated through Python IDLE (v2.4), but simply stops doing any more SQL calculations after having done maybe 15-20 of the operations, and says it's done. 

If I ever have it work after new ArcGIS or pyodbc updates, I'll try and remember to post an update here.
J

[neilwebster]

Hi there,

I thought I would reply to this thread as I came across it when searching on a problem resolution.

As mentioned before there are conflicts between pyodbc and the 9.3 python geoprocessor.

The problem I encounter was when I added fields using pyodbc there were not recognised by the setvalue function in the geoprocessor.  I was adding the fields using pyodbc as the AddField command was causing Pythoin to crash.

As a test of this I used the below code with a field added using the pyodbc module and one added using the gp function:

Field = "x"+str(Con)
conn = pyodbc.connect('DRIVER={Microsoft Access Driver (*.mdb)};DBQ='+Database, autocommit="True")
cursor = conn.cursor()
AddField = "ALTER TABLE " + os.path.basename(Results) + " ADD COLUMN " + Field +" NUMBER;"
cursor.execute(AddField)
conn.commit()
conn.close()
del conn

Field = "xx"+str(Con)
gp.AddField_management(Results, Field, "Double", "4","", "", "", "", "", "")

sFields = gp.ListFields(Results)
for field in sFields:
print field.Name, field.Type

The only fields listed by the list fields function were those from the second adding of the fields (the geoprocessor command), the ones beginning "xx".

I hope this is useful for any one who has similar problems and if any solutions exist I would love to hear about them.

[billchuang]

Hi,I encounter the same problem as yours,the difference is that I use the ArcGIS 10
Do you solve the problem?Can you enjoy your experience?

[DetailleOctave]

Hi!
Same problem for me!

• Runs perfectly in my IDE (Eclipse, configured to use the version of Python that come with ArccGIS);


• Works the first time the geoprocessing is invoked;


• Crash the second (and next) times it is invoked

Here is a minimal code snippet that allow to reproduce the problem:

import arcpy
import pyodbc

inputString = arcpy.GetParameterAsText(0)
arcpy.SetParameterAsText(1, 'echo:'+inputString)
try:
    arcpy.AddMessage("START")
    conn=pyodbc.connect('DRIVER={SQL Server};SERVER=DGENV-LPD.aris-lux.lan\LPD;DATABASE=lpd_proto;UID=lpd_proto;PWD=lpd_proto')
    cursor = conn.cursor()
    cursor.execute("INSERT INTO IMPORT_BATCH (batchDate,lifeProjectNumber,archiveName,stage) VALUES ('2012-03-26 17:16:37.4900000','Minimal test','Grrrrr...','TEMP')")
    r=cursor.execute("SELECT @@IDENTITY")
    f=cursor.fetchone()
    cursor.close()
    conn.commit()
    arcpy.AddMessage("DONE")

except Exception,e:
    arcpy.AddMessage("e="+str(e))

finally:
    conn.close() 

It is wrapped in a toolbox which has one input/output String parameter.

Here are the output of the script (when invoked from Firefox, as REST geoprocessing service)

First execution :

Results:

    out: echo:test

Messages:

    esriJobMessageTypeInformative: Executing (InputScriptLabel): InputScript test
    esriJobMessageTypeInformative: Start Time: Tue Mar 27 11:27:04 2012
    esriJobMessageTypeInformative: Running script InputScript...
    esriJobMessageTypeInformative: START
    esriJobMessageTypeInformative: DONE
    esriJobMessageTypeInformative: Completed script InputScript...
    esriJobMessageTypeInformative: Succeeded at Tue Mar 27 11:27:04 2012 (Elapsed Time: 0.00 seconds)

Next executions :

Results:

    out: echo:test

Messages:

    esriJobMessageTypeInformative: Executing (InputScriptLabel): InputScript test
    esriJobMessageTypeInformative: Start Time: Tue Mar 27 11:23:31 2012
    esriJobMessageTypeInformative: Running script InputScript...
    esriJobMessageTypeInformative: START
    esriJobMessageTypeInformative: e='NoneType' object is not callable
    esriJobMessageTypeInformative: Completed script InputScript...
    esriJobMessageTypeInformative: Succeeded at Tue Mar 27 11:23:31 2012 (Elapsed Time: 0.00 seconds)

Using the old school debbuging technique, using log (arcpy.AddMessage actually), I found out the the exception is thrown by "f=cursor.fetchone()". Removing this instruction prevents the service to crash ... but does not longer do the work ... 😞

My configuration is as follow:

• Windows Server 2008 R2 Standart (64bit)


• ArcGIS Server 10


• Python 2.6 (bundled version)


• pyodbc-3.0.2 32b (pyodbc-3.0.2.win32-py2.6.exe, because 64bit does not work because of a "Not a valid Win32 application" error).


• Microsoft SQL Server 2008 R2 (64b)

Help would be very appreciated!
Thanks a lot,
Octave

[MikeHunter]

pyodbc and ArcGIS do not play well together.  I get the same problem described here, run once ok, then crash on the 2nd run.  There are 2 workarounds that I've found.  1st, run the geoprocessing tool out of process.  I don't like this option, since it slows things down considerably.  2nd is simply to import pyodbc into the Python window global namespace when you start Arcmap or ArcCatalog.  For some reason when that's done, any tool using pyodbc will run as many times as needed.  You can do this automatically when arcmap or arccatalog starts (if it starts with the Python window open) by setting the PYTHONSTARTUP environment variable to a startup script.  Here's the pertinent part of my startup script:

if __name__ == '__main__':
    import os, sys
    e = sys.executable.lower()   
    if 'arcmap.exe' in e or 'arccatalog.exe' in e:
        import pyodbc
        main()

A better way would be to do this with the code in the tool script, but I haven't figured a way how to do it.  And better still would be for our ESRI guys to fix the conflict.

Mike

[SigurdHaagensen]

Hi

I�??ve been struggling with the same issue. I have not been able to make my script run properly when deploying them as geoprocessing services using the pyodbc module. To me it seems like installing PyWin32 and using the odbc module is a better approach. Pyodbc and odbc is very alike, the only changes I had to make to my scripts were to

1)
import odbc
instead of
import pyodbc

2)
and change
sqlCnn = pyodbc.connect(<connection string>)
to
sqlCnn = odbc.odbc(<connection string>)


-Sigurd

[SamuelHenderson]

mahunter243

pyodbc and ArcGIS do not play well together. I get the same problem described here, run once ok, then crash on the 2nd run. There are 2 workarounds that I've found. 1st, run the geoprocessing tool out of process. I don't like this option, since it slows things down considerably. 2nd is simply to import pyodbc into the Python window global namespace when you start Arcmap or ArcCatalog. For some reason when that's done, any tool using pyodbc will run as many times as needed. You can do this automatically when arcmap or arccatalog starts (if it starts with the Python window open) by setting the PYTHONSTARTUP environment variable to a startup script. Here's the pertinent part of my startup script:

 
if __name__ == '__main__':
    import os, sys
    e = sys.executable.lower()   
    if 'arcmap.exe' in e or 'arccatalog.exe' in e:
        import pyodbc
        main()

I'm running into the same problem. Unfortunately I can't run the script out of process because I'm sharing the tool as a GP service and when I run the GP service (with the tool out of process) I get a generic "ERROR 704:  Something is wrong in the script" message.

I don't think your second solution would work for me either since I won't be running the tool from ArcMap or ArcCatatalog.

I�??ve been struggling with the same issue. I have not been able to make my script run properly when deploying them as geoprocessing services using the pyodbc module. To me it seems like installing PyWin32 and using the odbc module is a better approach. Pyodbc and odbc is very alike,

Hmm.  I'll try the Python for Windows Extensions package and report back 🙂

[AveVill1]

Hi,

Does anyone have experience with pyodbc in ArcGIS 10.1 Server on Linux?

I made a short script for testing and published it as a GP Service.

import arcpy, pyodbc

conn = pyodbc.connect('DSN=<DSN_name>;PWD=<Password>')
conn.autocommit = True
cursor = conn.cursor()
cursor.execute('SET CHAINED OFF')

cursor.execute('{call Sybase_Stored_proc()}')
rows = cursor.fetchall()
arcpy.AddMessage(len(rows))

cursor.close()
conn.close()
del rows, cursor, conn

GP Service runs successfully first time and fails next two times.
Second time an error message in ArcGIS Server Manager Logs tells:
[INDENT]Traceback (most recent call last): File "/opt/arcgis/server/usr/directories/arcgissystem/arcgisinput/dbconSybase.GPServer/extracted/v101/work/dbconSybase.py", line 9, in File "C:\Python27\ArcGISx6410.1\lib\decimal.py", line 543, in __new__ m = _parser(value.strip()) TypeError: 'NoneType' object is not callable Failed to execute (dbconSybase). Failed to execute (dbconSybase).[/INDENT]
Third time an error message in ArcGIS Server Manager Logs tells:
[INDENT]The containing process for 'dbconSybase' job 'jf9cfffae54d142d688d35d96a2457981' has crashed.[/INDENT]


Following Mike's recommendation to import pyodbc into the Python window global namespace in Arcmap 10.1 before running script tool helps to get rid of the problem in ArcGIS Desktop, but I haven't found a solution for ArcGIS Server Geoprocessing Service.

Our server configuration is:

• Linux 2.6.32-358.0.1.el6.x86_64 x86_64 (CentOS release 6.4)


• ArcGIS Server 10.1


• Python 2.7


• pyodbc-3.0.6.win-amd64-py2.7 (under "/opt/arcgis/server/framework/runtime/.wine/drive_c/Python27/ArcGISx6410.1/lib/site-packages/)

It would be nice to hear the suggestions from ESRI people. Is there something missing in the code we use for the GP Service script? Would it be better to forget pyodbc and to use something else to connect Sybase ASE database from GP Service?

Thanks in advance,
Ave Vill