I am getting an incorrect SQL statement while trying to extractby attributes where item is in list. Can somebody help me form the correct SQL statement?
Line 30 below...
import arcpy import os arcpy.env.overwriteOutput = 1 # Check out any necessary licenses arcpy.CheckOutExtension("Spatial") base = r'C:\Users\Noah\Desktop\LCP Example\Scratch.gdb' name = "landuse_Clip" fc= os.path.join(base,name) field = "Value" arcpy.env.workspace = base # Use SearchCursor with list comprehension to return a # unique set of values in the specified field # for row in arcpy.da.SearchCursor(fc, "Value"): values = [row[0]] query = "Value" in values # Extract Features where Value is in values attExtract = ExtractByAttributes(name, query) attExtract.save("Extract_landuseRaster") landuseRasterWeighted = "Extract_landuseRaster"
Solved! Go to Solution.
Beyond the issue identified by Dan Patterson, you have two additional problems. First, the SQL "IN" operator requires the set of values to be enclosed in parentheses, e.g., expression IN (value1, value2, value3, ...). Second, the ExtractByAttributes where_clause is a string-based SQL expression, and you are passing a reference to a python object instead of an actual string-based list. Assuming values is a Python list containing numbers you want to check for, line 30 would have to be rewritten as:
query = "Value IN ({})".format(str(values).strip('[]'))
this line makes no sense to me
values = [row[0]]
are you trying to append values to a values list? if so values needs to be defined outside of the cursor and then row results appended to it
values = []
.....
values.append(row[0]) # whatever
then figure out what to do with the values list
Hi Dan. Thanks for chiming in. I am just trying to return a list of unique values. This revised portion of the script may have been less confusing. This one was also successful in printing the list in the desired format. How can I use this now in my sql expression?
# Use SearchCursor with list comprehension to return a # unique set of values in the specified field # values = [row[0] for row in arcpy.da.SearchCursor(fc, (field))] uniqueValues = (values) print(uniqueValues)
Thank you for your help with this!
Beyond the issue identified by Dan Patterson, you have two additional problems. First, the SQL "IN" operator requires the set of values to be enclosed in parentheses, e.g., expression IN (value1, value2, value3, ...). Second, the ExtractByAttributes where_clause is a string-based SQL expression, and you are passing a reference to a python object instead of an actual string-based list. Assuming values is a Python list containing numbers you want to check for, line 30 would have to be rewritten as:
query = "Value IN ({})".format(str(values).strip('[]'))
Here is code that combines Joshua and Dan's suggestions:
# get unique list of values values = [row[0] for row in arcpy.da.SearchCursor(fc, (field))] uniqueValues = list(set(values)) # ["a","b","c"] # create a SQL expression string in the form "FIELD IN ('a', 'b', 'c')" where = "FIELD IN ({})".format(",".join(["'{}'".format(v) for v in uniqueValues])
Thank you all!! You all seem very knowledgeable and have provided much useful info. Josh your query statement is precisely what I am looking for with this thread. Below is the complete code for this operation I am posting for the archives.
# get unique list of values values = [row[0] for row in arcpy.da.SearchCursor(fc, (field))] query = "Value IN ({})".format(str(values).strip('[]')) # Extract Features where Value is in values attExtract = ExtractByAttributes(name, query) attExtract.save("Extract_landuseRaster") landuseRasterWeighted = "Extract_landuseRaster"
You don't get unique values that way, unless they are unique to start with. In the following example, I got a gazillion values until I converted the list to a set (and optionally sorted). From there, construct your query as Joshua Bixby indicated
>>> import arcpy >>> fc = "C:\SomePath\SomeFile.shp" >>> fld = "subclass" >>> values = [row[0] for row in arcpy.da.SearchCursor(fc, (fld))] >>> unique_values_set = set(values) >>> unique_values_set set([u'ARTERIAL', u'TRANSITWAY', u'LOCAL', u'RAMP', u'CONNECTOR', u'MAJCOLLECTOR', u'HIGHWAY', u'COLLECTOR', u'CHANNEL']) >>> >>> unique_values_list = list(set(values)) # convert the set to a list and sort >>> unique_values_list [u'ARTERIAL', u'TRANSITWAY', u'LOCAL', u'RAMP', u'CONNECTOR', u'MAJCOLLECTOR', u'HIGHWAY', u'COLLECTOR', u'CHANNEL']
Just to be on the safe side... 'set' is a useful tool but it is unordered like dictionaries