Can you please take a look at this snippet and let me know why I am not able to properly pass the Where Clause ('"[NAME_1]" = Ohio') in SearchCursor?

import arcpy
from arcpy import env
def unique_values(table , field):
 with arcpy.da.SearchCursor(table, [field], '"[NAME_1]" = Ohio') as cursor:
 return sorted({row[0] for row in cursor})
uniques = unique_values(r'C:\arcgis\ArcTutor\AAA\src\USA.shp' , 'NAME_2')
for unique in uniques:
  print (unique)

I am getting this error on runtime

Traceback (most recent call last):
  File "<module1>", line 8, in <module>
  File "<module1>", line 7, in unique_values
  File "<module1>", line 7, in <setcomp>
RuntimeError: Unspecified error

Same error on this format as well

with arcpy.da.SearchCursor(table, [field], '"NAME_1" = Ohio') as cursor:
  • Skip the bracers, use "NAME_1 = 'Ohio'" (note the use of double and single quotes). If you're only using one field it doesn't need to be a list, use with arcpy.da.SearchCursor(table, field, "NAME_1 = 'Ohio'") as cursor: and you should reduce your problems. What's the next line about? You're making a dictionary but it's not, there's no key:value pairs, surely you want to do sorted([row[0] for row in cursor])? – Michael Stimson Oct 16 at 5:00
  • 2
    @Fezter, no they're not. Try it and see. – Michael Stimson Oct 16 at 5:04
  • 1
    This is how it shoul like for shapefile "NAME_1" = 'Ohio' – FelixIP Oct 16 at 5:06
  • 2
    I understand your aversion to shapefiles @Fezter, I have found that no quote or bracers are required for any of the common storage types when defining a cursor. It may be that some obscure enterprise database needs them but I've not found one yet that complained. – Michael Stimson Oct 16 at 5:08
  • 1
    I agree with @MichaelStimson on this one. I think the need to use AddFieldDelimiters or to use field delimiters evaporated 3-4 dot releases ago for ArcGIS Desktop 10.x and they've never been needed for ArcGIS Pro. – PolyGeo Oct 16 at 6:53
up vote 3 down vote accepted

I wasn't able to replicate your error, but try this:

import arcpy    

expression = "NAME_1 = 'Ohio'"

def unique_values(table , field):
    with arcpy.da.SearchCursor(table, [field], where_clause=expression) as cursor:
        return sorted({row[0] for row in cursor})

uniques = unique_values(r'C:\arcgis\ArcTutor\AAA\src\USA.shp' , 'NAME_2')

for unique in uniques:
    print (unique)

The SearchCursor where_clause parameter takes a SQL string as its input. There are more examples here

Use AddFieldDelimiters:

The field delimiters used in an SQL expression differ depending on the format of the queried data. For instance, file geodatabases and shapefiles use double quotation marks (" "), personal geodatabases use square brackets ([ ]), and enterprise geodatabases don't use field delimiters. The function can take away the guess work in ensuring that the field delimiters used with your SQL expression are the correct ones.

with single quotes around Ohio and three double quotes surrounding everything:

sql = """{0} = 'Ohio'""".format(arcpy.AddFieldDelimiters(table,'NAME_1'))
with arcpy.da.SearchCursor(table,field,sql) as cursor:
    ...
  • 1
    The problem isn't with the field as specified but with the value, Felix stated it most succinctly, the SQL definitely needs a single quote delimiter for a string literal, as the OP had the query stated the SQL was looking to compare NAME_1 field with a field called Ohio and not with the string literal 'Ohio'. – Michael Stimson Oct 16 at 5:17
  • @MichaelStimson as the question is currently written i see no indication that Ohio is a field – BERA Oct 16 at 7:59
  • And that is the problem, Ohio is not a field but the SQL is looking for it and generating the unspecified error when it can't find the field called Ohio within the table. – Michael Stimson Oct 16 at 23:15

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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