Select By Attributes using a variable in the Where clause

[KirstenBarrett]

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.

[JasonScheirer]

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

[KirstenBarrett]

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'.

[ChrisMathers]

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.

[KirstenBarrett]

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]

Here is how I do this in my code:

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

[KirstenBarrett]

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

[ZoeZaloudek]

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]

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!

[KirstenBarrett]

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).