topic using update cursor from python script in Python Questions

using update cursor from python script

ShakirAhmed1 — Fri, 31 Jan 2014 11:40:32 GMT

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 "<interactive input>", line 1, in <module>
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" )]

Re: using update cursor from python script

JoshuaChisholm — Sat, 11 Dec 2021 08:52:45 GMT

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

Re: using update cursor from python script

ShakirAhmed1 — Sat, 11 Dec 2021 08:52:47 GMT

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 "<interactive input>", line 1, in <module>
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' )]

Re: using update cursor from python script

JoshuaChisholm — Sat, 11 Dec 2021 08:52:50 GMT

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!

Re: using update cursor from python script

ShakirAhmed1 — Fri, 31 Jan 2014 13:49:33 GMT

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