Loop: Select by attribute and write to attribute table

1704
4
Jump to solution
02-20-2017 01:57 PM
jadenrowley
New Contributor

Is there a way to loop this function so that I'm not retyping it each time? I want to select and write to the attribute table for 3 bands, but am lost on how to loop this. See code below:

import arcpy

arcpy.SelectLayerByAttribute_management("Strain Regions","NEW_SELECTION",'''"band_number" = 'Band 1' ''')
arcpy.SelectLayerByLocation_management("Earthquakes","COMPLETELY_WITHIN","Strain Regions",'',"ADD_TO_SELECTION")
arcpy.AddField_management("Earthquakes",'region2', "TEXT")
arcpy.CalculateField_management("Earthquakes","region2",'"band 1"')
arcpy.SelectLayerByAttribute_management("Strain Regions","CLEAR_SELECTION")
arcpy.SelectLayerByAttribute_management("Earthquakes","CLEAR_SELECTION")

arcpy.SelectLayerByAttribute_management("Strain Regions","NEW_SELECTION",'''"band_number" = 'Band 2' ''')
arcpy.SelectLayerByLocation_management("Earthquakes","COMPLETELY_WITHIN","Strain Regions",'',"ADD_TO_SELECTION")
arcpy.AddField_management("Earthquakes",'region2', "TEXT")
arcpy.CalculateField_management("Earthquakes","region2",'"band 2"')
arcpy.SelectLayerByAttribute_management("Strain Regions","CLEAR_SELECTION")
arcpy.SelectLayerByAttribute_management("Earthquakes","CLEAR_SELECTION")

arcpy.SelectLayerByAttribute_management("Strain Regions","NEW_SELECTION",'''"band_number" = 'Band 3' ''')
arcpy.SelectLayerByLocation_management("Earthquakes","COMPLETELY_WITHIN","Strain Regions",'',"ADD_TO_SELECTION")
arcpy.AddField_management("Earthquakes",'region2', "TEXT")
arcpy.CalculateField_management("Earthquakes","region2",'"band 3"')
arcpy.SelectLayerByAttribute_management("Strain Regions","CLEAR_SELECTION")
arcpy.SelectLayerByAttribute_management("Earthquakes","CLEAR_SELECTION")

Tags (3)
0 Kudos
1 Solution

Accepted Solutions
DarrenWiens2
MVP Honored Contributor

Untested, but I think it should be like this:

for i in range(1,4): # returns 1,2,3
    arcpy.SelectLayerByAttribute_management("Strain Regions","NEW_SELECTION",'''"band_number" = 'Band ' + str(i) ''')
    arcpy.SelectLayerByLocation_management("Earthquakes","COMPLETELY_WITHIN","Strain Regions",'',"ADD_TO_SELECTION")
    arcpy.AddField_management("Earthquakes",'region2', "TEXT")
    arcpy.CalculateField_management("Earthquakes","region2",'"band " + str(i) ')
    arcpy.SelectLayerByAttribute_management("Strain Regions","CLEAR_SELECTION")
    arcpy.SelectLayerByAttribute_management("Earthquakes","CLEAR_SELECTION")

Basically, by using str(i) in the loop, it changes from 1 to 2 to 3. 

View solution in original post

4 Replies
DarrenWiens2
MVP Honored Contributor

Untested, but I think it should be like this:

for i in range(1,4): # returns 1,2,3
    arcpy.SelectLayerByAttribute_management("Strain Regions","NEW_SELECTION",'''"band_number" = 'Band ' + str(i) ''')
    arcpy.SelectLayerByLocation_management("Earthquakes","COMPLETELY_WITHIN","Strain Regions",'',"ADD_TO_SELECTION")
    arcpy.AddField_management("Earthquakes",'region2', "TEXT")
    arcpy.CalculateField_management("Earthquakes","region2",'"band " + str(i) ')
    arcpy.SelectLayerByAttribute_management("Strain Regions","CLEAR_SELECTION")
    arcpy.SelectLayerByAttribute_management("Earthquakes","CLEAR_SELECTION")

Basically, by using str(i) in the loop, it changes from 1 to 2 to 3. 

View solution in original post

jadenrowley
New Contributor

Thank you! I'm still getting an issue as follows: 

An invalid SQL statement was used. [SELECT OBJECTID FROM polygons WHERE "band_number" = 'Band ' + str(i) ] 

I'm guessing it's an error with quotation marks or spacing of some sort, so I'm trying to change it around here.

0 Kudos
DarrenWiens2
MVP Honored Contributor

Yes, I assume it does have to do with the quotes. It looks like a mess, but here's how I try to set up my queries.

1.) Enclose the main query in triple-double-quotes.

2.) Use 'format' notation.

3.) Use AddFieldDelimiters to avoid the quote/bracket/nothing problem between data sources.

arcpy.SelectLayerByAttribute_management("Strain Regions","NEW_SELECTION",""" {} = 'Band {}' """.format(arcpy.AddFieldDelimiters("Strain Regions","band_number"),str(i)))

More info and an example here.

jadenrowley
New Contributor

This helps- I've rarely used field delimiters being new to arcpy so I'll do some reading.

0 Kudos