topic Re: Problem with SelectByAttribute in Python Questions

Problem with SelectByAttribute

SaidAkif — Tue, 13 Jan 2015 15:16:20 GMT

I made a script that use arcpy.SelectLayerByAttribute_management.

The problem that some of the unique values of the field used in this selection have a special character (') ffor example : Gwich'in Conservation land

I know that the " is the problem because I removed the in from the field and the problem happened but when I removed the ' the script run perfectly

the selection was made automatically. I locate my field first after a function put all unique values in a table. an example of my code is in the txt file attached.

I believe that I have to change something in my expression.

Can you please help me to resolve this issue?

Thanks

Re: Problem with SelectByAttribute

RichardFairhurst — Tue, 13 Jan 2015 15:25:45 GMT

SQL cannot take a string with an apostrophe in it, since the apostrophe is the character used by SQL to quote string values. Your Python code will need to replace the apostrophe with '''' (4 single quotes) to let the SQL interpreter use the apostrophe character literally. The way your code is written makes the SQL parser think you are trying to quote a string and not search for an apostrophe character.

Re: Problem with SelectByAttribute

JoshuaBixby — Fri, 10 Dec 2021 22:43:06 GMT

I encourage you to check out Python string formatting. Not only is using built-in string formatting more Pythonic than concatenating several strings together, I would also argue it is more readable.

First, I would just try restructuring the expression using string formatting to see if that works:

expression = "{} = '{}' AND {} = '{}'".format(SelectCriteriaF,
                                              str(list_UV_CriteriaF),
                                              SplitCriteria,
                                              str(listSplit))

If that doesn't work, then you might have to try string replacement to insert an escape character before the quote. From looking at the code, I am not sure which variable is causing the problem, so I just replaced text on both. This might work:

expression = "{} = '{}' AND {} = '{}'".format(SelectCriteriaF,
                                              str(list_UV_CriteriaF).replace("'","\\'"),
                                              SplitCriteria,
                                              str(listSplit).replace("'","\\'"))

Re: Problem with SelectByAttribute

SaidAkif — Tue, 13 Jan 2015 17:26:11 GMT

Thanks for your quick ranswer

I tried both solution giver by Joshua. Unfortunatly, they did not work.Also I tried to replace ' by "", but the expression don't work

Any other suggestion??

Thanks a lo

Re: Problem with SelectByAttribute

JoshuaBixby — Tue, 13 Jan 2015 17:49:01 GMT

If you print out an example using all the variables along with the error messages, it might help narrow down the issue.

Re: Problem with SelectByAttribute

JamesCrandall — Tue, 13 Jan 2015 18:21:33 GMT

Can you remove the single apostrophe from the source data?

If not, can you replace it with the GRAVE ACCENT or Backtick "`"?

I am unsure about ISO sql standards as it relates to the character, but it does work in a da.SearchCursor as long as it is acceptable to replace the apostrophe in the data.

Re: Problem with SelectByAttribute

SaidAkif — Tue, 13 Jan 2015 19:49:59 GMT

I see in the error message that the problem is related the the value of the field espeacially "Gwich'in Conservation land" unique value. I first remove the in from the field in the original data. but the script still crash. but when I remove the ', the script worked perfectly. I am sure that the problem is '

How just to specify that the interpretor (python) must deal with Gwich<in Conservation land as one black or one unit or I don't know??

Thanks

Re: Problem with SelectByAttribute

JamesCrandall — Tue, 13 Jan 2015 20:11:55 GMT

In your expression try adding an extra ' apostrophe to the string. For example:

expression = """MyField = 'Gwich''in'"""

That is instead of Gwich'in add an extra apostrophe between the "h" and "I" characters: Gwich''in

Re: Problem with SelectByAttribute

JoshuaBixby — Tue, 13 Jan 2015 21:09:26 GMT

Unfortunately without specifics of what the variables are when the code errors, or even the exact error text, those of us trying to help are grasping as straws to some extent.

Re: Problem with SelectByAttribute

JamesCrandall — Fri, 10 Dec 2021 22:43:09 GMT

I just setup an example scenario that fits the OP's issue of attempting to build a query expression that must pickup string values stored with apostrophe's in them.

The sample FeatureClass just has 2 features/rows and I populated a field called "strtest" with a string value: "Gwich'in" just like the OP has. Then I just setup a da.SearchCursor to test out how to overcome the limitations of building an expression to include the apostrophe:

txt = """'Gwich''in'"""
fc = r'H:\Documents\ArcGIS\Default.gdb\TestLayer'
flds = ['OBJECTID','strtest']
sql = """strtest = """ + txt
print sql
with arcpy.da.SearchCursor(fc, flds, sql) as cur:
    for row in cur:
        print("{0}, {1}".format(row[0], row[1]))
sys.exit()

By simply adding an extra apostrophe in the expression, it successfully selects the feature/row.

Re: Problem with SelectByAttribute

SaidAkif — Tue, 13 Jan 2015 21:35:32 GMT

Sorry. Here is the error message

Re: Problem with SelectByAttribute

JoshuaBixby — Fri, 10 Dec 2021 22:43:12 GMT

I revisited the Help for Building a query expression, where it states:

If the string contains a single quote you will first need to use another single quote as an escape character.

In Python, the backslash is the dominant escape character, but there are some additional escape sequences. My sample code above that used text replacement to escape the single quote used the backslash, but ArcGIS wants users to escape a single quote by using another single quote.

The updated code worked for an example I ginned up on my machine:

expression = "{} = '{}' AND {} = '{}'".format(SelectCriteriaF,  
                                             str(list_UV_CriteriaF).replace("'","''"),  
                                             SplitCriteria,  
                                             str(listSplit).replace("'","''"))

Re: Problem with SelectByAttribute

SaidAkif — Wed, 14 Jan 2015 13:17:13 GMT

thanks for your answer

Here is my variable that will contain the value = "Gwich'in Conservation area" : "'" + str(ListUVCriteriaF) + "'"

When I added """ in both side of my variable (""" + "'" + str(...) + "'" + """) it did not work (all thsi line in green so python did not recognize the variable.

I need a way to add the """

Thanks

Re: Problem with SelectByAttribute

JamesCrandall — Wed, 14 Jan 2015 13:36:44 GMT

Your variable needs to be like this in your expression:

Gwich''in

Do you see the two (2) apostrophe's? That's how it must be set. How you deal with setting a variable will be up to you because you will need to check to see an apostrophe exists and if so then double it up. I cannot understand the code you posted.

Re: Problem with SelectByAttribute

SaidAkif — Wed, 14 Jan 2015 15:12:41 GMT

Hi
Indeed, when I replace ' by '', it works. I used the last Joshua code.

Thanks for your help guys