topic Re: selecting from a FGDB where the item has apostrophes in Python Questions

selecting from a FGDB where the item has apostrophes

JoshuaChan — Fri, 10 Feb 2012 20:05:39 GMT

I am trying to perform a SearchCursor or alternatively a SelectLayerByAttribute where the data will sometimes contain special characters, like apostrophes, dashes, and slashes.

I'm trying to figure out how to make the apostrophes work in my query.
eg.

featureclass = r"C:\my feature class"
qry = "Item_Name = 'John's Cabin' "
arcpy.SearchCursor(featureclass, qry)

that of course doesn't work. But I've tried a few different things, and none works.
I've tried:
qry = "John\'s Cabin"; John''s Cabin and neither work. Any other suggestions?

Re: selecting from a FGDB where the item has apostrophes

DarrenWiens2 — Fri, 10 Dec 2021 22:00:48 GMT

You need to enclose the field (Item_Name) in double quotes, the string value in single quotes (John's Cabin), definately escape the apostrophe with another one (John''s Cabin), and possibly wrap the whole thing in escaped double quotes. See this page (Chicago examples), and try it yourself in the Select Layer By Attribute toolbox tool expression builder. I would try:

qry = "Item_Name" = 'John''s Cabin' 

and if that doesn't work:

qry = """ "Item_Name" = 'John''s Cabin' """

Re: selecting from a FGDB where the item has apostrophes

JoshuaChan — Fri, 10 Dec 2021 22:00:51 GMT

No that isn't it. The problem isn't in the ITEM NAME but rather in the value "John's Cabin"
because ArcMap is expecting single quotes to be around the value - but in this case the apostrophe messes that all up.
So far, using the double quotes in python doesn't seem to work nor does using the escape character \.

You need to enclose the field (Item_Name) in double quotes, the string value in single quotes (John's Cabin), definately escape the apostrophe with another one (John''s Cabin), and possibly wrap the whole thing in escaped double quotes. See this page (Chicago examples), and try it yourself in the Select Layer By Attribute toolbox tool expression builder. I would try:
qry = "Item_Name" = 'John''s Cabin' 

and if that doesn't work:

qry = """ "Item_Name" = 'John''s Cabin' """

Re: selecting from a FGDB where the item has apostrophes

RichardFairhurst — Fri, 10 Feb 2012 21:02:32 GMT

No that isn't it. The problem isn't in the ITEM NAME but rather in the value "John's Cabin"
because ArcMap is expecting single quotes to be around the value - but in this case the apostrophe messes that all up.
So far, using the double quotes in python doesn't seem to work nor does using the escape character \.

I t didn't work because there was an error in the first line suggested. It should have been:

qry = """Item_Name"" = 'John''s Cabin' "

The two single quotes together are SQL specific, not Python specific and that is how you include a single quote in an SQL query. (Done it many times with an FGDB).

Re: selecting from a FGDB where the item has apostrophes

JoshuaChan — Fri, 10 Feb 2012 22:05:57 GMT

OK, so here's my example, with the real field name and value:
[HTML]arcpy.SelectLayerByAttribute_management(lyr, "NEW_SELECTION", """CONTACT_ORG"" = 'Lil''wat First Nation'")[/HTML]

I got an error in PythonWin compiler saying: Failed to check - syntax error - EOF while scanning triple-quoted string literal

did I put one too many double quotes?

arcMap will bomb if it sees this : "ITEM" = 'Lil''wat First Nation' because I think it stops at 'Lil'

I t didn't work because there was an error in the first line suggested. It should have been:
qry = """Item_Name"" = 'John''s Cabin' "
The two single quotes together are SQL specific, not Python specific and that is how you include a single quote in an SQL query. (Done it many times with an FGDB).

Re: selecting from a FGDB where the item has apostrophes

JoshuaChan — Fri, 10 Dec 2021 22:00:53 GMT

OK. my mistake, human error caused most of the problems.

In case anyone's interested this code works:

# fixing FN name if apostrophe exists
if string.find(fn_name, "'") <> -1:
    print 'fixing apostrophe in', fn_name, 'to be queryable.'
    fn_name_qry = string.replace(fn_name, "'", "''")
    print fn_name_qry
else: 
    fn_name_qry = fn_name

cad_qry = cad_itm + ' = \'' + fn_name_qry + '\''

that results in an fn_name looking like Lil''wat First Nation
and the double-single quotes is interpreted OK by ArcMap.

thanks everyone.

Re: selecting from a FGDB where the item has apostrophes

DavidTillberg_community — Thu, 25 Nov 2021 14:04:38 GMT

Issue is that SQL query syntax needs to escape single quote, which is done by using two single quotes:

' (in John's) needs to become '' in SQL syntax

I think easiest way is to split out the search term into a variable:

qry_item_value = "John's Cabin"

qry_item_value_fixed = qry_item_value.replace("'", "''")

qry = "Item_Name = '%s'" % qry_item_value_fixed