How can I handle the failure of a DELETE statement to delete any rows?

Question

I wrote this code to delete a row from a table – but if I enter a name which is not in the table, it still outputs "Data Deleted Successfully":

n = input("Enter Student name you want to delete:")
try:
    cur.execute('DELETE FROM studentdata WHERE name=?', (n,))
    print("Data Deleted Successfully")
    conn.commit()
except:
    print("No data found with this name: ")

How can I handle this properly?

Answer 1

Cursor.execute() will only raise an exception if the SQL statement it attempts to execute fails – for example:

>>> cur.execute("This is not SQL")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
sqlite3.OperationalError: near "This": syntax error

or

>>> cur.execute("SELECT * FROM nonexistent_table;")
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
sqlite3.OperationalError: no such table: nonexistent_table

A valid SQL statement which correctly does nothing has succeeded, not failed, and so doesn't raise an exception. Your DELETE statement is correct to do nothing when it doesn't find the provided value for name, so there's no error.

You can find out how many rows were affected by an SQL statement using the Cursor.rowcount attribute. Rewriting your code to make use of the attribute would look something like this:

name = input("Enter Student name you want to delete:")
cur.execute('DELETE FROM studentdata WHERE name = ?;', [name])
if cur.rowcount > 0:
    print("Data Deleted Successfully")
    conn.commit()
else:
    print("No data found with this name:", name)

Note: I've left the commit() where it was in your code … depending on your application, it may be that it should in fact be moved outside the if/else block.