Delete data with SQLite

We've already learned the basics of SQLite3 with Python. By now, we also know how to connect to a database and execute several SQL statements to create tables, select, insert, and update data. In this topic, we will learn how we can delete it. Do you still remember our database model with students and departments? We are going to continue working with it in this topic:

Deleting data

Before deleting anything, we need to:

1. Establish a connection to the SQLite database by creating a connection object using the connect() function;

2. Create a cursor object using the cursor() method of the connection object;

3. Execute a DELETE statement using the execute() method of the cursor object. If you want to pass the arguments to the statement, use a question mark (?) for each argument.

To delete entries from a table, use the DELETE FROM statement. To remove a specific entry, use the WHERE clause. For example, we want to delete the second entry from the student table:

from sqlite3 import Error


try:
    sql_del = cursor.execute("DELETE FROM Student WHERE id = 2")
    print("Total records affected: ", sql_del.rowcount)
    con.commit()
except Error as e:
    print(f"Oops! Something went wrong. Error: {e}")
    # reverse the change in case of error
    con.rollback()


# Total records affected:  1

In the code above, we assign the cursor to the sql_del variable and use its rowcount attribute to return the number of rows affected by the last SQL query executed. If something goes wrong, we have the rollback() function that reverses the changes.

If you execute a DELETE FROM statement without the WHERE clause, it will delete all the entries from the specified table. Finally, to verify that all the records were deleted:

cursor.execute("SELECT * FROM Student")
print(cursor.fetchall())

# []

This prints an empty list confirming that all records have been removed from the table.

Deleting multiple entries

In the first example, we referred to the execute() method of the cursor object to delete a single entry, but sometimes, we need to delete several rows. For example, you want to remove students with the id of 1, 3, and 5 from the Student table:

delete_query = """DELETE FROM Student WHERE id = ?"""
list_ids = [(1,), (3,), (5,)]

cursor.executemany(delete_query, list_ids)
con.commit()

print("Total", cursor.rowcount, "Records deleted successfully")
# Total 3 Records deleted successfully

We've called the executemany() method to delete multiple rows from the SQLite table. Each element of list_ids is nothing but a tuple for each row. Each tuple contains a student's ID. We've created three tuples, so we are deleting three rows.

The DROP statement

You can drop (remove) a table with the DROP statement. You need to be very careful while deleting any existing table because the data cannot be recovered:

cursor.execute("DROP TABLE Student;")

If you try to delete the Student table again, you will get an error saying no such table:

cursor.execute("DROP TABLE Student;")

# Traceback (most recent call last):
# ...
# cursor.execute("DROP TABLE Student;")
# sqlite3.OperationalError: no such table: Student

To resolve this, you can use the IF EXISTS clause along with a DROP statement:

cursor.execute("DROP TABLE IF EXISTS Student;")
# <sqlite3.Cursor at 0x24642d06810>

Conclusion

In this topic, we've learned how to use the Python SQLite3 module to delete objects from your database. This is the outline:

• Use the execute() method with a DELETE FROM TABLE statement to remove all entries from a table;

• To remove a specific entry, add the WHERE clause to the above statement;

• To remove multiple entries, use the executemany() method;

• To get the number of rows affected by the last query, use the rowcount attribute;

• rollback() allows you to roll back the changes;

• The DROP TABLE SQL statement drops an existing table from the SQLite database;

• When the IF EXISTS clause is used along with the DROP TABLE syntax, SQLite will not pop up any error messages if the table does not exist.