Correct usage of a counter using arcpy.da.UpdateCursor

Question

In the Python code inside a Calculate Value tool in my model, I have the following:

getNum("%Selected Features%")

---

def getNum(ftr):
 lst = [4, 11, 15] #values for SQL expression
 x = 20
 fields = ('TYPE_A', 'TYPE_B', 'NUM_ID')
 for l in lst:
 y = x + 1
 whereclause = """"NUM_ID" = %s """ % l
 with arcpy.da.UpdateCursor(ftr, fields, whereclause) as cursor:
 for row in cursor:
 if row[0] ! = row[1]:
 row[2] = y
 cursor.updateRow(row)
 y += 1

What I would like to do is, for every element in lst, create a cursor based on a selection of records where NUM_ID = l. The values in TYPE_A and TYPE_B should be compared for each record and updated if necessary.

The script is failing after the cursor is created, with the error Item not in collection. If I remove the whereclause, it executes. Once the records have been selected and the values compared and updated if necessary, I need to increment the counter y by 1. I think I'm doing it in the wrong place though, because if I run the script without the whereclause it does execute correctly.When I check the records with the changed values, they are now all set to y, which indicates that y was not incremented.

• blah238: Try stepping through your code in a Python debugger to get an idea of what's going on.
• dango: Is n defined somewhere else? cant see it in the code you posted.
• Cindy Jayakumar: @dango sorry was a typo, changed it to y.

Answer 1

Couple of points that might help:

• You redefine the value of y at the beginning of your for loop, so every time the loop is executed y is reset to x + 1. Instead set y outside the loop:

y = x + 1
for l in lst:
 #...

• Depending on the type of feature class you're using ArcGIS requires different delimiters around field names. For example, [PERSONAL_GDB] vs "FILE_GDB". To prevent having to worry about this you can use the AddFieldDelimiters arcpy function to get back the correctly delimited fields.

#Using dictionary string-formatting
whereclause = "%(sql_name)s = %(l)i """ % {"sql_name":arcpy.AddFieldDelimiters(ftr, "NUM_ID"), "l":l}

• Lastly you should probably check that NUM_ID exists in your feature class as otherwise the cursor will fall over. You can use ListFields for this.

assert "NUM_ID" in [f.name for f in arcpy.ListFields(ftr)], "NUM_ID field no in feature class"

• blah238: I think the field assertion check is probably a little overly defensive, but that's just me. I would expect arcpy to generate a reasonable error message on its own about this.
• om_henners: @blah238 You're right, it should - this is more sa an example of a way you could check. Also, cheers for the props!
• Cindy Jayakumar: Thanks for all the tips. After changing the whereclause, it failed because it was expecting an integer but I'm actually testing for a string, so I changed it to %(l)s so it's fine. It now fails on the fact that is an invalid sql statement.
• Cindy Jayakumar: The counter now increments correctly and the field values are updated as expected. Thank you

Answer 2

In addition to @om_henners great suggestions, it seems to me like you could use an IN statement to reduce the number of queries to the database from len(lst) to 1. I have an example function to build such a WHERE clause in this answer: Select features by attribute if in Python list

• Cindy Jayakumar: Thanks for the link to your other answer, it solved the issue I had in the comment I made above.