Delete records in feature class that have same ID in CSV table using arcpy cursors

Question

So I have a csv table with records that have an ID. I want to find the same IDs in a feature class/shapefile and delete those records in the feature class/shapefile using Python.

I've messed with add.join and relates without a good result so I figured I'd move to cursors. I've setup two search cursors to test the result but I don't think the logic is quite right so I'm posing my question here.

Here's a code snippet. The second cursor will eventually change to Update.Cursor using delete row but not until I get the result correct first.

import arcpy

tc = "d:\\temp\\tc.shp"
changes = "d:\\temp\\changes.csv"

with arcpy.da.SearchCursor(changes,"RECORDNUM_AFTER") as changecur:
    for row in changecur:
        changeIDs=row

tcur = arcpy.SearchCursor(tc, fields="RECORDNUM")
for row in tcur:
    if row in changeIDs:
        print row.getValue("RECORDNUM")

Answer 1

Store all of your csv IDs in a python list. Then iterate and delete with an update cursor if the leak number is in the list:

import arcpy

tc = "d:\\temp\\tc.shp"
changes = "d:\\temp\\changes.csv"

#List comprehension for IDs
ids = set ([i for i, in arcpy.da.SearchCursor
       (changes, "RECORDNUM_AFTER") if i != None])

#update cursor to delete rows
with arcpy.da.UpdateCursor (tc, "RECORDNUM") as cursor:
    for recordnum, in cursor:
        if recordnum in ids:
            cursor.deleteRow ()

del cursor

Depending on the number of ids, an sql expression may be faster:

import arcpy

tc = "d:\\temp\\tc.shp"
changes = "d:\\temp\\changes.csv"

#List comprehension for IDs
ids = set ([i for i, in arcpy.da.SearchCursor
       (changes, "RECORDNUM_AFTER") if i != None])

#sql expression
delFld = arcpy.AddFieldDelimiters (tc, "RECORDNUM")
idStr = ", ".join (map (str, ids))
sql = "{0} IN ({1})".format (delFld, idStr)
#filter and delete
arcpy.MakeFeatureLayer_management (tc, "lyr", sql)
arcpy.DeleteRows_management ("lyr")
arcpy.Delete_management ("lyr")

If it's a text field you'll need to tweak the code a bit:

idStr = "', '".join (map (str, ids))
sql = "{0} IN ('{1}')".format (delFld, idStr)

Answer 2

This uses the csv module to parse the CSV file, and add the values to a list. It then loops through the list and creates a query on the UpdateCursor for that id and deletes the row if it exists.

Look at the comments in the code to see which parts you might need to alter. The code below assumes the RECORDNUM field type is integer.

import arcpy
import csv

fc = "d:\\temp\\tc.shp"
csv_data = "d:\\temp\\changes.csv"

num_list = []
with open(csv_data, "r") as csv_file:
    reader = csv.reader(csv_file)

    #skips the header, remove this if no header info present
    reader.next()

    #change row[0] to whatever column you need, row[0] is first column
    for row in reader:
        num_list.append(int(row[0]))

num_tuple = str(tuple(num_list))

field = arcpy.AddFieldDelimiters(fc, "RECORDNUM")
query = """{0} IN {1}""".format(field, num_tuple)

with arcpy.da.UpdateCursor(fc, ("RECORDNUM"), query) as cursor:
     for row in cursor:
         cursor.deleteRow()