Problem with SelectByAttribute

[SaidAkif]

Hi

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

[RichardFairhurst]

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.

[JoshuaBixby]

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("'","\\'"))

[SaidAkif]

Hi

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

[JoshuaBixby]

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

[JamesCrandall]

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.

[SaidAkif]

Hi

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

[JamesCrandall]

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

[JoshuaBixby]

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.

[JamesCrandall]

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.