My python script in ArcMap has an initial dialog box with two inputs: REG_CODE (values are in a list and include 'AKR', 'IMR', 'NER', etc) and Selection_type which offers 'NEW_SELECTION', 'REMOVE_FROM_SELECTION', etc.
My code runs just fine unless I pick the value 'NER'. Then I get this error:
Traceback (most recent call last): File "C:\Temp\SelectRegionalCREnterpriseData.py", line 99, in selectRelatedRecords(originTable, destinationTable, primaryKeyField, foreignKeyField) File "C:\Temp\SelectRegionalCREnterpriseData.py", line 96, in selectRelatedRecords arcpy.SelectLayerByAttribute_management(destinationTable, selectType, whereClause) File "c:\program files (x86)\arcgis\desktop10.2\arcpy\arcpy\management.py", line 6688, in SelectLayerByAttribute raise e ExecuteError: ERROR 000358: Invalid expression Failed to execute (SelectLayerByAttribute).
I can verify that my layers and tables have a valid field called REG_CODE, that 'NER' does exist as an attribute value, and that there are no associated NULL values in the CR_ID field.
The script does two things... 1) goes through each layer, and selects features where REG_CODE = (say, NER) 2) Finds all records in CR_Catalog table where REG_CODE = and then selects the related records in the CR_Link table.
import arcpy, os, string
import arcpy.mapping as MAP
#Needed CR layers as well as Catalog and Link tables should be loaded into current mxd
mxd = MAP.MapDocument("CURRENT")
df = MAP.ListDataFrames(mxd)[0]
#set Reg_Code value for regional data into a sql expression
fld = arcpy.AddFieldDelimiters(arcpy.env.workspace, "REG_CODE")
sqlExp = fld + " = " + " '{0}' ".format(arcpy.GetParameterAsText(0))
arcpy.AddMessage(sqlExp)
#set selection type
selectType = arcpy.GetParameterAsText(1)
#Make lists of all feature layers and another list of all tables in dataframe
layers = MAP.ListLayers(mxd, "", df)
tables = MAP.ListTableViews(mxd, "", df)
#Set destinationTable (CR_Link) to empty string. This gets populated only if Link table is loaded into mxd.
# else, the select layer by attribute tool doesn't run
destinationTable = ""
originTable = ""
#used in generating list of related CR_IDs for match Catalog to Link table. Related by CR_ID field.
primaryKeyField = "CR_ID"
foreignKeyField = "CR_ID"
#These will be used to get records in Link that are related to Catalog where #Reg_Code = <input>
for table in tables:
descTable = arcpy.Describe(table)
tableNameList = (descTable.name).split(".")
tableName = tableNameList[-1]
if tableName == "CR_Catalog":
originTable = table
elif tableName == "CR_Link":
destinationTable = table
else:
pass
#Main code to select all records in Catalog with matching Reg_code and then getting related records in Link table
if not str(destinationTable) == "" and not str(originTable) == "":
def buildWhereClauseFromList(originTable, primaryKeyField, valueList):
# Add DBMS-specific field delimiters
fieldDelimited = arcpy.AddFieldDelimiters(arcpy.Describe(originTable).path, primaryKeyField)
# Determine field type
fieldType = arcpy.ListFields(originTable, primaryKeyField)[0].type
# Add single-quotes for string field values
if str(fieldType) == 'String':
valueList = ["'%s'" % value for value in valueList]
# Format WHERE clause in the form of an IN statement
whereClause = "%s IN(%s)" % (fieldDelimited, ', '.join(map(str, valueList)))
return whereClause
def selectRelatedRecords(originTable, destinationTable, primaryKeyField, foreignKeyField):
## Defines the record selection from the record selection of the OriginTable
## and applys it to the DestinationTable using a SQL WHERE clause built
## in the previous defintion"""
# Set the SearchCursor to look through the selection of the OriginTable
sourceIDs = set([row[0] for row in arcpy.da.SearchCursor(originTable, primaryKeyField, sqlExp)])
# Establishes the where clause used to select records from DestinationTable
whereClause = buildWhereClauseFromList(destinationTable, foreignKeyField, sourceIDs)
# Process: Select Layer By Attribute
arcpy.SelectLayerByAttribute_management(destinationTable, selectType, whereClause)
# Process: Select related records between OriginTable and DestinationTable
selectRelatedRecords(originTable, destinationTable, primaryKeyField, foreignKeyField)
del tables, tableName, tableNameList, primaryKeyField, foreignKeyField
del originTable, destinationTable
#If Link isn't in mxd, send a message
else:
arcpy.AddMessage("CR_Link and CR_Catalog tables must be added to dataframe in order to create selection in CR_Link")
for lyr in layers:
fieldList = arcpy.ListFields(lyr.dataSource, "REG_CODE")
if len(fieldList) == 1:
arcpy.SelectLayerByAttribute_management(lyr, selectType, sqlExp)
else:
pass
del lyr, layers,
del sqlExp, fld, mxd, df, fieldList
