SQLite Python: Deleting Data

Summary: in this tutorial, you will learn how to delete data in an SQLite database from a Python program using the sqlite3 module.

How to delete data from a table using Python

To delete data in the SQLite database from a Python program, you follow these steps:

First, create a database connection to an SQLite database file by calling the connect() function of the sqlite3 module:

conn = sqlite3.connect(database)Code language: Python (python)

The connect() function returns a Connection object representing a connection to a specified SQLite database.

Next, create a cursor object from Connection object by calling the cursor() method:

cur = conn.cursor()Code language: Python (python)

Then, execute a DELETE statement by calling the execute() method of the Cursor object:

cur.execute(delete_statement)Code language: Python (python)

If you want to bind the arguments to the DELETE statement, use a question mark (?) for each argument. For example:

DELETE FROM table_name
WHERE id = ?Code language: Python (python)

In this syntax, the question mark (?) acts as a placeholder that will be replaced by an id. In this case, you need to pass the second argument as a tuple that includes the id to the execute() method:

cur.execute(delete_statement, (id,))Code language: Python (python)

After that, call the commit() method of the Connection object to apply the change to the database permanently:

conn.commit()Code language: Python (python)

Finally, close the database connection by calling the close() method of the Connection object:

conn.close()Code language: Python (python)

Here are the complete steps:

sql = 'DELETE FROM sample_table WHERE id = ?'
conn = sqlite3.connect(database)
cur = conn.cursor()
cur.execute(sql, (id,))
conn.commit()
conn.close()Code language: Python (python)

If you use a context manager, you don’t need to explicitly close the database connection. The code will be like this:

import sqlite3

sql = 'DELETE FROM sample_table WHERE id = ?'
with sqlite3.connect(database) as conn:
    cur = conn.cursor()
    cur.execute(sql, (id,))
    conn.commit()Code language: Python (python)

An error may occur when deleting the data. To handle it, you can use the try-except statement:

import sqlite3

sql = 'DELETE FROM sample_table WHERE id = ?'

try:
    with sqlite3.connect(database) as conn:
        cur = conn.cursor()
        cur.execute(delete_stmt, (id,))
        conn.commit()
except sqlite3.OperationalError as e:
    print(e)Code language: Python (python)

Deleting data from a table example

The following program illustrates how to delete a row from the tasks table:

import sqlite3

sql = 'DELETE FROM tasks WHERE id = ?'

try:
    with sqlite3.connect('my.db') as conn:
        cur = conn.cursor()
        cur.execute(sql, (1,))
        conn.commit()
except sqlite3.OperationalError as e:
    print(e)Code language: Python (python)

Summary

• Call the execute() method of a Cursor object to run a DELETE statement that deletes a row from a table.
• Always call the commit() method of the Connection object to permanently delete data from a table.