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:
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 = 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
أكثر...
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 = 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
أكثر...