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")

• jbchurchill: I'm thinking you will want to put the second cursor inside the first one so that you check every value in the second table against the single selected row in the first cursor.

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)

• SL_GIS: Nice...I thought about the list but could not for the life of me think of a way to do the 'list comprehension'. I'll have to sample the sql section for performance. I'm just beginning to learn python. Thanks Emil!

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()

• Emil Brundage: Creating a new cursor for each id could become quite slow if the table is large. Using SQL IN statement once is faster.
• ianbroad: @EmilBrundage yeah, you're right, I've updated it.