I am trying to pass a variable into a where clause in the "Search Layer by Attribute" arcpy function. I cannot seem to figure out the correct format for the SQL expression so that the function reads the value of the given variable, and doesn't just read the variable name as a string.
The variable I am trying to pass is called "currentRaster" and is a list of dates in the 'YYYY-MM-DD 00:00:00' format.
The expression I am having trouble with is: whereClause = """"DATE" = date 'currentRaster'"""
If I run the python script and tell it to "print whereClause", the result is: whereClause = """"DATE" = date 'currentRaster'"""
instead of, for example: whereClause = """"DATE" = date '1998-01-31 00:00:00'"""
If I substitute one of the dates for the variable name, e.g. 1998-01-31 00:00:00, and run the script, the expression works.
Does anyone know the proper way to format this SQL expression so that it reads the variable as the date it contains? The rest of the script it pasted below.
Thank you! Liza
#Interpolate points from temporal ArcGIS rasters #import modules import arcpy from arcpy import env import datetime from datetime import timedelta #environment settings env.workspace = "Z:\\sharks\\Models\\Summer2013\\Data_From_Jason\\compiled Chl" arcpy.env.overwriteOutput = True #enable MGET modules and enable verbose logging import GeoEco.Logging from GeoEco.Logging import Logger Logger.Initialize(activateArcGISLogging=True) import GeoEco.SpatialAnalysis.Interpolation from GeoEco.SpatialAnalysis.Interpolation import Interpolator #set up variables rasters = arcpy.ListRasters() points = "Z:\\sharks\\Models\\Summer2013\\summer2013.gdb\\bll_wgs84" fields = ["Chla_Daily"] method = "Automatic" #define function that extracts the gregorian date from the raster filename (which is in julian days) def get_date(fname, nbeg, nend): yr = int(fname[nbeg:nbeg+4]) j = int(fname[nbeg+4:nend]) beg = datetime.datetime(yr,1, 1)+ timedelta(days=(j-1)) return beg #create feature layer for select layer by attributes analysis arcpy.MakeFeatureLayer_management(points,"points_lyr") #set up loop to step through all rasters for i in rasters: currentRaster = get_date(fname=i, nbeg=16, nend=23) #set up cursor to step through all records in attribute table with arcpy.da.SearchCursor("points_lyr", ("Date",)) as cursor: for row in sorted(cursor): value = row print value print currentRaster #if this record's date matches the date of the current raster, then select that record and perform interpolation if value == currentRaster: whereClause = """"DATE" = date 'currentRaster'""" print whereClause arcpy.SelectLayerByAttribute_management("points_lyr", "NEW_SELECTION",whereClause) Interpolator.InterpolateArcGISRasterValuesAtPoints([i], "points_lyr", fields, method,None, None,False,None,None,None,None) else: print "no" arcpy.Delete_management("points_lyr") del row, cursor