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

mattstutts — Sun, 12 Dec 2021 02:10:36 GMT

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))
arcpy.AddMessage(sqlExp)


#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)

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

BruceHarold — Wed, 20 Jan 2016 16:30:08 GMT

Please take a look at this sample and see if it does what you need:

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

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

mattstutts — Wed, 20 Jan 2016 16:41:16 GMT

Actually, yeah. I've seen and used that code before. I should have started there. This would do, provided I add a for lyr in layers: line to it.

topic ArcPy: More efficient method to cycle through code/selection of records in Python Questions

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

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

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