topic Re: da.UpdateCursor not deleting Null rows in Python Questions

da.UpdateCursor not deleting Null rows

AlanDodson — Sun, 12 Dec 2021 05:42:10 GMT

I have a fairly simple problem, and I've tried everything I can think of to fix it. I am using a data access update cursor in a larger script to update the value of one field, and if the other field is Null I want to delete the record. The update part works fine, but the delete part does not.

My input data is an annotation feature class created by ArcMap (turn on labels, right-click layer, create annotation). Nothing fancy or complex. The annotation expression is stored in a field named TextString. If no annotation is created for the feature, the field is Null (as a default, all annotation classes created by ArcMap are nullable). When you look at it the attribute table it appears as <Null>.

I cannot use a where clause in this case because of the first update.

The following example should work as far as I can tell, but doesn't.

with arcpy.da.UpdateCursor (in_table="RoadsAnno", field_names=["Status","TextString"]) as cursor:
    for row in cursor:
        row[0] = 0
        cursor.updateRow(row)
        if row[1] is None:
            cursor.deleteRow()
del cursor, row‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

(I also tried replacing "if row[1] is None:" with "if row[1] == None:" but no joy. "is None:" is the most proper way to write this according to my Python resources, though "== None:" still works (in other situations)

So I then tried the following:

with arcpy.da.UpdateCursor (in_table="RoadsAnno", field_names=["Status","TextString"]) as cursor:
    for row in cursor:
        row[0] = 0
        cursor.updateRow(row)    
        if row[1] is None or row[1] =="" or row[1] == "<Null>":
            cursor.deleteRow()‍‍‍‍‍‍
del cursor, row‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

I figured one of those statements would catch the Null value, but it did not.

I then changed the value of a previously Null row in the RoadsAnno feature class to read "DeleteMe" (not in quotes) and ran the following just to test if the cursor was picking up the right field:

with arcpy.da.UpdateCursor (in_table="RoadsAnno", field_names=["Status","TextString"]) as cursor:
    for row in cursor:
        row[0] = 0
        cursor.updateRow(row)    
        if row[1] is None or row[1] =="DeleteMe" or row[1] == "<Null>":
             cursor.deleteRow()‍‍‍‍‍‍
del cursor, row‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

The row containing the TextString attribute "DeleteMe" was deleted. So what's happening with the Null value?

Would anyone be able to help with this? Why doesn't the cursor recognize the Null value in the field? Maybe Arc creates Null values with unseen characters or something that need to be stripped?

EDIT: Do I have to put the updateRow statement after the deleteRow statement? Maybe the cursor is moving to the next row with the update and never gets the chance to delete? Although that wasn't the case for the "DeleteMe" example so perhaps this line of thought is not correct.

Re: da.UpdateCursor not deleting Null rows

DanPatterson_Retired — Thu, 12 Mar 2020 23:22:48 GMT

did you give the last paragraph a try?

Re: da.UpdateCursor not deleting Null rows

DavidPike — Thu, 12 Mar 2020 23:49:46 GMT

for row in cursor:

row[0]...

Re: da.UpdateCursor not deleting Null rows

AlanDodson — Thu, 12 Mar 2020 23:51:57 GMT

Ah yes, that was in the code originally - I was typing out this question from memory at home and forgot that line. I'll amend the question; thanks for the heads up.

Re: da.UpdateCursor not deleting Null rows

AlanDodson — Thu, 12 Mar 2020 23:56:30 GMT

No, I don't have access to the software at home in the evenings but I will try out in the morning. As I said in that paragraph however, the order of the updateRow and deleteRow statements didn't seem to affect the functioning of the last example where I changed the attribute to "DeleteMe"

Re: da.UpdateCursor not deleting Null rows

DanPatterson_Retired — Fri, 13 Mar 2020 04:51:25 GMT

Just a thought... sql

Didn't want to delete records so adjust accordingly with the appropriate cursor

Re: da.UpdateCursor not deleting Null rows

Anonymous User — Sun, 12 Dec 2021 05:42:13 GMT

Regarding your last paragraph, I'd so no because when it is done executing the update it will then proceed to the next line testing for row[1], but... I'd probably check for row[1] first because if you are going to possibly delete the row, updating row[0] wouldn't matter, like this:

...   
if row[1] is None or row[1] == "":
   cursor.deleteRow()
else:
   row[0] = 0
   cursor.updateRow(row) ‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

You could even do something like:

# check if TextString is not null or empty
if row[1] is not None and row[1] != '':
    row[0] = 0
    cursor.updateRow(row)
else:
    cursor.deleteRow()‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

and

row[1] == "<Null>"‍‍‍

is checking for a string, so it is not really doing anything against true nulls.

if row[1] is None or row[1] == ""‍‍‍

is correct- checking for nulls and for empty cells.

Lastly, row is automatically deleted at the end of the for loop too. No need to del it.

Re: da.UpdateCursor not deleting Null rows

DavidPike — Sun, 12 Dec 2021 05:42:16 GMT

It seems the updateRow(row) must be applied a the end to effect the deletion:

with arcpy.da.UpdateCursor (in_table="RoadsAnno", field_names=["Status","TextString"]) as cursor:
    for row in cursor:
        row[0] = 0
        
        if row[1] is None:
            cursor.deleteRow()

        cursor.updateRow(row)
‍‍‍‍‍‍‍‍

Re: da.UpdateCursor not deleting Null rows

AlanDodson — Sun, 12 Dec 2021 05:42:19 GMT

I figured out the problem and it was a stupid amateur mistake that I didn't see until now. I hadn't cleared a selection prior to running the cursor, so the cursor was only running on selected records. Clearing the selection allowed the cursor to run on the entire layer which is what I wanted. Such a silly mistake. Thanks to everyone David Pike Dan Patterson Jeff Kling for their help in organizing the code just a bit better.

Here is the code I ended up using (although my original code would've worked just fine too if the selection was cleared beforehand):

with arcpy.da.UpdateCursor (in_table="RoadsAnno", field_names=["Status","TextString"] as cursor:
    for row in cursor:
        if row[1] is None or row[1] =="":
            cursor.deleteRow()
        else:
            row[0] = 0
            cursor.updateRow(row)
del cursor‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍