Referencing Selection in SQL query (where clause) with ArcPy?

Question

I keep raising errors with my where_clause in the 9th line of this script. FID is an Object ID field and I want the cursor to select whatever row it is currently pointing at. I don't know how to do this with proper syntax - any ideas? I am running this on ArcGIS Desktop 10.3.

This is the error:

Runtime error Traceback (most recent call last): File "", line 27, in File "c:\program files (x86)\arcgis\desktop10.3\arcpy\arcpy\management.py", line 7211, in SelectLayerByAttribute raise e ExecuteError: ERROR 000358: Invalid expression Failed to execute (SelectLayerByAttribute).

#Make a layer out of the the original FC
arcpy.MakeFeatureLayer_management(fc, 'Sales_lyr')
#Use an update cursor when there is a value you need to change in the attribute table
with arcpy.da.UpdateCursor('Sales_lyr', ["BOROUGH", "FID", "COUNT"]) as cursor:
 #For every row in the cursor, do the following
 for row in cursor:
 #Select the current row in the UpdateCursor
 arcpy.SelectLayerByAttribute_management('Sales_lyr', "NEW_SELECTION", "FID IN(" + str(row[1]) + ")")

• Richard Fairhurst: Several things are wrong with the last line where clause. The most important thing is that you have the quotes around the wrong parts of the expression. There are also missing parentheses and concatenation operators. Assuming you want quote delimiters for the FID field name, the quotes need to be '"FID" = ' + str(row[1]). So the full expression would be: arcpy.SelectLayerByAttribute_management('Sales_lyr', "NEW_SELECTION", '"FID" = ' + str(row[1]))
• Vince: The way to debug selection expression construction from string assembly is to comment out the actual query and use print statements until the expression at least looks valid. If you don't know what a valid expression looks like, then you'll need to tackle that first, without the looping to complicate matters.
• Vince: The error text in your question reports "invalid expression". If that isn't the error, then you'll need to edit the question to reflect an error message related with locking issues.
• Richard Fairhurst: My attempt is still much closer than yours. Probably your database does not actually use quote delimiters for the field name (I only assumed it did because of the way you built the expression). If the delimiters are eliminated it may work (or you need to tell me the exact feature class database you are querying to get proper SQL, since I have no idea what type of data source fc or Sales_lyr point to). So try this: arcpy.SelectLayerByAttribute_management('Sales_lyr', "NEW_SELECTION", 'FID = ' + str(row[1]))
• Vince: Part of the problem may be that you're not actually looking for an SQL expression --shapefiles don't support SQL. Please edit the question to include the version of ArcGIS and the data storage format.

Answer 1

The problem here is you are converted the FID (objectID field type) value to string which should be a numeric value within the expression, try this:

with arcpy.da.UpdateCursor('Sales_lyr', ["BOROUGH", "FID", "COUNT"]) as cursor:
 for row in cursor:
 expression = '"FID" = {}'.format(row[1])
 arcpy.SelectLayerByAttribute_management('Sales_lyr', "NEW_SELECTION", expression)