nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

arcpy.da.insertcursor slow performance and stopiteration error

Anonymous User

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")))

Trouver plus de publications taguées avec

Accepted answers

All comments

Anonymous User

Some good news and some bad news... The updatecursor completed in 22 seconds. The insertcursor still took 45 minutes. I even removed the utils.log and arcpy.addmessage calls in an attempt to isolate the processing time.

Anonymous User

Great, its running now. I'll report back on the performance. Thank you. I'll need to do more reading on using brackets vs parentheses so I can better understand what you provided.

RichardFairhurst

Line 36 is fixed. I should have used brackets, not parentheses, to access the item out of the tuple. As i said, it is untested code and little details like that get me nearly every time.

Anonymous User

Thank you Richard Fairhurst‌ It now fails at line 36, while waiting for your response I will read more about dictionary.

Traceback info:

File "C:\GISData\Utilities\ValveInspections.py", line 36, in <module>

scRow.insert(0,keyValue(1))

Error Info:

'tuple' object is not callable

RichardFairhurst

All source data should be read into a dictionary and then the insert cursor and update cursor should be populated from that. Never do embedded cursors that iterate through where clauses or use cursors that include the OrderBy clause. Totally a waste of time. Add the sort field as the first part of a tuple dictionary key and sort the dictionary keys. Performance will be incredibly fast. The untested code below should work and be done in under 1 minute for the cursor portions.

The update cursor error occurred because you were trying to update the search cursor row, not the update cursor row. InsertRow and updateRow do not have the same syntax.

See my blog about Turbo Charging Data Manipulation with Python Cursors and Dictionaries.

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(False,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>Create dictionary from sourceFC to insert rows into targetTable start<TIMESTAMP position='start'>" + time.strftime("%Y%m%d.%H%M%S") + "</TIMESTAMP></COMMENT>\n", logFile)  
    print("Create dictionary from sourceFC to insert rows into targetTable start " + str(time.strftime("%Y%m%d.%H%M%S")))  
    valueDict = {(r[10],r[0]):(r[1:]) for r in arcpy.da.SearchCursor(sourceFC, fieldsToInsert)}  
    ic = arcpy.da.InsertCursor(targetTable, fieldsToInsert)  
    for keyValue in sorted(valueDict.keys()):    
        scRow = list(valueDict[keyValue])
        scRow.insert(0,keyValue[1])
        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)
    del ic
    edit.stopOperation()
    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")))  
except Exception as err:
    raise err
    print err.message
    Utils.Log("<ERROR>Failed Insert cursor operation<ERROR>\n", logFile)
try:
    edit.startOperation() 
    Utils.Log("<COMMENT>Create dictionary from sourceFC to update rows in targetFC<TIMESTAMP position='start'>" + time.strftime("%Y%m%d.%H%M%S") + "</TIMESTAMP></COMMENT>\n", logFile)  
    print("Create dictionary from sourceFC to update rows in targetFC start " + str(time.strftime("%Y%m%d.%H%M%S")))  
    valueDict = {r[0]:(r[1:]) for r in arcpy.da.SearchCursor(sourceFC, fieldsToUpdate)}  
    uc = arcpy.da.UpdateCursor(targetFC, fieldsToUpdate)  
    for updateRow in uc:
        keyValue = updateRow[0]
        if keyValue in valueDict:
            for n in range (1,len(fieldsToUpdate)):  
                updateRow = valueDict[keyValue][n-1]
            arcpy.AddMessage("Updating gGasValve " + (keyValue) #+ " ValveBox to " + (sc2Row[1]))  
            print("Updating gGasValve " + (keyValue) #+ " ValveBox to " + (sc2Row[1]))  
            Utils.Log("<ACTION>Updating gGasValve " + (keyValue) + "</ACTION>\n", logFile)  
            try:  
                uc.updateRow(updateRow)  
            except Exception as err:  
                raise err  
                print err.message  
                Utils.Log("<ERROR>Failed updating gGasValve FacilityID " + valveNumber + "</ERROR>\n", logFile)  
    del uc
    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")))

Rejoindre la conversation

Les membres connectés peuvent publier, suivre les mises à jour, et plus encore. Nouveau ici ? Inscrivez-vous gratuitement.

Liens rapides

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.