AnsweredAssumed Answered

arcpy.da.insertcursor slow performance and stopiteration error

Question asked by friendde on Jan 3, 2015
Latest reply on Jan 5, 2015 by friendde

I have two questions about my code below. For the insertcursor portion it took 45 minutes to insert 542 rows into a table (not a featureclass table). That seems way too long for a small numbers of rows. Then a "StopIteration: iteration not started" error was raised during the first update in updatecursor portion. Advice on improving performance is requested and why was the error raised if I have 542 rows to update in the featureclass?

 

The overall purpose of this is to take portions of a valve inspection from sourceFC and it split data into targetTable and targetFC

 

import arcpy, os, time
import Utils
import MM


logFileName = "C:/GISData/Utilities/Logs/ValveInspections.log"
mmapp = MM.InitializeLicense()
previousAUMode = MM.DisableAUs()


workspace = 'Database Connections/OSAuth.sde'
sourceFC = 'Database Connections/OSAuth.sde/Inspection/giValveInspection'
targetTable = 'Database Connections/OSAuth.sde/GVALVEINSPECTION'
targetFC = 'Database Connections/OSAuth.sde/Gas/gValve'


fieldsToInsert = 'FACILITYID','REPAIRSMADE','CONDITIONFOUND','CONDITIONLEFT','MAINTENANCENOTES','TIMEWORKED','LUBRICATEDVALVE','WORKEDBY','INSPECTOR','INSPECTIOID','INSPECTIONDATE','INSPECTIONSTATUS','PAINTED','VACUUMED','TAGGED','WORKEDBY','FLAGGED','MARKER','INSECTICIDE','CGIBEG','CGIEND','MAPCORRECT','EXERCISED','GASVALVEOID'
fieldsToUpdate = 'FACILITYID','VALVEBOX','INSPECTIONDATE','INSPECTIONSTATUS','TAGGED','FLAGGED','MARKER'


logFile = Utils.Log("<PROCESS name='ValveInspections.py'><TIMESTAMP position='start'>" + time.strftime("%Y%m%d.%H%M%S") + "</TIMESTAMP>\n", None, logFileName)
print("PROCESS ValveInspections.py start " + time.strftime("%Y%m%d.%H%M%S"))
try:
        Utils.Log("<COMMENT>Starting edit operation</COMMENT>\n", logFile)
        print("Starting edit operation")
        edit = arcpy.da.Editor(workspace)
        edit.startEditing(True,True)
        edit.startOperation()
except Exception as err:
        raise err
        print err.message
        Utils.Log("<ERROR>Failed to start edit operation</ERROR>\n", logFile)
try:
        Utils.Log("<COMMENT>Searching sourceFC to insert rows into targetTable<TIMESTAMP position='start'>" + time.strftime("%Y%m%d.%H%M%S") + "</TIMESTAMP></COMMENT>\n", logFile)
        print("Searching sourceFC to insert rows into targetTable start " + str(time.strftime("%Y%m%d.%H%M%S")))
        ic = arcpy.da.InsertCursor(targetTable, fieldsToInsert)
        with arcpy.da.SearchCursor(sourceFC, fieldsToInsert, sql_clause=(None, 'ORDER BY INSPECTIONDATE')) as sc:
                #with arcpy.da.InsertCursor(targetTable, fieldsToInsert) as ic:
            for scRow in sc:
                arcpy.AddMessage("Inserting inspection for valve number " + (scRow[0]) + " with inspection date " + str((scRow[10])))
                print("Inserting inspection for valve number " + (scRow[0]) + " with inspection date " + str((scRow[10])))
                Utils.Log("<ACTION>Inserting inspection for " + (scRow[0]) + " with inspection date " + str((scRow[10])) + "</ACTION>\n", logFile)
                ic.insertRow((scRow))
        Utils.Log("<COMMENT>Stopped searching sourceFC to insert rows into targetTable<TIMESTAMP position='end'>" + time.strftime("%Y%m%d.%H%M%S") + "</TIMESTAMP></COMMENT>\n", logFile)
        print("Stopped searching sourceFC to insert rows into targetTable end " + str(time.strftime("%Y%m%d.%H%M%S")))
        Utils.Log("<COMMENT>Searching sourceFC to update rows in targetFC<TIMESTAMP position='start'>" + time.strftime("%Y%m%d.%H%M%S") + "</TIMESTAMP></COMMENT>\n", logFile)
        print("Searching sourceFC to update rows in targetFC start " + str(time.strftime("%Y%m%d.%H%M%S")))
        with arcpy.da.SearchCursor(sourceFC, fieldsToUpdate, sql_clause=(None, 'ORDER BY INSPECTIONDATE')) as sc2:
                for sc2Row in sc2:
                        valveNumber = (sc2Row[0])
                        #valveBox = (sc2Row[1])
                        uc = arcpy.da.UpdateCursor(targetFC, fieldsToUpdate, where_clause="FACILITYID = '" + (sc2Row[0]) + "'")
                        arcpy.AddMessage("Updating gGasValve " + (sc2Row[0])) #+ " ValveBox to " + (sc2Row[1]))
                        print("Updating gGasValve " + (sc2Row[0])) #+ " ValveBox to " + (sc2Row[1]))
                        Utils.Log("<ACTION>Updating gGasValve " + (sc2Row[0]) + "</ACTION>\n", logFile)
                        try:
                                uc.updateRow((sc2Row))
                        except Exception as err:
                                raise err
                                print err.message
                                Utils.Log("<ERROR>Failed updating gGasValve FacilityID " + valveNumber + "</ERROR>\n", logFile)
        Utils.Log("<COMMENT>Stopped searching sourceFC to update rows in targetFC<TIMESTAMP position='end'>" + time.strftime("%Y%m%d.%H%M%S") + "</TIMESTAMP></COMMENT>\n", logFile)
        print("Stopped searching sourceFC to update rows in targetFC end " + str(time.strftime("%Y%m%d.%H%M%S")))
except Exception as err:
    raise err
    print err.message
finally:
    edit.stopOperation()
    edit.stopEditing(True)
    mmapp.Shutdown
    del mmapp
    Utils.Log("<PROCESS name='ValveInspections.py'><TIMESTAMP position='end'>" + time.strftime("%Y%m%d.%H%M%S") + "</TIMESTAMP>\n", logFile)
    print("ValveInspections.py end '>" + str(time.strftime("%Y%m%d.%H%M%S")))

Outcomes