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

554
12
Jump to solution
07-26-2019 06:34 AM
LuisResh
New Contributor II

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.

Tags (1)
0 Kudos
1 Solution

Accepted Solutions
JoshuaBixby
MVP Esteemed Contributor

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.

View solution in original post

12 Replies
JoeBorgione
MVP Esteemed Contributor

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.

That should just about do it....
0 Kudos
JoshuaBixby
MVP Esteemed Contributor

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.

JoeBorgione
MVP Esteemed Contributor

Good to know!

That should just about do it....
0 Kudos
JoshuaBixby
MVP Esteemed Contributor

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

0 Kudos
LuisResh
New Contributor II

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').

0 Kudos
RandyBurton
MVP Regular Contributor

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'
RandyBurton
MVP Regular Contributor

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')
0 Kudos
LuisResh
New Contributor II

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)

0 Kudos
LuisResh
New Contributor II

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.

0 Kudos