topic Re: Delete all table records with special character, # , ' in Python Questions

Delete all table records with special character, # , '

2Quiker — Sat, 11 Dec 2021 11:10:34 GMT

I have a feature class table that i need to delete the rows with special characters of # , ' or other certain special characters. I have tried the following but nothing happens so I am not coding it correctly, I am in need of some help please.

A2 = "ArcSDE Personal Server/featureclass"

# sql SubName LIKE '%#'
with arcpy.da.UpdateCursor(A2 , ["OBJECTID","Field"]) as cursor:  
    for row in cursor:      
        if row[1] in ["'", "#"]:
            print ("Deleting record: 'OBJECTID' = {}".format(row[0])) 
            cursor.deleteRow()

‍‍‍‍‍‍‍‍‍

Re: Delete all table records with special character, # , '

JoeBorgione — Mon, 18 May 2020 17:17:02 GMT

Does Field only contain a special character or are there other characters there as well? Your line #6 is defined such that the contents of the field is only a single quote or a hash tag. If you have other characters in that field you may want to consider some regular expression searching. Take a look here: Program to check if a string contains any special character - GeeksforGeeks

Re: Delete all table records with special character, # , '

2Quiker — Mon, 18 May 2020 17:33:26 GMT

It does but I am looking to delete rows with just hash tag and apostrophe.

Re: Delete all table records with special character, # , '

JoeBorgione — Sat, 11 Dec 2021 11:10:37 GMT

Then your line 6 is backwards. See if this works:

if "'" or "#" in row[1]:
   do something‍‍

Re: Delete all table records with special character, # , '

JoshuaBixby — Mon, 18 May 2020 18:06:33 GMT

Joe, that won't work. It would have to be written

if "'" in row[1] or "#" in row[1]:‍‍‍

Re: Delete all table records with special character, # , '

JoeBorgione — Mon, 18 May 2020 18:08:28 GMT

yeah. that sounds more better!

Re: Delete all table records with special character, # , '

JoshuaBixby — Mon, 18 May 2020 18:10:34 GMT

A different approach would be to use a SQL WHERE clause on your update cursor so that all records already meet the criteria, then you can just delete them without having to check them in Python.

sql = "FIELD LIKE '%'%' or FIELD LIKE '%#%'"

Re: Delete all table records with special character, # , '

BlakeTerhune — Sat, 11 Dec 2021 11:10:40 GMT

I know your question has already been answered but I couldn't resist sharing a couple little tricks I use. First, consider using the @ field name token options to access Esri fields more reliably.

with arcpy.da.UpdateCursor(A2 , ["OID@", "Field"]) as cursor:
    for oid, field in cursor:
        if "'" in field or "#" in field:
            print ("Deleting record: 'OBJECTID' = {}".format(oid))‍‍‍‍

Also used in the sample above is a method called sequence unpacking to easily assign variable names to your cursor row indexes (field values). This works best with only a few fields.

If you have many fields in your cursor, you can use this helper function to make a dictionary of the values so it's cleaner to access them instead of using the row index like row[1].

with arcpy.da.UpdateCursor(A2 , ["aField", "anotherField", "moreField"]) as cursor:
    for row in row_as_dict(cursor, cursor.fields):
        if "'" in row["aField"] or "#" in row["anotherField"]:
            print ("Deleting record: 'moreField' = {}".format(row["moreField"]))

def row_as_dict(data, fields):
    for row in data:
        yield OrderedDict(zip(fields, row))‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍