Using SearchCursor with UpdateCursor

[MarcCusumano]

Hello, I'm trying to use an UpdateCursor inside of a SearchCursor to populate sequence numbers on an inspection table according to a parcitular UserID. Basically the SearchCursor iterates through a small table with a list of about 29 user ID's (the table changes over time) and uses the values in the where_clause of the updatecursor.

The UpdateCursor sorts the order of the input table based on a structure number and populates the sequences from 1 to 0. Here is the code I have tried:

techrows = arcpy.SearchCursor(psegGasGDB_InputPrefix + "ASSIGNED_TECHS")  for row in techrows:      if TechID != row.ASSIGNED_USER:          TechID = row.ASSIGNED_USER         where_clause = "ASSIGNED_USER = " + TechID         rows = arcpy.UpdateCursor(CONVERSION_InputPrefix + "CPTestPointInspection", where_clause, "SEQ_NO; GAS_CORR_STRUCT_NO", "GAS_CORR_STRUCT_NO")          for row in rows:              try:                        print row                        stop = 1                        row.SetValue("SEQ_NO", stop)                         stop = stop + 1               except Exception as e:                   print e                   logging.error(": %s" %(e))          del rows     del techrows 

But this gives an "Error 999999: Index passed was not within the valid range". Can't figure out what I'm doing wrong...

[MathewCoyle · Accepted solution]

First off, using nested cursors is generally a bad idea. Extracting the information from your search cursor and storing them in a dictionary to access with your update cursor is the preferred method. That being said, your issue is you define the row object for both cursors as the same variable, causing the code to implode. Also it looks like your indentation may be off.

[ChristopherThompson]

What kind of value is ASSIGNED_USER? If it is a string value then I think the where clause you specify here is going to fail, in fact it will fail anyway because it is not delimitted correctly:

where_clause = "ASSIGNED_USER = " + TechID

that probably needs to look more like one of these:

where_clause = """ "ASSIGNED_USER" = """ + TechID # if TechID is numeric
where_clause = """ "ASSIGNED_USER" = """ + "'"+TechID+"'" # if TechID is string

Beyond this, I think the logic of running a cursor inside another cursor is challenging to pull off, and am wondering if there may be some confusion between the two different row objects that are being referenced as 'row' - you don't need to name those row objects 'row', you can just give them any name, so maybe changing one of those to be a different name will make a difference. What you might do instead is use your search cursor to create a list of valid Users, then iterate through that list in a for loop, supplying each user ID to the where clause in your update cursor.

[MathewCoyle]

Looking at it again, probably the biggest problem here is that you are deleting your cursor in the middle of your loop.

This line should be outside your loop.

del techrows

[MarcCusumano]

Thank you Matt, using a dictionary worked well:

lookupDict = {}
searchRows = arcpy.SearchCursor(psegGasGDB_InputPrefix + "ASSIGNED_TECHS")
searchRow = searchRows.next()

while searchRow:
     lookupDict[searchRow.TECH_ID] = [searchRow.TECH_ID]
     where_clause =  """ "ASSIGNED_USER" = """ + "'"+searchRow.TECH_ID+"'"
     updatecursor = arcpy.UpdateCursor(CONVERSION_InputPrefix + "CPTestPointInspection", where_clause, "", "SEQ_NO; GAS_CORR_STRUCT_NO", "GAS_CORR_STRUCT_NO")
     stop = 0
     fldSEQNO = "SEQ_NO"
     
     for row in updatecursor:

          try:
             print stop
             stop = stop + 1
             row.setValue(fldSEQNO, stop)
             cursor.updateRow(row)

                    
          except Exception as e:
             print e
             logging.error(": %s" %(e))
             
     searchRow = searchRows.next()
     
del searchRow
del searchRows

[MarcCusumano]

Hi all, I'm trying to re-write this to run through SQL Server for performance reasons.

Here is what I have, but it is not generating the sequence from 1 to n, it is starting at the row number of the PARTITION_BY and GROUP_BY statement.

UPDATE CPTESTPOINTINSPECTION
SET CPTESTPOINTINSPECTION.SEQ_NO = seq

FROM 
(
SELECT t.TECH_ID,i.GAS_CORR_STRUCT_NO, INSPECTIONDATE, SEQ_NO, ROW_NUMBER() OVER (PARTITION BY t.TECH_ID ORDER BY i.GAS_CORR_STRUCT_NO) AS Seq
FROM ASSIGNED_TECHS t
INNER JOIN GISDG..CONVERSION.CPTESTPOINTINSPECTION i
ON i.ASSIGNED_USER  = t.TECH_ID
) CPTESTPOINTINSPECTION
WHERE CPTESTPOINTINSPECTION.INSPECTIONDATE IS NULL

I know this isn't a SQL forum but I figured I'd ask anyway, I know there are very talented people here, so I figured I'd give it a shot.