5
with arcpy.da.UpdateCursor(inFeatures, "[TDS_Name]") as change_cursor:
for x in change_cursor:
 if x[0] == None:
 x[0] = x[0]
 else:
 x[0] = x[0].translate("-F", " ")
 
 change_cursor.updateRow(x)
 del change_cursor

RuntimeError: An invalid SQL statement was used. [SELECT [TDS_Name], OBJECTID FROM Test_Pit_2]

My purpose: Replace "-" with a " ", but only the first "-" in the ["TDS_Name"] field.

examples in TDS_Name: 1000-XX5, 4000-XX3, 3000-XX35-1

What they should be in the end: 1000 XX5, 4000 XX3, 3000 XX35-1

I used this line here which worked, but if I ran the script again would, unfortunately, remove the second "-":

arcpy.CalculateField_management(inFeatures, "TDS_Name", "!TDS_Name!.replace('-',' ', 1)", "PYTHON3", '', "TEXT")

I have imported ArcPy, and my workspace was set and the features showed up with the Exists function.

I tried using .replace in the UpdateCursor, but it didn't work and there is obviously an issue with my setup of the cursor in the first place.

Aaron
52k30 gold badges161 silver badges326 bronze badges
asked Dec 28, 2020 at 17:31
2
  • 3
    In this case, the error message is literally correct. The brackets are not valid SQL, and therefore you need to pass in an array of strings, not a string with brackets -- ["TDS_Name"] not "[TDS_Name]" . Commented Dec 28, 2020 at 17:40
  • How can I like your comment/place it as the answer? Thank you! Commented Dec 28, 2020 at 17:45

3 Answers 3

9

There are several issues with your code:

  1. As @Vince points out your list of fields that you supply to the cursor is incorrect
  2. You are deleting the cursor within the loop
  3. You are using the with statement so there is no need for a del
  4. Your test for None does not need you to then assign Nothing back to it.
  5. 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...
  6. 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)
answered Dec 28, 2020 at 17:50
1
  • In response to: 2/3. Apologies there, that's just incompetence, as well as the indentation. Jupyter made sure of that after I fixed the string issue that @Vince brought up. 4. I was getting an error when looking to replace values for some of the NULL fields in my data. I took that from some other forum post as a way to avoid that error. Commented Dec 28, 2020 at 17:54
6

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)
answered Dec 28, 2020 at 17:42
2
  • 1
    Hi @BERA, As always you have slick code! When you filter a cursor with an SQL where clause, do you generally observe any performance boost? Commented Jan 3, 2021 at 20:02
  • :). I have never measured any time so I dont know. I mostly dont bother with a where_clause since the cursor is so fast anyway at least for by data (a few thousands of rows at most) Commented Jan 4, 2021 at 8:13
5

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])
answered Dec 28, 2020 at 17:56
2
  • This is what I ended up using. Anyone checking out this thread should consider this answer as well as the one mentioning my mistakes. Thank you @Vince Commented Dec 29, 2020 at 1:19
  • 1
    Thank you @BERA Commented Dec 30, 2020 at 16:23

Your Answer

Draft saved
Draft discarded

Sign up or log in

Sign up using Google
Sign up using Email and Password

Post as a guest

Required, but never shown

Post as a guest

Required, but never shown

By clicking "Post Your Answer", you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.