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.

Try stepping through your code in a Python debugger to get an idea of what's going on. — blah238, Jan 29 '13 at 19:04
Is n defined somewhere else? cant see it in the code you posted. — dango, Jan 29 '13 at 19:19

score 5 · Accepted Answer · answered Jan 29 '13 at 21:22

5

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"

answered Jan 29 '13 at 21:22

om_henners

15,642
2
46
86

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. – blah238 Jan 29 '13 at 23:55
@blah238 You're right, it should - this is more sa an example of a way you could check. Also, cheers for the props! – om_henners Jan 30 '13 at 04:17
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 Jan 30 '13 at 05:39
The counter now increments correctly and the field values are updated as expected. Thank you – Cindy Jayakumar Jan 30 '13 at 05:46

score 3 · Answer 2 · edited Apr 13 '17 at 12:33

3

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

edited Apr 13 '17 at 12:33

Community

1

answered Jan 29 '13 at 22:59

blah238

35,793
7
94
195

Thanks for the link to your other answer, it solved the issue I had in the comment I made above. – Cindy Jayakumar Jan 30 '13 at 05:42

Correct usage of a counter using arcpy.da.UpdateCursor

2 Answers2

Linked