Select layer by attribute based on multiple field attribute values in a list

Question

I have a list of street_names

['SH-701', 'SH-67', ' ABC', 'Dharampur Road', 'Kailash Road', 'Tithal Road', 'SH-6', 'Halar Road', 'SH-183', 'Ramji Tekra Road', 'Mahatma Gandhi Road', 'Bandar Road', 'M G Road', 'M Road', 'Station Road', 'Azad Chowk', 'Golden Quadrilateral', 'Service Road', 'NH-48', 'NH-8', 'Baldev Street', 'Stadium Road', 'Civil Road', 'Rotatry Circle', 'Auranga River Major Bridge', 'Police Headquarters Road', 'Ramroti Chowk', ' dalaram st', 'Maa Sharda Path', 'Tower Road', 'Pranami Street', 'Abrama Road', 'Atul Road', 'Gali No 7', 'Javahar Dhakka Road', 'Railway Yard Road', 'Azad Road', 'Shivraj Park Road', 'Vajifdar Street', 'Tithal Crass Lane', 'Desai Jagdishbhai Marg', 'Approach Road', 'Nana Parsiwad Road', 'Kosamba Road', 'Desaivad Road', 'NCB Road', 'Juna Kosamba Road', 'Mota Taiwad Road', 'Gachiwad Road', 'Dadiya Faliya Road', 'Valsad Callege Road', 'B Road', 'D Road', 'E Road', 'C Road', 'I Road', 'H Road', 'Kharia Road', 'Nanakwada Kharia Faliya Road', 'Nanakwada Road', 'Vinayak Road', 'Lal School Road', 'Nana Talwad Road', 'Sipaiwad Road', 'Malaviyaji Road', 'Agyari Street', 'Parsiwad Road', 'Disepensary Road', 'Moti Mehtwad Road', 'Ghandhi Chowk', 'Desai Street', 'Tandel Street', 'Divadandi Road', 'Mullawadi Road', 'Ramwadi Road', 'Geeta Sadan Road', 'Shahid Road', 'Satyam Road', 'Street Number 1', 'Ambelal Patel Marg', 'Mogravadi Road', 'Valsad Station Road', 'Thakorbhai Park Society Road', 'Jujwa Sugar Road', 'Tapawad Road', 'Jujwa Marala Road', 'IP Gandhi School Road', 'Tadkeshwar Road', 'Kapadia Chawl Bechar Road', 'Valsad Godi Road', 'Bechar Road', 'Railway Colony Road', 'Control Office Road', 'A Road', 'F Road', 'Devang Gandhi', 'Desai Faliya Halar Road', 'Suraj Srushti Halar Road', 'Patel Street', 'Krishna Park Road', 'White House Lane', 'Abrama Bridge', 'Leela Park Society Road', 'Wanki River Bridge Road']

I want to use SelectLayerByAttribute for multiple fields where it will iterate select layer by attribute function by looping through different street_names values for multiple fields each time and will provide me the required features.

I am using this code:

    for item in street_names:
        query = '"' + "ST_NAME1 = '" + item +  "' Or Remark1 = '" + item + "' Or ST_NAME2 = '" + item + "' Or Remark2 = '" + item + "' Or ST_NAME3 = '" + item + "' Or Remark3 = '" + item + "' Or ST_NAME4 = '" + item + "' Or Remark4 = '" + item + "' Or ST_NAME5 = '" + item + "' Or Remark5 = '" + item + "' Or ST_NAME6 = '" + item + "' Or Remark6 = '" + item + "' Or ST_NAME7 = '" + item + "' Or Remark7 = '" + item + "' Or ST_NAME8 = '" + item + "' Or Remark8 = '" + item + "' Or ST_NAME9 = '" + item + "' Or Remark9 = '" + item + "'" + '"'
        in_data = arcpy.management.SelectLayerByAttribute(self.street, "ADD_TO_SELECTION", query , "NON_INVERT")

But it is throwing this error:

return lambda *args: val(*gp_fixargs(args, True)) arcgisscripting.ExecuteError: ERROR 000358: Invalid expression Failed to execute (SelectLayerByAttribute).

I am using ArcGIS Pro 2.7 and I need to perform this using ArcPy package in a Python script.

Answer 1

Debugging your query assembly math with a fixed item of 'Named Road' using:

parts = query.split('Or')
print("Expression members:\n\t{:s}".format('OR\n\t'.join(parts)))

I got output:

    "ST_NAME1 = 'Named Road' OR
     Remark1 = 'Named Road' OR
     ST_NAME2 = 'Named Road' OR
     Remark2 = 'Named Road' OR
     ST_NAME3 = 'Named Road' OR
     Remark3 = 'Named Road' OR
     ST_NAME4 = 'Named Road' OR
     Remark4 = 'Named Road' OR
     ST_NAME5 = 'Named Road' OR
     Remark5 = 'Named Road' OR
     ST_NAME6 = 'Named Road' OR
     Remark6 = 'Named Road' OR
     ST_NAME7 = 'Named Road' OR
     Remark7 = 'Named Road' OR
     ST_NAME8 = 'Named Road' OR
     Remark8 = 'Named Road' OR
     ST_NAME9 = 'Named Road' OR
     Remark9 = 'Named Road'"

So it appears that you put double-quotes around the whole expression, making it one huge Boolean field name (hence the syntax error).

But there's lots of simplification opportunities here, exploiting the use of both str.format() and str.join():

    fields = []
    for i in range(1,10):
        fields.append("ST_NAME{:d}".format(i))
        fields.append("Remark{:d}".format(i))
    
    for item in street_names:
        terms = []
        for field in fields:
            terms.append("{:s} = '{:s}'".format(field,item))
        query = ' OR '.join(terms)
            
        in_data = arcpy.management.SelectLayerByAttribute(
                self.street, "ADD_TO_SELECTION", query , "NON_INVERT")

Not only is this cleaner, you also don't need to debug string math.

It will run slower (albeit not measurably slower, in the context of layer selection). I benchmarked the two different query assembly algorithms, and the .join added one millisecond to overall execution across all 104 street names). So then I tried a list comprehension in terms assembly:

    for item in street_names:
        terms = ["{:s} = '{:s}'".format(field,item) for field in fields]
        query = ' OR '.join(terms)

and was able to cut the cost to 0.5 milliseconds (measurement averaged across 500 iterations).

If you want double quotes around each field name (permitted, but not required for file geodatabase), then the list comprehension gets six extra characters:

    for item in street_names:
        terms = [""""{:s}" = '{:s}'""".format(field,item) for field in fields]
        query = ' OR '.join(terms)

Answer 2

This approach helped.

for item in street_names: in_data = arcpy.management.SelectLayerByAttribute(self.street, "ADD_TO_SELECTION", "ST_NAME1 ='{}'".format(item) or "Remark1 ='{}'".format(item) or "ST_NAME2 ='{}'".format(item) or "Remark2 ='{}'".format(item) or "ST_NAME3 ='{}'".format(item) or "Remark3 ='{}'".format(item) or "ST_NAME3 ='{}'".format(item) or "Remark3 ='{}'".format(item) or "ST_NAME4 ='{}'".format(item) or "Remark4 ='{}'".format(item) or "ST_NAME5 ='{}'".format(item) or "Remark5 ='{}'".format(item) or "ST_NAME6 ='{}'".format(item) or "Remark6 ='{}'".format(item) or "ST_NAME7 ='{}'".format(item) or "Remark7 ='{}'".format(item) or "ST_NAME8 ='{}'".format(item) or "Remark8 ='{}'".format(item) or "ST_NAME9 ='{}'".format(item) or "Remark9 ='{}'".format(item), "NON_INVERT")