topic Re: ERROR 000358: Invalid expression Failed to execute (SelectLayerByAttribute) in Python Questions

ERROR 000358: Invalid expression Failed to execute (SelectLayerByAttribute)

mattstutts — Sat, 11 Dec 2021 18:45:38 GMT

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

Re: ERROR 000358: Invalid expression Failed to execute (SelectLayerByAttribute)

DanPatterson_Retired — Fri, 04 Dec 2015 19:53:24 GMT

can you format your code using Python code blocks in the Syntax Highligher section, >>> It is hard to follow the indentation

Re: ERROR 000358: Invalid expression Failed to execute (SelectLayerByAttribute)

DarrenWiens2 — Fri, 04 Dec 2015 19:54:28 GMT

What is the result of 'print whereClause'?

Re: ERROR 000358: Invalid expression Failed to execute (SelectLayerByAttribute)

BruceHarold — Fri, 04 Dec 2015 19:58:00 GMT

Please take a look at this as an alternate solution:

http://www.arcgis.com/home/item.html?id=e638afe0695a4ad38388cb8d9b350446

Regards

Re: ERROR 000358: Invalid expression Failed to execute (SelectLayerByAttribute)

mattstutts — Fri, 04 Dec 2015 20:08:29 GMT

That gives me: CR_ID IN ('{guid]}', '{guid}', etc).

Basically, a list of string values that come from the field CR_ID from the CR_Catalog table. I get these returned no matter the REG_CODE value I choose... even 'NER'.

Re: ERROR 000358: Invalid expression Failed to execute (SelectLayerByAttribute)

DanPatterson_Retired — Fri, 04 Dec 2015 20:14:13 GMT

did you copy that or type it since there is an unclosed ] after the first guid ( IN ('{guid X }'

Re: ERROR 000358: Invalid expression Failed to execute (SelectLayerByAttribute)

DanPatterson_Retired — Fri, 04 Dec 2015 20:15:29 GMT

can't open on iThingy...got a snippet from there demonstrating the principles Bruce?

Re: ERROR 000358: Invalid expression Failed to execute (SelectLayerByAttribute)

mattstutts — Fri, 04 Dec 2015 20:18:20 GMT

Sorry... typo. CR_ID IN ('{guid}', '{guid}', etc)

Re: ERROR 000358: Invalid expression Failed to execute (SelectLayerByAttribute)

BruceHarold — Fri, 04 Dec 2015 20:19:11 GMT

The code makes Python sets from field values and does comparisons between sets.

No joins required, no SQL. If you can even only download the zipfile the .py is there.

Re: ERROR 000358: Invalid expression Failed to execute (SelectLayerByAttribute)

mattstutts — Fri, 04 Dec 2015 20:24:10 GMT

Tried it. I'd have to first do a full selection on the first table (CR_Catalog) instead of using arcpy.da.SearchCursor. I see how it can get me around a potential sql formatting problem but I'm not convinced that's what's going on here. Still, it might be an option.

Re: ERROR 000358: Invalid expression Failed to execute (SelectLayerByAttribute)

DanPatterson_Retired — Fri, 04 Dec 2015 20:33:37 GMT

Thanks Bruce...zips and iThingy don't get along, I will check later. But in short all set operations can be used then I presume which would be nice and hopefully cut out all that syntax error stuff.

Re: ERROR 000358: Invalid expression Failed to execute (SelectLayerByAttribute)

BruceHarold — Fri, 04 Dec 2015 20:35:45 GMT

Toss that iThingy - get a Lumia like us real workers 😉

Re: ERROR 000358: Invalid expression Failed to execute (SelectLayerByAttribute)

mattstutts — Fri, 04 Dec 2015 22:05:48 GMT

Although I was able to modify that script to fit my needs and get it to run.... I still get errors when the 'keyview' table has records selected where REG_CODE = 'NER'. I don't get it. There aren't null values in there. This is insane.

Re: ERROR 000358: Invalid expression Failed to execute (SelectLayerByAttribute)

BruceHarold — Fri, 04 Dec 2015 22:36:10 GMT

Hi, if you can share your data I will take a look.

Re: ERROR 000358: Invalid expression Failed to execute (SelectLayerByAttribute)

BruceHarold — Fri, 04 Dec 2015 22:47:01 GMT

I spotted something suspicious.

Your code:

whereClause = "%s IN(%s)" % (fieldDelimited, ', '.join(map(str, valueList)))

INNER is of course a SQL reserved word, there isn't a space in IN('NER') and there may be a bug in the workspace SQL parser, please make sure you put a space between IN and the () part of the statement. The () part must also have the strings quoted. I use a construct like this:

values = ('NER','ABC','DEF')

whereClause = '{} IN {}'.format(arcpy.addFieldDelimiters(somefieldname),repr(values))

Re: ERROR 000358: Invalid expression Failed to execute (SelectLayerByAttribute)

mattstutts — Fri, 04 Dec 2015 22:53:52 GMT

That might take a while. This is a large sde db with many feature classes and some sensitive data I'd have to remove. I'll get around to this if necessary next week

Re: ERROR 000358: Invalid expression Failed to execute (SelectLayerByAttribute)

mattstutts — Fri, 04 Dec 2015 22:57:17 GMT

That'd make sense if it weren't for the fact that I've since been working w/the other script you pointed me to that doesn't use that whereClause. And again, it bombs out on NER. here's a portion of what I've been working on:

# Process: Select Layer By Attribute to pre-select records in catalogTable where REG_CODE = <input>

arcpy.SelectLayerByAttribute_management(catalogTable, selOption, sqlExp)

# Environment

arcpy.env.qualifiedFieldNames = False

# Get the set of selected OIDs in the input

desc = arcpy.Describe(linkTable)

fidSet = desc.FIDSet

if fidSet:

fidList = fidSet.replace(' ','').split(';')

oidSet = set([int(oid) for oid in fidList])

else:

oidSet = set()

# Get a set of key values

arcpy.AddMessage('Finding unique key field values...\n')

keyValues = set([row[0] for row in arcpy.da.SearchCursor(catalogTable,"CR_ID")])

# Extract all candidate target OIDs and their key field values

# We operate on this set for later selection on ObjectID

arcpy.AddMessage('Finding input table ObjectID and key field mapping...\n')

sourcePath = os.path.join(desc.path,desc.name)

tmpView = "tmpView"

arcpy.MakeTableView_management(sourcePath,tmpView)

oidKVDict = {row[0]:row[1] for row in arcpy.da.SearchCursor(tmpView,

['OID@',"CR_ID"])}

#oidKVList = [(k, v) for (k, v) in oidKVDict.items()]

oidKVList = [(k, v) for (k, v) in list(oidKVDict.items())]

allSet = set([t[0] for t in oidKVList])

# Find OID values to select on

arcpy.AddMessage('Finding selection set...\n')

if selOption == 'NEW_SELECTION': # Adopt value set

oidList = sorted([t[0] for t in oidKVList if t[1] in keyValues])

elif selOption == 'ADD_TO_SELECTION': # Add value set

addSet = set([t[0] for t in oidKVList if t[1] in keyValues])

oidSet |= addSet

oidList = sorted(list(oidSet))

elif selOption == 'REMOVE_FROM_SELECTION': # Common values removed

remSet = set([t[0] for t in oidKVList if t[1] in keyValues and t[0] in oidSet])

oidSet -= remSet

oidList = sorted(list(oidSet))

elif selOption == 'SUBSET_SELECTION': # Common values retained

andSet = set([t[0] for t in oidKVList if t[1] in keyValues])

oidSet &= andSet

oidList = sorted(list(oidSet))

else:

arcpy.AddError('Unknown selection option\n')

sys.exit(0)

# Make the selection

arcpy.AddMessage('Selecting...\n')

compOIDList = sorted(list(allSet - set(oidList)))

if len(compOIDList) == 0: # select all

whereClause = '{} is not null'.format(desc.OIDFieldName)

elif len(oidList) == 0: # select none

whereClause = '{} < 0'.format(desc.OIDFieldName)

elif len(oidList) <= len(compOIDList):

inStatement = repr(tuple(oidList)).replace(',)',')')

whereClause = '{} in {}'.format(desc.OIDFieldName,inStatement)

else:

inStatement = repr(tuple(compOIDList)).replace(',)',')')

whereClause = '{} not in {}'.format(desc.OIDFieldName,inStatement)

arcpy.SelectLayerByAttribute_management(linkTable,'NEW_SELECTION',whereClause)

#arcpy.SetParameter(5,linkTable)

arcpy.Delete_management(tmpView)

Re: ERROR 000358: Invalid expression Failed to execute (SelectLayerByAttribute)

mattstutts — Fri, 04 Dec 2015 22:58:16 GMT

I should point out.. .It bombs on this specifically... only when I pick 'NER'

arcpy.SelectLayerByAttribute_management(linkTable,'NEW_SELECTION',whereClause)

Re: ERROR 000358: Invalid expression Failed to execute (SelectLayerByAttribute)

BruceHarold — Fri, 04 Dec 2015 23:01:17 GMT

My money is still on some reserved word issue, lets do this by brute force when you can share your data. Thanks.

Re: ERROR 000358: Invalid expression Failed to execute (SelectLayerByAttribute)

mattstutts — Fri, 04 Dec 2015 23:01:39 GMT

Also, that part of the code with the IN(%).... I'm not passing 'NER' there. The guid string values from my CR_ID field are being passed in there. I believe.