Accounting for special characters in dynamic arcpy Search by Attribute query statement

OlyPowers

I cannot be the only person to come across this problem, but I have yet to find a solution that works.

I have a script that counts the number of features that intersects another feature, and cycles through the file using the UpdateCursor. Everything has been going smoothly, until I came across an attribute that has special characters in it. Due to reasons, I cannot remove the apostrophes, ampersands, forward slashes, etc. from the attribute data and have to account for these in the code.

 

edit.startEditing(False, True)
edit.startOperation()

with arcpy.da.UpdateCursor(subdivisions, ['Subd_Name', 'LOTS', 'CON_STATUS']) as subdivUpdate:
    for each in subdivUpdate:
        if subdivUpdate[2] != "UNDERCON" or subdivUpdate[2] != "ESTBLSHD":
            sub = subdivUpdate[0]
            query1 = 'Subd_Name = \'{}\''.format(sub)
            subDiv = arcpy.management.SelectLayerByAttribute(subdivisions, 'NEW_SELECTION', query1)
            selectedParcels = arcpy.management.SelectLayerByLocation(parcels, 'HAVE_THEIR_CENTER_IN', subDiv)

            numberOfParcels = arcpy.GetCount_management(selectedParcels)
            print(subdivUpdate[0])
            each[1] = int(str(numberOfParcels))
            print(numberOfParcels)

        subdivUpdate.updateRow(each)

edit.stopOperation()
edit.stopEditing(True)

 

Everything runs the way it is supposed to until it comes across a record with a special character in it, and throws the error,  arcgisscripting.ExecuteError: ERROR 000358: Invalid expression Failed to execute (SelectLayerByAttribute).

I have tried several combinations of triple quotes around the query, but they've all failed as soon as it reaches a record with special characters. If there's a better way of dynamically inserting the query value I'm all ears.

 

Thanks in advance for any help!

HannesZiegler

Chiming in here, though I might be misunderstanding the question.

I simplified OP's case to isolate the error, and created a featureclass with the field Name containing the subdivision names OP provided in a comment. I got the same error OP got.

I think the problem might just be that any ' characters need to be escaped by doubling these up. I'd also suggest making use of arcpy.AddFieldDelimiters. So when I use re.sub to add an additional ' in front of any ' character encountered (so that ' becomes ''), the query works for all OP's strings.

 

 

with arcpy.da.UpdateCursor(fc, ["Name"]) as cursor:
    for row in cursor:
        subdiv = re.sub(r"(')", r"'\1", row[0])
        query = f"{arcpy.AddFieldDelimiters(fc, 'Name')} = '{subdiv}'"
        print(query)
        sel = arcpy.management.SelectLayerByAttribute(fc, "NEW_SELECTION", query)
        print('Selected OID(s):', arcpy.Describe(sel).FIDSet)

 

 

This prints the following to console:

Name = 'BRAUM''S ADDITION'
Selected OID(s): 1
Name = 'BROWN & BROWN SUBD'
Selected OID(s): 2
Name = 'I-30 - 205 PLAZA 1'
Selected OID(s): 3
Name = 'K.H.D. INC. SUBDIVISION'
Selected OID(s): 4
Name = 'PANEX LTD ADDN (REPLAT)'
Selected OID(s): 6
Name = 'SUBWAY / GATEWAY/ HEALTH FOODSTORE'
Selected OID(s): 7