Pass dictionary key to arcpy.SelectLayerByAttribute_management

1245
9
06-11-2021 08:43 AM
TonyAlmeida
Occasional Contributor II

 I am trying to compare the PIN fields of two layers, fc1 and fc2 and select fc2 features. The issues I have is that this process one PIN at a time it doesn't just select them all at once.

 

 

#arcpy.MakeFeatureLayer_management(fc2, "fc2_Lyr")

for WellID in fc2List:
    arcpy.SelectLayerByAttribute_management(fc2, "NEW_SELECTION", "\"PIN\" = '{}'".format(WellID))

#arcpy.MakeFeatureLayer_management(fc2, "SelPar_lyr", "\"PIN\" = '{}'".format(WellID))

 

 

0 Kudos
9 Replies
DavidPike
MVP Frequent Contributor

Not entirely sure what you mean, is it to select everything in fc2list?

If so, maybe just get rid of the for loop and use the SQL 'IN' statement.

arcpy.SelectLayerByAttribute_management("fc2_Lyr", "NEW_SELECTION", "\"PIN\" IN ({})".format(fc2List))
0 Kudos
TonyAlmeida
Occasional Contributor II

My bad I thought I posted the whole code.

 

 

fc = "Wells"
fc2 = "Polygon"

fcList = []
fc2List = []

with arcpy.da.SearchCursor(fc, ["PIN"]) as cursor:
    for row in cursor:
        fcList.append(row[0])
        #print row[0]

with arcpy.da.SearchCursor(fc2, ["PIN"]) as cursor:
    for row in cursor:
        if row[0] in fcList:
            fc2List.append(row[0])
            print row[0]


#arcpy.MakeFeatureLayer_management(fc2, "fc2_Lyr")

for WellID in fc2List:
    arcpy.SelectLayerByAttribute_management("fc2, "NEW_SELECTION", "\"PIN\" = '{}'".format(WellID))
#arcpy.MakeFeatureLayer_management(fc2, "SelPar_lyr", "\"PIN\" = '{}'".format(WellID))

 

0 Kudos
JoeBorgione
MVP Emeritus

A variation on @DavidPike 's theme.  Here I use list comprehension to create a list of the Wells pin values and then select the Polygons if the PIN is in the list.  You mentioned using a dictionary in the subject and you can use dictionary comphrension the same way. FYI: untested.  you may need to fiddle with the select statement...

 

import arcpy

fc = "Wells"
fc2 = "Polygon"
fields = ['PIN']

fcList = [i[0] for i in arcpy.da.SearchCursor(fc,fields)]


arcpy.MakeFeatureLayer_management(fc2, "fc2_Lyr")

select = f'PIN IN {list}'
arcpy.SelectLayerByAttribute_management("fc2_Lyr", 'ADD_TO_SELECTION', select)

 

.

 

That should just about do it....
0 Kudos
BlakeTerhune
MVP Regular Contributor

I like this, but don't forget that formatting your select string with the list isn't valid SQL syntax because of the brackets. You need to reformat it with parenthesis. Assuming PIN is a string, you would need

 

select = f'PIN IN({",".join(fcList)})'

 

JoeBorgione
MVP Emeritus

Thanks @BlakeTerhune That's exactly why I included the disclaimer!  

That should just about do it....
TonyAlmeida
Occasional Contributor II

ok, thanks for the suggestion. It is not liking the  expression. This is currently in a shapefile.
Failed to execute (SelectLayerByAttribute). PIN is a string

 

arcpy.MakeFeatureLayer_management(fc2, "fc2_Lyr")
result = arcpy.GetCount_management("fc2_Lyr").getOutput(0)
print ('{} has {} records'.format("fc2_Lyr", result[0]))
select = f'PIN IN({",".join(fc2List)})'
arcpy.SelectLayerByAttribute_management("fc2_Lyr", 'ADD_TO_SELECTION', select)

 

0 Kudos
BlakeTerhune
MVP Regular Contributor

Ah, I forgot the single quotes. As @JoeBorgione mentioned, these often take some fiddling. Hopefully this will keep the strings as single quotes.

 

 

select = f"PIN IN({str(fc2List)[1:-1]})"

 

 

Edit:

I just found this solution that is more robust and less of a hack.

 

str_values = ",".join(f"'{x}'" for x in fc2List)
select = f"PIN IN({str_values})"

 

DanPatterson
MVP Esteemed Contributor

Geoprocessing considerations for shapefile output—ArcGIS Pro | Documentation

dBASE files have little SQL support aside from a WHERE clause.


... sort of retired...
JoeBorgione
MVP Emeritus
pinList = ['123','456','789']

'123' in pinList
Out[2]: True

'123' in ','.join(pinList)
Out[4]: True

 

Like I said, you'll need to fiddle a bit with the selection statement.  It typically takes me a time or two to get them right,

That should just about do it....