Search Cursors Inside of Loops Throwing Runtime Errors?

Question

I am attempting to create a function to use a search cursor and associated list to get the unique values of a specified field and then use that list of unique values to loop through each value, create a where_clause with that unique value, and then use a search cursor to sum the field values in a target field for the queried results. Currently I am trying to get area(acreage) values with this measurement.

import arcpy
#User provides workspace, feature class, field to search by and desired 
 field to sum together

def search_test(workspace,inFC,searchField,targetField):
arcpy.env.workspace = workspace
fields = [searchField,targetField]
#List to store values
unique_field_attributes = []

with arcpy.da.SearchCursor(inFC,fields) as cursor:
    for row in cursor:
        if row[0] not in unique_field_attributes:
           unique_field_attributes.append(row[0])

unique_field_attributes.sort()
print "unique attributes: ",unique_field_attributes

Here everything runs fine, I get a nice ordered list of attributes. The problem occurs when I try to use a search cursor inside of a for loop.

for attr in unique_field_attributes:
    swhere_clause = """{0} = {1}""".format(arcpy.AddFieldDelimiters(
                    inFC,searchField),attr)
    print swhere_clause

When I do not have the search cursor after this in my program, it prints all of the where clauses correctly. However, when I add the search cursor it only prints the first where clause and I get a Runtime Error

with arcpy.da.SearchCursor(inFC,fields,swhere_clause) as cursor:
    for row in cursor:
        #Attempt to sum an area value
        area += row[1]

Error looks like this:

Traceback (most recent call last):
    File "<stdin>", line 1, in <module>
    File "SearchTest.py", line 24, in search_test 
        for row in cursor:
    RuntimeError: an invalid SQL statement was used. [file name here]

Any help or suggestions?

Answer 1

I'm not sure if I get it but I'll give it a try

It looks like your where clause may have a problem.. Here's what you can try:

If the value you're looking for is a string data use '' in your query, like so:

for attr in unique_field_attributes:
swhere_clause = """{0} = '{1}'""".format(arcpy.AddFieldDelimiters(
                inFC,searchField),attr)
print swhere_clause

You may also have some trouble with the cursor and the where clause, but I'm not sure. I suggest you try making a SelectLayerByAttribute on a layer created from the feature class you're using and then running the cursor on that layer. Here's an example:

my_layer = "in_memory\\MyFcLayer"
arcpy.MakeFeatureLayer_management(inFc, my_layer)
arcpy.SelectLayerByAttribute_management(my_layer, "NEW_SELECTION", swhere_clause)
with arcpy.da.SearchCursor(my_layer, fields)as cursor:
    for row in cursor:
        area += row[1]

Just one more thing, try running the where clause you're generating in ArcGis to see if it works