topic Re: Accounting for special characters in dynamic arcpy Search by Attribute query statement in ArcGIS Pro Questions

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

OlyPowers — Fri, 10 Sep 2021 13:48:22 GMT

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!

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

JoeBorgione — Fri, 10 Sep 2021 14:35:45 GMT

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.

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

DavidPike — Fri, 10 Sep 2021 15:00:10 GMT

What about raw string formatting?

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

JoeBorgione — Fri, 10 Sep 2021 15:02:16 GMT

Good idea @DavidPike !

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

OlyPowers — Fri, 10 Sep 2021 15:35:46 GMT

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.

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

DavidPike — Fri, 10 Sep 2021 15:37:33 GMT

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

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

OlyPowers — Fri, 10 Sep 2021 15:56:46 GMT

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.

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

DavidPike — Fri, 10 Sep 2021 16:13:57 GMT

oh an apostrophe, that's a pain. maybe

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

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

HannesZiegler — Tue, 09 Nov 2021 17:40:43 GMT

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