topic Re: Selecting records from feature class that have special characters using a dictionary (python) in Python Questions

Selecting records from feature class that have special characters using a dictionary (python)

LuisResh — Fri, 26 Jul 2019 13:34:29 GMT

I am having trouble selecting counties that have single quotes and/or periods in their name with python that are stored in a dictionary. The below code I have works on county records with no special characters but does not with the special character counties. Any advice/suggestions?

import arcpy
fc = "Maryland_County_Boundaries"
CountyDict = {
"Allegany": "Allegany",
"Anne Arundel": "Anne Arundel",
"Baltimore": "Baltimore",
"Calvert": "Calvert",
"Caroline": "Caroline",
"Carroll": "Carroll",
"Cecil": "Cecil",
"Charles": "Charles",
"Dorchester": "Dorchester",
"Frederick": "Frederick",
"Garrett": "Garrett",
"Harford": "Harford",
"Howard": "Howard",
"Kent": "Kent",
"Montgomery": "Montgomery",
"Prince George\'s": "Prince George\"s",
"Queen Anne\'s": "Queen Anne\"s",
"St. Mary\'s": "St. Mary\"s",
"Somerset": "Somerset",
"Talbot": "Talbot",
"Washington": "Washington",
"Wicomico": "Wicomico",
"Worcester": "Worcester"
}
for k,v in CountyDict.items():
       if k == "Prince George's":
              qry = '"' + "COUNTY" + '"' + " IN ('{}')".format(v)
              arcpy.SelectLayerByAttribute_management(fc, 'NEW_SELECTION',qry)

Below is attachment of attribute field with county names.

Re: Selecting records from feature class that have special characters using a dictionary (python)

JoeBorgione — Sat, 11 Dec 2021 10:06:05 GMT

First, are you converting your 'fc' into a Feature Layer? Most of my python is executed as a stand alone script, so I do that by default. To make a selection on a feature class, you need to MakeFeatureLayer...

I've used something like this to find special characters in in street names:

import string
from string import punctuation
from string import ascii_letters
temptable = "tableinmemory"
fields = ["StreetName","OBJECTID"]
badstring = []
with arcpy.da.SearchCursor("view",fields) as cursor:
    for row in cursor:
        name = row[0]
        if any(char in string.punctuation for char in name):
            badstring.append(int(row[1]))  #get a list of violators oid‍‍‍‍‍‍‍‍‍‍‍

This particular snippet creates a list of object ids which you could then loop through in a selection block.

Re: Selecting records from feature class that have special characters using a dictionary (python)

JoshuaBixby — Fri, 26 Jul 2019 15:50:13 GMT

Joe Borgione‌, with ArcGIS Pro, the Select Layer By ... tools accept data sets directly and will return a feature layer. This is a behavior change from ArcMap.

Re: Selecting records from feature class that have special characters using a dictionary (python)

JoshuaBixby — Fri, 26 Jul 2019 15:54:27 GMT

Are the county names really using double quotes, e.g., Prince George"s ?

Re: Selecting records from feature class that have special characters using a dictionary (python)

JoeBorgione — Fri, 26 Jul 2019 16:08:05 GMT

Good to know!

Re: Selecting records from feature class that have special characters using a dictionary (python)

RandyBurton — Sat, 11 Dec 2021 10:06:08 GMT

Do you want something that would escape the single quote for a query?

>>> d = {"Prince George's": "Prince George\\'s" }
>>> print d["Prince George's"]
Prince George\'s
>>> query = "COUNTY = '{}'".format(d["Prince George's"])
>>> print query
COUNTY = 'Prince George\'s'‍‍‍‍‍‍

Re: Selecting records from feature class that have special characters using a dictionary (python)

LuisResh — Fri, 26 Jul 2019 16:15:15 GMT

The "fc" is an already existing layer and the intent was to use this bit of code in both pro and desktop within different scripts I am creating. But in desktop creating a select by attributes statement requires the double quote instead of single quote (EX: 'Prince George's' ----> 'Prince George"s').

Re: Selecting records from feature class that have special characters using a dictionary (python)

LuisResh — Fri, 26 Jul 2019 16:29:44 GMT

The intended use is to take a user input of a single or many county name, one name(s) listed in the above attachment image, and tell the script to run a select by attribute of those specified and in later code extract specific assets by the selected county or counties. The problem is the statement returns an error whenever the name selected counties are the ones with single quotes and/or period (St. Mary's, Queen Anne's, Prince George's)

Re: Selecting records from feature class that have special characters using a dictionary (python)

LuisResh — Fri, 26 Jul 2019 16:36:13 GMT

Even after the script runs nothing happens? No error is thrown and the statement matches exactly what is used when I manually do the select by attribute tool in desktop. Below are snippets of a manual selection working but the python code not. The query from the code is also printed to show end result.

Re: Selecting records from feature class that have special characters using a dictionary (python)

DanPatterson_Retired — Fri, 26 Jul 2019 16:40:15 GMT

Luis, have you tried saving a snippet that works manually, then load the '*.cal' file in a text editor and check to see what format is used?

Re: Selecting records from feature class that have special characters using a dictionary (python)

JoshuaBixby — Fri, 26 Jul 2019 16:56:27 GMT

What you are seeing in the GUI isn't a double quote but two single quotes. It isn't a Python thing, but a SQL thing, i.e., in SQL you use a double single quote to escape a single quote.

Re: Selecting records from feature class that have special characters using a dictionary (python)

RandyBurton — Sat, 11 Dec 2021 10:06:11 GMT

Or:

county = "Prince George's"
query = "COUNTY = '{}'".format(county.replace("'","\\'"))
print query
# COUNTY = 'Prince George\'s'

county = ["Prince George's", "Queen Anne's"]
query = "COUNTY IN ('{}')".format("', '".join("".join(i.replace("'","\\'")) for i in county))
print query
# COUNTY IN ('Prince George\'s', 'Queen Anne\'s')‍‍‍‍‍‍‍‍‍

Re: Selecting records from feature class that have special characters using a dictionary (python)

LuisResh — Fri, 26 Jul 2019 17:41:06 GMT

Yes that worked! So in dictionary, for example Prince George's, I changed item to "Prince George\'s": "Prince George\'\'s" and it worked. Thank you all for your help! Updated code below.

import arcpy
fc = "Maryland_County_Boundaries"
CountyDict = {
"Allegany": "Allegany",
"Anne Arundel": "Anne Arundel",
"Baltimore": "Baltimore",
"Calvert": "Calvert",
"Caroline": "Caroline",
"Carroll": "Carroll",
"Cecil": "Cecil",
"Charles": "Charles",
"Dorchester": "Dorchester",
"Frederick": "Frederick",
"Garrett": "Garrett",
"Harford": "Harford",
"Howard": "Howard",
"Kent": "Kent",
"Montgomery": "Montgomery",
"Prince George\'s": "Prince George\'\'s",
"Queen Anne\'s": "Queen Anne\'\'s",
"St. Mary\'s": "St. Mary\'\'s",
"Somerset": "Somerset",
"Talbot": "Talbot",
"Washington": "Washington",
"Wicomico": "Wicomico",
"Worcester": "Worcester"
}
for k,v in CountyDict.items():
         if k == "Prince George's":
               qry = '"' + "COUNTY" + '"' + " IN ('{}')".format(v)
               arcpy.SelectLayerByAttribute_management(fc, 'NEW_SELECTION',qry)