[GIS] Formatting SQL expression for where clause in Search layer by attribute with ArcPy

arcpyselect-by-attributesql

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. 

#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[0]
            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

Best Answer

If you need to insert a variable inside of a string, the easiest (and preferred way) is to use str.format().

x,y,z = 1,2,3
print "{0} + {1} = {2}".format(x,y,z)

The result is the string "1 + 2 = 3". The brackets (and numbers inside of them) are used to refer to the parameter values passed to format().

So, in your instance, you'll want something like:

"\"DATE\" = date '{0}'".format(currentRaster)
Related Question