1

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?

Improve this question

3 Answers 3

Reset to default
3

@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)"

Improve this answer
7
  • Thank you! I'm learning Python syntax but I don't know SQL at all. That helps a lot! Commented Nov 5, 2020 at 14:05
  • I've tried both the list comprehension and YearsAsStrings methods and I'm getting the same error: ERROR 000358: Invalid expression BASIN = 'NA' AND USA_RECORD = 'L' AND SEASON IN ('1950','1993','2010'). The field name for the years is SEASON, I updated your code with that name. Commented Nov 5, 2020 at 15:43
  • The years are short integers, which quotes do I drop in sql = "BASIN = 'NA' AND USA_RECORD = 'L' AND SEASON IN ('{}')".format("','".join([str(x) for x in analog_years]))? Commented Nov 5, 2020 at 17:29
  • Ah, yes - you're right. I pulled my example from some code I'd written and simplified to suit, but clearly missed that :) Will fix my answer so as not to cause confusion... Commented Nov 7, 2020 at 0:53
  • More pythonic way would be “,”.join(map(unicode, analog_years)) Commented Nov 7, 2020 at 5:09
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.

Improve this answer
3
  • Do I just declare whereclause like that and then tack it on to the end of the where clause section in my original statement? Or, do I need to add my other two fields to 'whereclause`? Commented Nov 5, 2020 at 0:59
  • You will need to modify the above to suit your specific use case. I prefer to consolidate all my criteria into a single whereclause variable, that I can print out to see its correct (and copy/paste into a select by attributes if needed to confirm results). Then I just pass the single whereclause to the MakeFeatureLayer function. Commented Nov 5, 2020 at 1:26
  • 1
    It's enough to str(tuple(list)) instead of bulky join. Commented Nov 5, 2020 at 2:05
0

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)
Improve this answer
0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.