0

I am writing a function to delete rows based on a certain value. The first code block below doesn't work; the second code block does work. I don't understand why I have to add the "if" clause to get it to work. Isn't the point of having a where clause (my sql variable) to select only the fields and rows that meet that condition?

Doesn't work, curious why?

def delRows(shp_in, fieldname, Val):
    """
    Delete rows based on values in specific fields
    :shp_in:            input shapefile for which rows are to be deleted
    :fieldname:         fieldname for finding value to delete
    :Val:               trigger value for row deletion
    """
    import arcpy
    sql = "'" + fieldname + "' = " + "'" + str(Val) + "'"
    print sql
    with arcpy.da.UpdateCursor(shp_in, [fieldname], sql) as cursor:
        for row in cursor:
            cursor.deleteRow()

#

Does work

def delRows(shp_in, fieldname, Val):
    """
    Delete rows based on values in specific fields
    :shp_in:            input shapefile for which rows are to be deleted
    :fieldname:         fieldname for finding value to delete
    :Val:               trigger value for row deletion
    """
    import arcpy
    sql = "'" + fieldname + "' = " + "'" + str(Val) + "'"
    print sql
    with arcpy.da.UpdateCursor(shp_in, [fieldname], sql) as cursor:
        for row in cursor:
            if row[0] == Val:
                cursor.deleteRow()

#

Edit Here's the print-out of the sql statement as coded in both of the above:

'Site' = '0'

#

1
  • 1
    could you show the content of the sql string?
    – JGH
    Oct 30, 2018 at 14:27

1 Answer 1

1

the SQL query is likely wrong and all rows are returned, so the if check actually do the filtering.

In the SQL query, the field name should not be quoted. Also if the value if numeric, it should also not be quoted. This doc gives more details.

sql = fieldname + " = " + str(delVal) 
6
  • I changed it to: sql = fieldname + " = " + "'" + str(delVal) + "'"
    – a11
    Oct 30, 2018 at 14:53
  • print out is: Site = '0' RuntimeError: An invalid SQL statement was used.
    – a11
    Oct 30, 2018 at 14:55
  • if it is a numerical field, the value should also not be quoted (see the doc)
    – JGH
    Oct 30, 2018 at 14:56
  • I tried: sql = fieldname + " = " + delVal
    – a11
    Oct 30, 2018 at 14:58
  • 1
    sql = fieldname + " = " + str(delVal)
    – JGH
    Oct 30, 2018 at 15:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.