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!

[JoeBorgione]

You aren't the only one; if you search on something like 'special characters' in the python space, you'll see I am your compadre in pain.  The problem with allowing special characters in a database is they may be great for the those entering/creating the data but they suck for those of use who actually use the data.  

As you've probably discovered, once you filter out for one special character, there are a dozen more that pop up.

Can you provide some of the data that it's choking on?  Even just in the form of a csv.  Maybe another set of eyes is all you need.

EDITED MOMENTS LATER....

I just noticed in lines 9 & 10 you haven't used arcpy.makeFeatureLayer   prior to your selections.  I wonder if that might be you problem.

[OlyPowers]

I'm unsure about the FeatureLayer. I think I'll still have an issue with the tool not being able to read the query statement regardless. Some of my Problem values are:

BRAUM'S ADDITION

BROWN & BROWN SUBD

I-30 - 205 PLAZA 1

K.H.D. INC. SUBDIVISION

PANEX LTD ADDN (REPLAT)

SUBWAY / GATEWAY/ HEALTH FOODSTORE

So I've got a pretty wide range of characters to deal with, and who knows what could be added in the future.  @DavidPike I've been playing with raw string formatting, but haven't been successful as of yet.

[DavidPike]

query1 = r"Subd_Name = '{}'".format(sub)

[OlyPowers]

It's still encountering the error. I printed the query before it runs the selection, and it's coming out as:

Subd_Name = 'BRAUM'S ADDITION'

ArcGIS Pro accounts for the apostrophe by doubling it, but I'm unsure how to do that dynamically in this situation.

[DavidPike]

oh an apostrophe, that's a pain. maybe

"Subd_Name = '{}'".format(sub.replace("'", "''"))

[DavidPike]

What about raw string formatting?

[JoeBorgione]

Good idea @DavidPike !

[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