UpdateCursor RuntimeError: An invalid SQL statement was used
There are several issues with your code:
- As @Vince points out your list of fields that you supply to the cursor is incorrect
- You are deleting the cursor within the loop
- You are using the
with
statement so there is no need for adel
- Your test for
None
does not need you to then assign Nothing back to it. - Indentation is EVERYTHING in python and currently your code is incorrectly indented. Now that may be you simply pasting it in wrong, but how are we to know that? If it was down to that then you need to make sure you don't do that as you are wasting everyone's time...
- You should get into the habit of commenting your code, it helps you and others understand what is going on.
The correct code would be:
with arcpy.da.UpdateCursor(inFeatures, ["TDS_Name"]) as change_cursor:
for row in change_cursor:
if row[0] != None:
# If row is not Nothing then replace first hyphen
row[0] = row[0].replace("-", " ",1)
change_cursor.updateRow(row)
You dont need to iterate over rows that are None/NULL
or doesnt contain -
so you can limit the rows returned by the cursor using a where_clause
.
And when using with
there is no need to delete the cursor:
sql = """{0} IS NOT NULL AND {0} LIKE '%-%'""".format(arcpy.AddFieldDelimiters(datasource=inFeatures, field='TDS_Name'))
with arcpy.da.UpdateCursor(in_table=inFeatures, field_names='TDS_Name', where_clause=sql) as cursor:
for row in cursor:
row[0] = row[0].replace('-',' ', 1)
cursor.updateRow(row)
There are few changes that should be made:
# (string in array)
with arcpy.da.UpdateCursor(inFeatures, ["TDS_Name"]) as change_cursor:
for x in change_cursor:
# (use 'in' operator, skipping x[0] = x[0] no-op)
if '-' in x[0]:
# (leverage replace, as in CalculateField)
x[0] = x[0].replace('-',' ', 1)
# (only update when changed)
change_cursor.updateRow(x)
# (remove unnecessary del [with handles this])