POST
|
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
... View more
07-22-2010
06:33 AM
|
0
|
0
|
1975
|
POST
|
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
... View more
06-24-2010
07:27 AM
|
0
|
17
|
11880
|
Online Status |
Offline
|
Date Last Visited |
11-11-2020
02:23 AM
|