1

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 Dec 4 '15 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 Dec 4 '15 at 19:47
  • Do you have 'NER' as an actual value? – recurvata Dec 4 '15 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 Dec 4 '15 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 Dec 4 '15 at 20:18
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.