using update cursor from python script

Question

Hi I am trying to update a field from File geodatabase using update cursor using where clause. but the search string has quote. I tried all different way to use where clause but did not work. I really apprecaite if anyone can help me to solve this problem or work around. Thanks =============================== Here is the code that I am using from python win. =============================== >>>my_name = "New Horizon's place.JPG" >>> where = Name = '"%s"' %my_name >>> cursor2 = arcpy.UpdateCursor(my_feature,where) Traceback (most recent call last): File "", line 1, in File "C:\Program Files (x86)\ArcGIS\Desktop10.1\arcpy\arcpy\__init__.py", line 1165, in UpdateCursor return gp.updateCursor(dataset, where_clause, spatial_reference, fields, sort_fields) File "C:\Program Files (x86)\ArcGIS\Desktop10.1\arcpy\arcpy\geoprocessing\_base.py", line 364, in updateCursor self._gp.UpdateCursor(*gp_fixargs(args, True))) RuntimeError: ERROR 999999: Error executing function. An invalid SQL statement was used. An invalid SQL statement was used. [my_feature] An invalid SQL statement was used. [SELECT * FROM my_feature WHERE ( "New Horizon's place.JPG" )]

AhmedShakir · Answer

Thanks  a lot Joshua Chisholm for your help. it worked.

JoshuaChisholm · Answer

Oh, sorry, I see it now.

You need to replace any single quotes (') with two single quotes (''). This is how SQL understands single quotes.

You can manually change this in your script ("New Horizon's place.JPG" to "New Horizon''s place.JPG") or add a replace line:my_name = "New Horizon's place.JPG"
my_name = my_name.replace("\'","\'\'")
my_feature = r"C:\Path\To\File.shp"
where = '\"Name\" LIKE \'%s\'' %my_name # here the my_name is the search string
cursor2 = arcpy.UpdateCursor(my_feature,where)

Hope it works!

AhmedShakir · Answer

Hello Shakir, The where statement is the problem. Can you explain exactly what you are trying to achieve with the where statement. Here are a few things to note: 1) A SQL (where) must be passed as a string (text) in python. This means it has to have quotes around it. You may also need to add some Backslashs (\) to quote marks. This tells python to use them only as a character and not defining the start/end of a string. 2) When building a SQL statement with wildcards ('%') you must use the 'LIKE' condition, not the '=' condition. Try this out: my_feature = r"C:\Path\To\File.shp" where = '\"Name\" LIKE \'%s\'' #produces this: "Name" LIKE '%s' cursor2 = arcpy.UpdateCursor(my_feature,where) Let me know how it goes. Good Luck! ~Josh Thanks for your reply but got following error my_name = "New Horizon's place.JPG" my_feature = r"C:\Path\To\File.shp" #where = '\"Name\" LIKE \'%s\'' #produces this: "Name" LIKE '%s' where = '\"Name\" LIKE \'%s\'' %my_name # here the my_name is the search string cursor2 = arcpy.UpdateCursor(my_feature,where) Traceback (most recent call last): File "", line 1, in File "C:\Program Files (x86)\ArcGIS\Desktop10.1\arcpy\arcpy\__init__.py", line 1165, in UpdateCursor return gp.updateCursor(dataset, where_clause, spatial_reference, fields, sort_fields) File "C:\Program Files (x86)\ArcGIS\Desktop10.1\arcpy\arcpy\geoprocessing\_base.py", line 364, in updateCursor self._gp.UpdateCursor(*gp_fixargs(args, True))) RuntimeError: ERROR 999999: Error executing function. An invalid SQL statement was used. An invalid SQL statement was used. [my_feature] An invalid SQL statement was used. [SELECT * FROM my_feature WHERE ( "Name" LIKE 'New Horizon's place.JPG' )]