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
    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
  • 1
    Is n defined somewhere else? cant see it in the code you posted. – dango Jan 29 '13 at 19:19
  • @dango sorry was a typo, changed it to y. – Cindy Jayakumar Jan 30 '13 at 5:14

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"
  • 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 4: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 5:39
  • The counter now increments correctly and the field values are updated as expected. Thank you – Cindy Jayakumar Jan 30 '13 at 5:46

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

  • 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 5:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.