Select By Attributes using a variable in the Where clause

18844
15
01-04-2011 03:01 PM
KirstenBarrett
New Contributor II
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.
Tags (2)
0 Kudos
15 Replies
JasonScheirer
Occasional Contributor III
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))
0 Kudos
KirstenBarrett
New Contributor II
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'.
0 Kudos
ChrisMathers
Occasional Contributor III
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.
0 Kudos
KirstenBarrett
New Contributor II
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).
ChrisMathers
Occasional Contributor III
Here is how I do this in my code:

for dat in datelist:
    arcpy.SelectLayerByAttribute_management ("hotspots", "NEW_SELECTION", "'date' = '%s'"%dat)
0 Kudos
KirstenBarrett
New Contributor II
Thanks, but using your code (clm42) it is still selecting all records.
0 Kudos
ZoeZaloudek
Occasional Contributor
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 + "'"
Sean_Perks
New Contributor II

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!

0 Kudos
KirstenBarrett
New Contributor II
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).
0 Kudos