AnsweredAssumed Answered

ArcPy: More efficient method to cycle through code/selection of records

Question asked by matt_stutts on Jan 20, 2016
Latest reply on Jan 20, 2016 by matt_stutts

I'm having a bit of trouble finding a more suitable way to handle a series of selections/queries based on a many-to-many relationship.

The code below works, for the most part, but it's in the "def selectRelatedRecords" section where I know I'm probably unnecessarily repeating steps and could use some input.


The basic idea:  I have a table (linkTable) where the user selects a field and a value from that table that becomes sqlExp.

I then get the value of the field "CR_ID" from the same linkTable where sqlExp is true.


I then want to cycle through all the feature layers in the current mxd and get the related records in those layers (again, CR_ID is the same as the now selected records in linkTable.)


But what I end up doing is reselecting the same records over and over in the linkTable each time I try to apply a SelectLayerByAtrribute to the available feature layers b/c I'm looping for lyr in layers:


I'm really looking for some tweaks so I can maybe put the selected CR_IDs into a list, maybe, and then apply the selectlayerbyattribtue to the available layers.


import arcpy, os, string
import arcpy.mapping as MAP

#Needed CR layers as well as Link table should be loaded into current mxd
mxd = MAP.MapDocument("CURRENT")
df = MAP.ListDataFrames(mxd)[0]

#Get Link tables from input
linkTable = arcpy.GetParameterAsText(0)
#Make lists of all feature layers in dataframe
layers = MAP.ListLayers(mxd, "", df)

#set Reg_Code value for regional data into a sql expression
fldName = arcpy.AddFieldDelimiters(arcpy.env.workspace, arcpy.GetParameterAsText(1))
sqlExp = fldName + " = " + " '{0}' ".format(arcpy.GetParameterAsText(2))

#set selection type
selectType = arcpy.GetParameterAsText(3)

#used in generating list of related CR_IDs for match Link table to CR Features.
linkKeyField = "CR_ID"
featureKeyField = "CR_ID"

#Main code to select all records in CR Feature Classes with matching Program ID in Link table
def buildWhereClauseFromList(linkTable, linkKeyField, valueList):
    # Add DBMS-specific field delimiters
    fieldDelimited = arcpy.AddFieldDelimiters(arcpy.Describe(linkTable).path, linkKeyField)
    arcpy.AddMessage("fieldDelimited value: {}".format(fieldDelimited))

    # Determine field type
    fieldType = arcpy.ListFields(linkTable, linkKeyField)[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
    arcpy.AddMessage("whereClause: {}".format(whereClause))

if len(layers) > 0:
    for lyr in layers:
        def selectRelatedRecords(linkTable, lyr, linkKeyField, featureKeyField):
        #    Defines the record selection from the record selection of the linkTable
        #      and applys it to the featureTable 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(linkTable, linkKeyField, sqlExp)])

            # Establishes the where clause used to select records from DestinationTable
            whereClause = buildWhereClauseFromList(lyr, featureKeyField, sourceIDs)
            arcpy.AddMessage("whereClause inside selectRelatedRecords: {} \n".format(whereClause))
            arcpy.AddMessage("lyr: {0}, selectType: {1}".format(lyr, selectType))

            # Process: Select Layer By Attribute
            arcpy.SelectLayerByAttribute_management(lyr, selectType, whereClause)

        # Process: Select related records between OriginTable and DestinationTable
        arcpy.AddMessage("Selecting records in CR Feature Layers related to records in Link table...")
        selectRelatedRecords(linkTable, lyr, linkKeyField, featureKeyField)