Selecting Layer by Attribute using SQL and variable in ArcPy

Question

I've had a good look around the site and haven't been able able to find any 'select by attribute' questions regarding string variables and how to integrate them into an SQL statement. In my particular case I have used a search cursor to create a variable but cannot find a way to use that variable in the select by attributes tool. Below is an example of one of the many ways I have tried to select the variable.

The variable I've mentioned (point_id) is the string EDRN_044851.

rows = arcpy.SearchCursor("feature_layer11")
for row in rows:
    point_id = row.getValue("DRN_update")

arcpy.MakeFeatureLayer_management("EDRN_NODE", "feature_layer15")
arcpy.SelectLayerByAttribute_management ("feature_layer15", "NEW_SELECTION", '\"LCC_DRN_ID\" = {0}'.format(point_id))

desc1 = arcpy.Describe("feature_layer15")
shapefieldname1 = desc1.ShapeFieldName
rows = arcpy.SearchCursor("feature_layer15")
for row in rows:
    feat = row.getValue(shapefieldname1)
    pnt = feat.getPart()
    pnt_x = pnt.X
    pnt_y = pnt.Y

Answer 1

It always helps to include the error message you're getting with the script, so I'm guessing a bit, but the correct format for your SQL where clause is probably

'"LCC_DRN_ID" = \'{0}\''.format(point_id)

Note that you don't have to escape the double quotes with the slash because you're using single quotes for the overall SQL string, but you do need to escape any inner single quotes, which is what I did around {0}.

Also, the correct field delimiter is not always double quotes - it depends on the data source. If you're using a personal geodatabase for example the field delimiter is square brackets [LCC_DRN_ID]. You can use the AddFieldDelimiter tool to add them in dynamically, if you're not sure of your datasource ahead of time http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//000v0000004n000000

Answer 2

You may try placing single quotes around the {0} in your statement. Example from ESRI:

" \"CITY_NAME\" = 'Chicago' "

In your case, try this (haven't validated it but worth a shot, I switched it to double quotes and escaped the internal double quotes, then added the string into single quotes):

arcpy.SelectLayerByAttribute_management ("feature_layer15", "NEW_SELECTION", **"\"LCC_DRN_ID\" = '{0}'"**.format(point_id))

Source: http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//001700000071000000