topic Best practices for writing SQL selection expressions in Python in Python Questions

Select By Attributes using a variable in the Where clause

KirstenBarrett — Tue, 04 Jan 2011 23:01:58 GMT

I am trying to iterate through each date in a shapefile using a list of dates. I am having trouble phrasing my Where clause to select only one date. If I use the following Python code, all dates are selected in each iteration:

datelst = ['2002-06-06','2002-06-07','2002-06-08']

arcpy.MakeFeatureLayer_management ("E:/hotspots.shp", "hotspots")

for dat in datelst:
arcpy.SelectLayerByAttribute_management ("hotspots", "NEW_SELECTION", ("'date' = 'dat'"))

I have tried rephrasing the Where clause in many different ways, but the result is that all records are selected, no records are selected, or an error message is returned. The query is successful if I use the string itself (e.g.

arcpy.SelectLayerByAttribute_management ("hotspots", "NEW_SELECTION", "date = '2002-06-06'")

but then it is not possible to iterate through all of the dates in the file.

Re: Select By Attributes using a variable in the Where clause

JasonScheirer — Tue, 04 Jan 2011 23:16:54 GMT

You need to use the string operator as such:

arcpy.SelectLayerByAttribute_management ("hotspots", "NEW_SELECTION", ("'date' = '" + dat + "'"))

You may be able to select the full set all at once as well:

datelst = ['2002-06-06','2002-06-07','2002-06-08']
dateliststring = ",".join("'%s'" % dat for dat in datelst)

arcpy.SelectLayerByAttribute_management ("hotspots", "NEW_SELECTION", ("'date' in (%s)" % dateliststring))

Re: Select By Attributes using a variable in the Where clause

KirstenBarrett — Wed, 05 Jan 2011 16:09:50 GMT

Thanks for your reply. When I use the code you suggested

arcpy.SelectLayerByAttribute_management ("hotspots", "NEW_SELECTION", ("'date' = '" + dat + "'"))

it produces the same result, i.e., the entire dataset is selected, not just the date specified by 'dat'.

Re: Select By Attributes using a variable in the Where clause

ChrisMathers — Wed, 05 Jan 2011 16:29:42 GMT

Why is your where clause in parenths? That could be what is messing you up. I dont know why it would but it might be.

Re: Select By Attributes using a variable in the Where clause

KirstenBarrett — Wed, 05 Jan 2011 16:40:55 GMT

The Tool Help suggests that you put the clause in parentheses. I've tried it without the parentheses:

arcpy.SelectLayerByAttribute_management ("hotspots", "NEW_SELECTION", "'date' = 'dat'")

and I get the same result (all dates selected).

Re: Select By Attributes using a variable in the Where clause

ChrisMathers — Sat, 11 Dec 2021 22:52:35 GMT

Here is how I do this in my code:

for dat in datelist:
    arcpy.SelectLayerByAttribute_management ("hotspots", "NEW_SELECTION", "'date' = '%s'"%dat)

Re: Select By Attributes using a variable in the Where clause

KirstenBarrett — Wed, 05 Jan 2011 17:57:12 GMT

Thanks, but using your code (clm42) it is still selecting all records.

Re: Select By Attributes using a variable in the Where clause

ZoeZaloudek — Sat, 11 Dec 2021 22:52:37 GMT

I'm using 9.3.1, so I don't know if the arcpy module would have a problem with this suggestion... If it was me, I would just calculate the where clause on a separate line, above the select tool:

for dat in datelist:
    whereclause = "'date' = " + dat
    arcpy.SelectLayerByAttribute_management ("hotspots", "NEW_SELECTION", whereclause)

I've run into where clause issues involving quotes plenty of times. If whereclause = "'date' = " + dat doesn't work, I would next try something like whereclause = "date = " + dat or whereclause = "'date' = '" + dat + "'"

Re: Select By Attributes using a variable in the Where clause

KirstenBarrett — Thu, 06 Jan 2011 17:21:00 GMT

Thanks for the suggestion. Only the last where clause example 'works' in that it selects records, but similar to everything else I've tried, it selects all records in the dataset (including the dates that were not specified in the where clause).

Re: Select By Attributes using a variable in the Where clause

KirstenBarrett — Thu, 06 Jan 2011 22:55:06 GMT

Ok, someone smarter than I am figured it out. Here is the correct where clause:

for dat in datelst:
arcpy.SelectLayerByAttribute_management ("hotspots", "NEW_SELECTION", "\"date\" = " + "'" + dat + "'")

Re: Select By Attributes using a variable in the Where clause

ChrisSnyder — Sat, 11 Dec 2021 22:52:40 GMT

You should also just be able to use this:

for dat in datelst:
   arcpy.SelectLayerByAttribute_management ("hotspots", "NEW_SELECTION", "date = '" + dat + "'")

In arcpy (and also when using gp = arcgisscripting.create(9.3)), you shouldn't need to use the "\" things in SQL expresions anymore.

I think your original problem was that you had single quotes ('MY_FIELD') arount your field name, and you don't need to.

Re: Select By Attributes using a variable in the Where clause

sofoo — Thu, 20 Jan 2011 16:31:12 GMT

Thank you for asking the question! I have been trying all kinds of different combinations for this problem as well. ESRI needs to re-write their help so as not to confuse the issue further. Adding parentheses and telling people they need double-quotes around field names to query shapefiles are both misleading pieces of advice. Neither is true in this situation!!!

Re: Select By Attributes using a variable in the Where clause

EvaJenkins — Mon, 05 Mar 2012 19:15:34 GMT

I'm looking at doing something very very similar, the only difference is that my list contains numeric values and I want to compare a field to each value in the unique list. However, I'm having trouble linking the field to the current value in the list using the MakeFeatureLayer tool. I tried doing it as per this thread, but it appears that I need another way to compare the current value other than using it as a string (since survey_month is a double field). Any help with linking the numerical value within the loop to my feature class field would be greatly appreciated!

valueList = []
rows = arcpy.SearchCursor(inFC)
for row in rows:
aVal = row.getValue(inField)
if aVal not in valueList:
    valueList.append(aVal)

del row, rows
print valueList

for value in valueList:
#make feature layer where survey_month field equals the current value in the unique list

lyrbase = "MonthLyr"
val = str(value)
lyrname = lyrbase + val

where = "survey_month = value"
##Also tried where = "survey_month = " + val
arcpy.MakeFeatureLayer_management(inFC, lyrname, where)
arcpy.RefreshTOC()

#update symbology based on layer in mxd
for lyr in arcpy.mapping.ListLayers(mxd, lyrname,df):
    arcpy.mapping.UpdateLayer(df, lyrname, sourcelayer, True)

#change the text element to read the month of the current unique value
for elm in arcpy.mapping.ListLayoutElements(mxd, "TEXT_ELEMENT", "Month"):
    elm.text = "Month: " + val

#export to PDF with settings
outfile = outloc + val
arcpy.mapping.ExportToPDF(mxd, outfile, data_frame="PAGE_LAYOUT", resolution=300, image_quality="BEST", colorspace="CMYK", image_compression="LZW", convert_markers="True", embed_fonts="True")

Re: Select By Attributes using a variable in the Where clause

DanaDiotte1 — Sat, 11 Dec 2021 22:52:43 GMT

Hello everyone,

Very helpful thread. Thanks. One thing I changed in my for loop was to use "ADD_TO_SELECTION" rather than use the "NEW_SELECTION". In my case the for loop would overwrite each previous selection which of course is not the intended result. The "ADD_TO_SELECTION" parameter acts like "NEW_SELECTION" for the first iteration.

Here is my code for future reference to anyone else having similar problems. I have three seperate lists because I use them elsewhere in my function and my table contains 48 records. Of the 48 records only 37 are matched in the table as per my criteria. The idea behind this chunk of code within my function is to create a temporary or one time lookup table based on an area of interest. My goal was to select attributes I needed and delete the rows I did not need to complete my table.

    sList = ['BG','PP','IDFxh1','IDFxh1a','IDFxh2','IDFxh2a']
    mLIST = ['IDFdk1','IDFdk1a','IDFdk2','IDFdk3','IDFunk','MS']
    dList = ['ESSF','ICH','CWH']
    mergeList = sList+mLIST+dList

    # This is using python list comprehension to add the '%' wildcard to each item in my list
    #    because the attributes BG, PP, MS, ESSF, ICH, CWH contain up to 4 more characters
    mergeList = [x[:1]=='%' and x or x+'%' for x in mergeList] 
    print "The merged lists look like this:\n " + str(mergeList)

    arcpy.MakeTableView_management("becFreqTbl","tempBecTbl")

    for list in mergeList:
        print 'iterating through list item:', list
        where_clause = "MAP_LABEL LIKE '"+list+"'"
        arcpy.SelectLayerByAttribute_management("tempBecTbl","ADD_TO_SELECTION",where_clause)

    newSel = int(arcpy.GetCount_management("tempBecTbl").getOutput(0))
    print "The new selection yieled "+str(newSel)+" fields selected"

    arcpy.SelectLayerByAttribute_management("tempBecTbl","SWITCH_SELECTION")
    switchSel = int(arcpy.GetCount_management("tempBecTbl").getOutput(0))
    print "The switch selection yielded "+str(switchSel)+" fields selected"
    
    arcpy.DeleteRows_management("tempBecTbl")
    print "Deleted "+str(switchSel)+" rows from table that did not match the list criteria"

The output looks like this:

The merged lists look like this:
['BG%', 'PP%', 'IDFxh1%', 'IDFxh1a%', 'IDFxh2%', 'IDFxh2a%', 'IDFdk1%', 'IDFdk1a%', 'IDFdk2%', 'IDFdk3%', 'IDFunk%', 'MS%', 'ESSF%', 'ICH%', 'CWH%']
iterating through list item: BG%
iterating through list item: PP%
iterating through list item: IDFxh1%
iterating through list item: IDFxh1a%
iterating through list item: IDFxh2%
iterating through list item: IDFxh2a%
iterating through list item: IDFdk1%
iterating through list item: IDFdk1a%
iterating through list item: IDFdk2%
iterating through list item: IDFdk3%
iterating through list item: IDFunk%
iterating through list item: MS%
iterating through list item: ESSF%
iterating through list item: ICH%
iterating through list item: CWH%
The new selection yieled 37 fields selected
The switch selection yielded 11 fields selected
Deleted 11 rows from table that did not match the list criteria

Best practices for writing SQL selection expressions in Python

curtvprice — Sat, 11 Dec 2021 22:52:45 GMT

Here is the correct where clause:

for dat in datelst:
  arcpy.SelectLayerByAttribute_management ("hotspots", "NEW_SELECTION", "\"date\" = " + "'" + dat + "'")

Python string substitution makes this kind of thing a LOT easier to write and and debug:

whereExpr =  "\"date\" = \'%s\'" % dat  # I use outside double-quotes always for stylistic reasons
whereExpr =  '"date" = \'%s\'' % dat    # but this is equivalent, using ' to preserve " inside the string
arcpy.SelectLayerByAttribute_management ("hotspots", "NEW_SELECTION",whereExpr)

The "new" way to do this in Python is to use the .format method.

whereExpr = "\"date\" = \'{0}\'".format(dat)

I prefer the "old" "% syntax" way myself for simple expression creation. Both are still fully supported.

In arcpy (and also when using gp = arcgisscripting.create(9.3)), you shouldn't need to use the "\" things in SQL expresions anymore

I think it's still good practice to double quote field names. If you have field names that cause SQL heartburn, the quotes will protect your field names from being parsed as SQL code and breaking your expression.

Note when you're working with personal geodatabases, you need to use special field name delimeters ([]). ArcPy has a handy AddFieldDelimiters method to make your expressions .mdb-safe ("wks" below is the workspace where the table view you are querying against lives):

whereExpr = "{0} = \'{1}\'".format(arcpy.AddFieldDelimiters(datefield,wks),dat)

Depending on the wks type, you get from this something like:

gdb, shape: "DATE" = '2012-01-02'
mdb: [DATE] = '2012-01-02'

One more thing, since you were mentioning dates: be wary of date fields, they have all kinds of gotchas.

More details:
Arc 10 help: SQL reference for query expressions used in ArcGIS

Re: Select By Attributes using a variable in the Where clause

Sean_Perks — Mon, 10 Apr 2023 23:48:29 GMT

This is so simple and effective I can't believe I didn't think to use python to make the variable to just pass as the whole where_clause. I've been working forever on this and finally saw your post!