topic Re: Whereclause in geoprocessing tool *select where item in list. in Python Questions

Whereclause in geoprocessing tool *select where item in list.

NoahHuntington — Sun, 12 Dec 2021 06:34:38 GMT

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"

Re: Whereclause in geoprocessing tool *select where item in list.

DanPatterson_Retired — Thu, 11 Dec 2014 19:57:16 GMT

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

Re: Whereclause in geoprocessing tool *select where item in list.

NoahHuntington — Sun, 12 Dec 2021 06:34:41 GMT

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!

Re: Whereclause in geoprocessing tool *select where item in list.

JoshuaBixby — Thu, 11 Dec 2014 21:51:09 GMT

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('[]'))

Re: Whereclause in geoprocessing tool *select where item in list.

DanPatterson_Retired — Sun, 12 Dec 2021 06:34:43 GMT

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

Re: Whereclause in geoprocessing tool *select where item in list.

curtvprice — Sun, 12 Dec 2021 06:34:46 GMT

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])

Re: Whereclause in geoprocessing tool *select where item in list.

NoahHuntington — Sun, 12 Dec 2021 06:34:49 GMT

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"