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.
I'm losing it. Is NER a reserved word of some sort? Makes no sense...
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
-
does this help ? gis.stackexchange.com/questions/61499/…radouxju– radouxju2015年12月04日 19:43:03 +00:00Commented Dec 4, 2015 at 19:43
-
No. In that example, the sql expression was not written correctly. Mine works absolutely fine with any input I choose from a list other than 'NER'.MattS– MattS2015年12月04日 19:47:24 +00:00Commented Dec 4, 2015 at 19:47
-
Do you have 'NER' as an actual value?recurvata– recurvata2015年12月04日 19:52:00 +00:00Commented Dec 4, 2015 at 19:52
-
Yes. All of those values are domains in a geodatabase. 'NER' is one value and it is found in all layers and the Catalog table as well.MattS– MattS2015年12月04日 19:53:19 +00:00Commented Dec 4, 2015 at 19:53
-
For debugging purposes, it may help to print out the where clause before running it through SelectLayerByAttribute since you are building it dynamically. It may be an issue of the string being formatted incorrectly due to an unexpected value, but it's difficult to tell from this.Evil Genius– Evil Genius2015年12月04日 20:18:47 +00:00Commented Dec 4, 2015 at 20:18
1 Answer 1
Without a more concise code snippet to try and debug I think any answer to your question, as it stands, may be need to be part guesswork.
However, I had the same error message today from the same tool and like you I was also using the buildWhereClauseFromList and selectRelatedRecords functions that I found at Efficiently selecting related records using ArcPy?
I tracked it down to field values that included names like O'BRIEN and O'TOOLE. This was expanding to a where clause like:
"FieldName IN ('O'BRIEN','O'TOOLE')"
when SelectLayerByAttribute requires:
"FieldName IN ('O''BRIEN','O''TOOLE')"
I was able to get past it by changing:
valueList = ["'%s'" % value for value in valueList]
to
valueList = ["'%s'" % value.replace("'","''") for value in valueList]
in the buildWhereClauseFromList function.