Deleting Duplicates from Multiple Tables in a GeoDatabase

Question

I'm having trouble deleting duplicates from multiple tables in a geodatabase. I have a field "facility_id" which should be unique, but I have multiple entries and need to delete all but one. I've started a python script to delete the duplicates but I'm not sure how to finish it. I tried a for loop to find all facility_ids in the values list and check for membership in the duplicate values list, but when I use cursor.deleteRow, nothing happens.

import arcpy, os
from arcpy import env

targetDB = r"C:\data\Temp\436e0250ef1748af92e7f1f75b5a0deb.gdb"
arcpy.env.workspace = targetDB
for table in arcpy.ListTables():
newTable = arcpy.Describe(table).basename
    if "ATTACH" not in newTable:
        with arcpy.da.UpdateCursor(newTable, "facility_id") as cursor:
            values = [r[0] for r in cursor]
            duplicates = [value for value in values if values.count(value) > 1]
            print "Original Table Values: " + str(newTable) + ": " + str(values)
            print "Duplicate Table Values: " + str(newTable) + ": " + str(duplicates)
            if values in duplicates:
                cursor.deleteRow()

output:

Original Table Values: ElecConduit: [u'PS145', u'PS022', u'PS022', u'PS108', u'PS134', u'PS999']

Duplicates Table Values: ElecConduit: [u'PS022', u'PS022']

Original Table Values: ElecLighting: [u'PS108', u'PS108', u'PS108']

Duplicates Table Values: ElecLighting: [u'PS108', u'PS108', u'PS108']

Answer 1

I think it's great that you're using list comprehensions as part of this task, but if I were to run into this same situation, I'd probably want to dumb the script down to a more explicit, step-by-step state. The following changes should delete all duplicate rows for any given 'facility_id' past the first occurrence of a unique 'facility_id' value, across all tables not named "ATTACH" in your target .gdb:

import arcpy, os
from arcpy import env

targetDB = r"C:\data\Temp\436e0250ef1748af92e7f1f75b5a0deb.gdb"
arcpy.env.workspace = targetDB
duplicate_check_list = [] # this is a global list recording known facility_id values for duplicate value checking purposes
for table in arcpy.ListTables():
    newTable = arcpy.Describe(table).basename
        if "ATTACH" not in newTable:
            with arcpy.da.UpdateCursor(newTable, "facility_id") as cursor:
                for row in cursor:
                    if row[0] in duplicate_check_list: # check the current facility_id against the values already present in the list
                        cursor.deleteRow()
                    else:
                        duplicate_check_list.append(row[0])

Best Luck with this.