Escaping single quotes for an SQL query in ArcPy

Question

I have some data with strings containing single quotes (') and can't figure out a way to pass that string into an SQL query in arcpy.analysis.Select().
The SQL query running on a feature class in a GDB requires single quotes for values e.g. Query: "Name = 'Bob'". I want to pass in a name such as "exam'ple2" into the query.
The "Definition Query" in ArcGIS Pro resolves this by using '' (2x single quotes). See below for an example of a working query:
NAME IN ('example1','exam''ple2','''examp''le3')

--- each ' in input is replaced with ''
However, this is not a working option for a python script since each pair of quotes (' ') is treated as a separate string, and all my attempts end up with strings encapsulated by double-quotes.
Here's the code I'm trying to run:
import arcpy

nametuple = ('example1',"exam'ple2","'examp'le3")

arcpy.analysis.Select("Areas","test",f"NAME IN {nametuple}")

ERROR 000358: Invalid expression NAME IN ('example1',"exam'ple2","'examp'le3")

PolyGeo · Accepted Answer

I just ran a test using ArcGIS Pro 2.7.2 and code like this worked for me from the Python window:
arcpy.analysis.Select("TestFC","TestFC_Sel","Name IN ('Dzawada''enuxw First Nation')")

Based on that learning I wrote a short Python script to test the same syntax in IDLE:
import arcpy

arcpy.env.overwriteOutput = True
arcpy.env.workspace = r"C:tempTestProject.gdb"

arcpy.analysis.Select("TestFC","TestFC_Sel",
                      "Name IN ('Dzawada''enuxw First Nation')")

That worked too.
I expanded my test to include two other values in the Name field:
import arcpy

arcpy.env.overwriteOutput = True
arcpy.env.workspace = r"C:tempTestProject.gdb"

arcpy.analysis.Select("TestFC","TestFC_Sel",
                      "Name IN ('''Namgis Nation','Dzawada''enuxw First Nation','Gwa''Sala-''Nakwaxda''xw Nation')")

That worked too.
Prompted by @Vince's comment I also ran a third test where the values are provided in a list before building the expression:
import arcpy

arcpy.env.overwriteOutput = True
arcpy.env.workspace = r"C:tempTestProject.gdb"

namesList = ["'Namgis Nation","Dzawada'enuxw First Nation","Gwa'Sala-'Nakwaxda'xw Nation"]

arcpy.analysis.Select("TestFC","TestFC_Sel",
                      "Name in ('{:s}')".format("','".join([x.replace("'","''") for x in namesList])))

That worked too.
For a fourth test I used a slightly different Python string formatting style and included a double-quoted string with no single quote embedded in it, and that worked too:
import arcpy

arcpy.env.overwriteOutput = True
arcpy.env.workspace = r"C:tempTestProject.gdb"

namesList = ["c","'Namgis Nation","Dzawada'enuxw First Nation","Gwa'Sala-'Nakwaxda'xw Nation"]

arcpy.analysis.Select("TestFC","TestFC_Sel",
                      "Name in ('{}')".format("','".join([x.replace("'","''") for x in namesList])))

Consequently, I am currently not able to reproduce the problem using my understanding from your description of it.

Escaping single quotes for an SQL query in ArcPy

One Answer

Add your own answers!

Ask a Question