topic Re: Problem using PYODBC in ArcGIS script in ArcGIS Spatial Analyst Questions

Problem using PYODBC in ArcGIS script

JessicaBhalerao — Thu, 24 Jun 2010 14:27:43 GMT

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

Re: Problem using PYODBC in ArcGIS script

KimOllivier — Fri, 25 Jun 2010 10:16:54 GMT

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.

Re: Problem using PYODBC in ArcGIS script

JessicaBhalerao — Thu, 22 Jul 2010 13:33:59 GMT

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

Re: Problem using PYODBC in ArcGIS script

neilwebster — Fri, 17 Sep 2010 11:58:00 GMT

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.

Re: Problem using PYODBC in ArcGIS script

billchuang — Thu, 28 Jul 2011 06:40:45 GMT

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?

Re: Problem using PYODBC in ArcGIS script

DetailleOctave — Sun, 12 Dec 2021 07:44:18 GMT

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

Re: Problem using PYODBC in ArcGIS script

MikeHunter — Sun, 12 Dec 2021 07:44:22 GMT

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

Re: Problem using PYODBC in ArcGIS script

SigurdHaagensen — Thu, 02 Aug 2012 06:28:35 GMT

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

Re: Problem using PYODBC in ArcGIS script

SamuelHenderson — Sun, 12 Dec 2021 07:44:24 GMT

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 🙂

Re: Problem using PYODBC in ArcGIS script

AveVill1 — Sun, 12 Dec 2021 07:44:27 GMT

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

Re: Problem using PYODBC in ArcGIS script

JoseZambrana — Fri, 31 May 2013 20:41:18 GMT

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.

Re: Problem using PYODBC in ArcGIS script

JakubOrálek — Wed, 17 Jul 2013 06:14:19 GMT

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

Re: Problem using PYODBC in ArcGIS script

DorothyMortenson — Fri, 22 Aug 2014 17:08:33 GMT

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

Re: Problem using PYODBC in ArcGIS script

DorothyMortenson — Wed, 15 Oct 2014 15:28:30 GMT

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.

Re: Problem using PYODBC in ArcGIS script

NicholasBraaksma — Thu, 06 Nov 2014 21:07:15 GMT

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

Re: Problem using PYODBC in ArcGIS script

AndrewHughes — Thu, 05 Mar 2015 05:36:29 GMT

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.

Re: Problem using PYODBC in ArcGIS script

ChadStidham1 — Wed, 01 Jul 2015 14:52:21 GMT

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.

Re: Problem using PYODBC in ArcGIS script

ScottDavis — Tue, 02 Jul 2019 18:27:37 GMT

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!