topic Problems single quotes and variables used in Where Clause in Python Questions

Problems single quotes and variables used in Where Clause

MattGray — Fri, 11 Apr 2014 12:42:34 GMT

I did not know where else to put this. I have a python list of string variables from an Arc Table I made, some of which contain single quotes in the middle of them. I am trying to have a for loop iterate through use these string values in the Where Clause of a few arcpy tools. The only thing is that in a where clause, since a string value has to to be in single quotes, the single quote in the middle of the string values is throwing an error and messing up the quotations that Arcmap is reading in the Where Clause. Other than having to have an update cursor go through and remove all the quotes from the string values, is there any way to get Arcpy tools with where clauses to work with strings that have a single quotation mark in them? I have already tried escaping it with \ and r', both of which didn't work. Any ideas?

Re: Problems single quotes and variables used in Where Clause

Anonymous User — Fri, 10 Dec 2021 22:21:32 GMT

Wow, that is tricky...I figured this out by doing the query manually in ArcMap...Looks like you have to escape the single quote with a another single quote...

So here were the values I was playing with:

rows = arcpy.SearchCursor("Quadrants")
>>> qts = [r.Quotes for r in rows]
>>> del rows
>>> qts
[u"test with quotes' 2", u"test with quotes' 3", u"test with quotes' 4", u"test with quotes' 5"]

To bypass the invalid expression error I had to replace the single quotes with 2 single quotes:

for q in qts:
...     where = '''"Quotes" = '{0}' '''.format(q.replace("'","''"))
...     arcpy.SelectLayerByAttribute_management("Quadrants","NEW_SELECTION", where)

This properly selected everything...That was interesting; I've never had to do that before.

Re: Problems single quotes and variables used in Where Clause

MattGray — Fri, 11 Apr 2014 14:26:31 GMT

Thanks for the quick reply. So far my script is working without failing, it will be interesting to see what the results are.