Using SelectLayerByAttribute and Where Clause in ArcPy?

Question

I'm trying to select the minimum value from a field to select and export that one feature to a temp shp so I can copy the field data from the point to a line. So far I have this:

import arcpy
from arcpy import env
# Set up the environment
env.workspace = r"C:\Users\BrettM\Desktop\OD_Python_Testing"
env.overwriteOutput = True
# Get Min
fcPoints = r"\OD_Shapefiles\One_Road_Points.shp"
temp = r"\OD_Shapefiles\intersectTempPoint.shp"
arcpy.Statistics_analysis(fcPoints, r"C:\Users\BrettM\Desktop\OD_Python_Testing\table",[["NEAR_DIST", "MIN"]])
nearIntersect = r"C:\Users\BrettM\Desktop\OD_Python_Testing\table"
minDistCursor = arcpy.da.SearchCursor("table", ["MIN_NEAR_DIST"])
for row in minDistCursor:
 intersectDist = row[0]
print intersectDist
arcpy.MakeFeatureLayer_management(fcPoints, "fcPoints_Lyr")
print "one"
arcpy.SelectLayerByAttribute_management("fcPoints_Lyr", "NEW_SELECTION", ' "NEAR_DIST" = intersectDist ')
print "two"
arcpy.CopyFeatures_management(fcPoints_Lyr, "\OD_Scratch\\temp")
print "three"
del row
del minDistCursor

All those print statements are just to see where I get in the script. I get to print one. I can't figure out how to get the where clause to work for the selectlayerbyattribute. I keep getting an invalid expression error. I may have the copyfeatures wrong as well but I haven't gotten to that part yet.

The overall goal is to cycle through a line file, select points that intersect that line and copy the data from the point that is nearest to a centerline to the line. I'm using near to get the distance from the centerline for all the points and will work on adding the rest once I get this part to work.

There may be a much easier way to go about this but I'm very new to Python so I'm learning on the fly.

• Brett: Thanks Ken, that fixed that problem. Now I get to "two", now to figure out how to get to "three"! When I have more time I'll have to look into the why of needing it that way.
• Brett: And all the way to "three"! Now for the next part, thanks for the help Ken.
• Ken: Hey Brett, I added an explanation about the formatting of the where clause to the answer.

Answer 1

I think the error is in the where clause of the SelectByAttribute tool.

You can try: "\"NEAR_DIST\" = {0}".format(intersectDist) in the where clause part in the SeletByAttributeTool.

The field name needs to be in quotes but the value for intersectDist doesn't since it's a number. The entire statement also needs to be in quotes. To make sure Python doesn't terminate the string at the second and third quotes, you need to use \" to tell Python to treat that quote as part of the string. To avoid using a lot of " and + to string togehter the where clause, you can use the .format(variable_or_value) function to substitute in a number where the {} is. If you had multiple values you want to substitute in, you can have many {} and many values. For example:

"{0} {1}".format('Hi', 'Brett')

will return the string "Hi Brett".

The numbers in the {} are necessary for Python 2.6.x. If you are on Python 2.7.x, you don't need the numbers.

• mr.adam: good explanation, and just to point out that if you use the index numbers in the string formatting, you can reuse the arguments: "{0} {1} {0}".format('Hi', 'Brett') returns "Hi Brett Hi".
• Brett: Cool, thanks for the explanation. I've got 2 books and been all over the internet and you just explained what I was trying to find in a few sentences. mr.adam too.