Writing MakeFeatureLayer_management Where Clause With Multiple Mixed Values using ArcPy

Question

I am trying to select certain points in a shape file using three different fields. Two fields don't need to vary, but the third one (the analog_years variable below) does. I want to be able to select different values and numbers of years for this variable.

analog_years = [1950, 1993, 2010]

arcpy.MakeFeatureLayer_management(hurr_p_data_01, "lfalls_01",'"BASIN" = \'NA\'' " AND " '"USA_RECORD" = \'L\'')

How do I also include the analog_years variable in the where clause or is there a way to write a where clause variable for all three and just reference that instead?

Answer 1

@AnnaForrest's answer is almost there.

You need to quote properly in SQL, have a look at my answer Generating a SQL statement from a text file to make a selection for the syntax, whereclause = "NAME IN ('{}')".format("','".join(list_of_strings))... look closely at the quotes, python doesn't care if you use single or double as long as they're matched, SQL does, it must be a single quote for strings in SQL so in python start/end with a double when creating definition queries and then you can use as many single quotes in that string as you like with needing to escape them in the form \'.

Although many examples of definition queries have quotes or brackets around field names the truth is you really don't need them for a selection query, just use the field names and the query will work with shapefiles, personal/file/enterprise geodatabases and tables - that saves a lot of quote-in-quote-out guessing (is this one for SQL or for python?) to having both ends double and then every single inside is for SQL!

As analog_years is already a list but numbers and not strings the join function will have a dummy spit which is why Anna has tried list comprehension str(x) for x in analog_years but is missing the beginning and terminating list brackets, that should be [str(x) for x in analog_years] as a list comprehension; I generally avoid using comprehension for new users as it's really confusing what's actually happening, for a new user try

YearsAsStrings = [] # an empty list
for ThisYear in analog_years:
  YearsAsStrings.append(str(ThisYear)) # add the string of the year to the list
analog_years = YearsAsStrings          # copy back to your original variable as strings

To select all those years:

# as list comprehension
analog_years = [1950, 1993, 2010]    
QF = "BASIN = 'NA' AND USA_RECORD = 'L' AND YEAR_FIELD  IN ('{}')".format("','".join([str(x) for x in analog_years]))    
arcpy.MakeFeatureLayer_management(hurr_p_data_01, "lfalls_01",QF)

-or-

# as list building - easier to see what's happening here.
analog_years = [1950, 1993, 2010]

YearsAsStrings = [] # an empty list
for ThisYear in analog_years:
    YearsAsStrings.append(str(ThisYear)) # add the string of the year to the list
analog_years = YearsAsStrings          # copy back to your original variable as strings
QF = "BASIN = 'NA' AND USA_RECORD = 'L' AND YEAR_FIELD IN ('{}')".format("','".join(analog_years))

arcpy.MakeFeatureLayer_management(hurr_p_data_01, "lfalls_01",QF)

-or to loop through each year, selecting only that year-

for ThisYear in analog_years:
    QF = "BASIN = 'NA' AND USA_RECORD = 'L' AND YEAR_FIELD = {}".format(ThisYear)
    arcpy.MakeFeatureLayer_management(hurr_p_data_01, "lfalls_01",QF)

Note: if your date field isn't a string then don't quote the values for SQL, the IN operator would work like "YEAR_FIELD IN (1950, 1993, 2010)"

Answer 2

You can concatenate your values into a whereclause like this:

whereclause = "FIELD_NAME IN (" +  ",".join([str(x) for x in analog_years]) + ")" 

If you're working with a string field you'll need to put single quotes around the values as well.

Answer 3

I like to use AddFieldDelimiters and format.

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

import arcpy
fc = r'C:\data.gdb\fc'
analog_years = [1950, 1993, 2010]
sql = """{0}='NA' AND {1}='L' AND {2} IN{3}""".format(arcpy.AddFieldDelimiters(datasource=fc, field='BASIN'), arcpy.AddFieldDelimiters(fc, 'USA_RECORD'), arcpy.AddFieldDelimiters(fc, 'YEAR_FIELD'), tuple(analog_years))
arcpy.MakeFeatureLayer_management(in_features=fc, out_layer='somelyr', where_clause=sql)