Using "IN" within where clause of arcpy.MakeFeatureLayer_management

Question

I am using ArcGIS Pro 2.4.0, with Python 3.6.8. I found the following similar posts but could not figure out how to apply them: Using Python Search Cursor results to Select Features by Attributes and Selecting rows in a layer using arcpy.SearchCursor

I wish to make a Feature Layer that is a subset of a Feature Class. The subset should include any records that meet one of nine different scenarios that I have for when a record should be flagged as a problem. If I were to create this layer using a where statement in arcpy.MakeFeatureLayer_management, I believe it would be illegible to someone else as it would have many compound OR and AND statements. (Method 3 below is a reproducible, truncated example.)

Instead, I'd like to first create a list of all unique id's of those features meeting one of the nine criteria as they pass through an arcpy.da.SearchCursor. Then I was planning on using a where clause within MakeFeatureLayer, selecting only those features whose unique ID is also within the compiled list. (Methods 1 and 2 below)

My problem is that in method 1, I can't figure out how to use an "in" SQL statement in my where clause. I receive the error: ERROR 000358: Invalid expression. Failed to execute (MakeFeatureLayer). (This is generated with the second to last line of code below).

Here's a reproducible example, where I use the shapefile within cb_2018_us_cd116_20m.zip (the 116 congressional districts) found on the Census website.

Method 1: (not working)

First run query via SearchCursor on data and save records matching criteria to list, then create layer based on IDs matching those in list via MakeFeatureLayer.

import arcpy
arcpy.env.workspace = '~Downloads/Reproducible_Example'
district_shapefile = r'~Downloads\Reproducible_Example\cb_2018_us_cd116_20m.shp'
# Create empty list to which GEOID of records of interest will be added
interest_list = []
# Run a search cursor to iterate through records and add GEOID that match criteria to list of records of interest.
in_table = district_shapefile
input_table_fields = ['GEOID', 'ALAND', 'AWATER']
with arcpy.da.SearchCursor(in_table, input_table_fields) as cursor:
 for row in cursor:
 # Scenario 1: ALNAD > 66914406427 and AWATER > 11321049519
 if row[1] > 66914406427 and row[2] > 11321049519:
 interest_list.append(row[0])
 # Scenario 2: ALAND < 37598152 but exclude record where ALAND = 26316818 and AWATER = 7203018 
 if row[1] < 37598152 and not (row [1] == 26316818 and row[2] == 7203018 ):
 interest_list.append(row[0])
# Print resultant list showing three records matching criteria: ['2708', '0200', '3610'] 
print(interest_list)
# Subset feature class so that only contains those records whose GEOID is in interest_list.
expression = '"GEOID" in interest_list'
arcpy.MakeFeatureLayer_management(in_features = district_shapefile, out_layer = 'flagged_records', where_clause = expression)
arcpy.SaveToLayerFile_management(in_layer = 'flagged_records', out_layer = 'flagged_records_layer')

An alternative approach I tried is method 2 (below), however my output layer is not a subset and instead contains all records. (Although I should say, I do not actually want to create a new feature class. I want to create a layer so that any edits I make to correct the flagged records are updated to the original feature class, so this method I believe is not applicable.)

Method 2: (not working)

First run query via SearchCursor on data and save records matching criteria to list (code above), then iterate though those records in list and add those to a selection via a second SeachCursor (below):

for_loop_layer = arcpy.MakeFeatureLayer_management(in_features = district_shapefile)
for i in interest_list:
 in_table = for_loop_layer
 input_table_fields = ['GEOID']
 with arcpy.da.SearchCursor(in_table, input_table_fields) as cursor:
 for row in cursor:
 # For each record, if the field ID matches i, add record to selection
 if row[0] == i:
 print(i)
 arcpy.SelectLayerByAttribute_management(in_layer_or_view = for_loop_layer, selection_type = 'ADD_TO_SELECTION')
arcpy.CopyFeatures_management(for_loop_layer, 'for_loop_selections') 

I am able to create a subset output layer of flagged records with method 3 (below). However, my real scenarios are long and I am looking for an alternative method so as to avoid having all criteria combined in one unintelligible where clause.

Method 3: (working but not desired)

Run query directly with one long where clause.

arcpy.MakeFeatureLayer_management(in_features = district_shapefile, out_layer = 'interest_layer', where_clause= "(ALAND > 66914406427 and AWATER > 11321049519) OR (ALAND < 37598152 and not (ALAND = 26316818 and AWATER = 7203018 ))")
arcpy.SaveToLayerFile_management(in_layer = 'interest_layer', out_layer = 'interest_records_layer')

• FelixIP: Use str(tuple(list)) in your query.
• Vince: Your Method1 doesn't actually place the list in expression -- try expression = '"GEOID" in ({:s})'.format(','.join(interest_list))
• ENIAC-6: @Vince, thank you. I still get the same error though ERROR 000358: Invalid expression Failed to execute (MakeFeatureLayer).
• ENIAC-6: @FelixIP, thank you. I tried '"GEOID" in str(tuple(interest_list))' but still get the same error.
• ENIAC-6: @FelixIP this did work! field = "GEOID" expression = '"{}" in {}'.format(field, str(tuple(interest_list))) Though I'm not exactly certain that I understand why. It looks like the where clause can only search within a string and not a list?

Answer 1

@Vince's comment is pretty close, but the GEOID field is actually a string, so you need to explicitly include single quotes around each value in the list in the sql query

expression = '"GEOID" in ({:s})'.format(','.join(["'" + str(geoid) +"'" for geoid in interest_list]))

• NW_Photo_Laureate: Quick addendum. Gave up on this pursuit multiple times. Understood the protocol steps of 1) arcpy.AddFieldDelimiter to ensure proper delimeters 2) strings need single quotations vs. integers which don't. But THIS response from @Nick included the use of in rather than IN for multiple target values. And THAT was the trick for me. Unsure why SQL statements applied when selecting rows from the Attribute Table, or Def Queries on Feature Classes in the Table of Contents use IN whereas a where_clause passed to arcpy functions as kwargs require in (?).
• Nick: That's odd, I wonder if that might be a bug in arcpy. It looks like Arc uses various SQL dialects depending on the datasource, but I don't know of any dialects that would be case sensitive for keywords like IN/in