Use Python to Select by Attributes in attribute table
We have scenarios where we want to select rows in a FC in the attribute table, but there isn't a way to run the query using the Select by Attributes window — due to limitations in SQL / SDE.ST_GEOEMTRY functions.
For example: Select features that have true curves. While it's not possible to make that selection via SQL, it's relatively easy to find the records via ArcPy using the hasCurves Geometry property.
Could ESRI consider giving us a way to Select by Attributes using Python — via functionality right in the Attribute Table window?
Something kind of like this?
Felt like solving a challenge so here's a working tool. I can't attach the full tool so here's the components:
Parameters:
Execution:
import arcpy
import ast
def parseSelectionType(s):
return s.strip().upper().replace(" ", "_")
def checkFunc(funcText):
try:
ast.parse(funcText)
return True, None
except SyntaxError as e:
return False, "Invalid syntax in Python function, line {}".format(e.lineno - 1)
def buildFuncText(funcBody, fieldNames):
FUNC_NAME = "_select_func"
signature = "def {}(".format(FUNC_NAME)
if len(fieldNames):
signature += ", ".join(f.lower() for f in fieldNames)
signature += "):\n\t"
indentFuncBody = funcBody.replace("\r\n", "\n").replace("\n","\n\t")
return signature + indentFuncBody, FUNC_NAME
def main(layer, fieldNames, funcBody, selectionType="NEW_SELECTION", isInvertSelection=False):
selectFuncText, selectFuncName = buildFuncText(funcBody, fieldNames)
isValid, error = checkFunc(selectFuncText)
if not isValid:
arcpy.AddError(error)
raise SystemExit(1)
namespace = {}
exec(selectFuncText, namespace)
selectFunc = namespace[selectFuncName]
desc = arcpy.da.Describe(layer)
oidFieldName = desc["OIDFieldName"]
isOIDInFunc = True
if oidFieldName not in fieldNames:
fieldNames = ["OID@"] + fieldNames
isOIDInFunc = False
shapeFieldName = desc["shapeFieldName"]
if shapeFieldName in fieldNames:
fieldNames = [f if f != shapeFieldName else "SHAPE@" for f in fieldNames]
selectOIDs = []
with arcpy.da.SearchCursor(layer, fieldNames) as search:
for row in search:
oid = row[0]
params = row if isOIDInFunc else row[1:]
if selectFunc(*params):
selectOIDs.append(oid)
sql = "{} IN ({})".format(oidFieldName, ",".join(str(o) for o in selectOIDs))
arcpy.management.SelectLayerByAttribute(layer, selectionType, sql, isInvertSelection)
if __name__ == "__main__":
layer = arcpy.GetParameterAsText(0)
selectionType = parseSelectionType(arcpy.GetParameterAsText(1)) if arcpy.GetParameterAsText(1) else "NEW_SELECTION"
fields = [f.value for f in arcpy.GetParameter(2)]
funcBody = "\n".join(s for s in arcpy.GetParameter(3))
isInvertSelection = arcpy.GetParameter(4)
main(layer, fields, funcBody, selectionType="NEW_SELECTION", isInvertSelection=False)
Validation:
import ast
def checkFunc(funcText):
try:
ast.parse(funcText)
return True, None
except SyntaxError as e:
return False, "Invalid syntax in Python function, line {}".format(e.lineno - 1)
def buildFuncText(funcBody, fieldNames):
FUNC_NAME = "_select_func"
signature = "def {}(".format(FUNC_NAME)
if len(fieldNames):
signature += ", ".join(f.lower() for f in fieldNames)
signature += "):\n\t"
indentFuncBody = funcBody.replace("\r\n", "\n").replace("\n","\n\t")
return signature + indentFuncBody, FUNC_NAME
class ToolValidator:
# Class to add custom behavior and properties to the tool and tool parameters.
def __init__(self):
# set self.params for use in other function
self.params = arcpy.GetParameterInfo()
def initializeParameters(self):
# Customize parameter properties.
# This gets called when the tool is opened.
return
def updateParameters(self):
# Modify parameter values and properties.
# This gets called each time a parameter is modified, before
# standard validation.
return
def updateMessages(self):
# Customize messages for the parameters.
# This gets called after standard validation.
fields = self.params[2]
funcBody = self.params[3]
if fields.value and funcBody.value:
fieldNames = [f.value for f in fields.values]
funcBodyText = "\n".join(funcBody.values)
funcText = buildFuncText(funcBodyText, fieldNames)[0]
isValid, error = checkFunc(funcText)
if not isValid:
funcBody.setErrorMessage(error)
return
# def isLicensed(self):
# # set tool isLicensed.
# return True
The Python Function Body parameter is a bit of a hack, it uses multiple string params in lieu of a proper text box (ESRI pls). I also didn't implement parameter parsing like the Calculate Field tool does so your function parameters are just the field names in lower case (e.g. the field "ParcelID" is accessed as "parcelid"). It'll also convert the shape field to python geometry objects just like the "SHAPE@" cursor token so you can do shape.firstPoint.X and such. There's probably some bugs lurking in here and using eval to run arbitrary input can cause issues but overall this'll get you what you need.