SQLite Python: Deleting Data

Summary: in this tutorial, you will learn how to delete data in the 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: SQL (Structured Query Language) (sql)

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: SQL (Structured Query Language) (sql)

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

cur.execute(delete_statement)Code language: SQL (Structured Query Language) (sql)

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: SQL (Structured Query Language) (sql)

In this syntax, the question mark (?) is the 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: SQL (Structured Query Language) (sql)

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

conn.commit()Code language: SQL (Structured Query Language) (sql)

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

conn.close()Code language: SQL (Structured Query Language) (sql)

Here are the complete steps:

conn = sqlite3.connect(database)
cur = conn.cursor()
delete_stmt = 'DELETE FROM sample_table WHERE id = ?'
cur.execute(delete_statement, (id,))
conn.commit()
conn.close()Code language: SQL (Structured Query Language) (sql)

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

import sqlite3

with sqlite3.connect(database) as conn:
    cur = conn.cursor()
    delete_stmt = 'DELETE FROM sample_table WHERE id = ?'
    cur.execute(delete_stmt, (id,))
    conn.commit()Code language: SQL (Structured Query Language) (sql)

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

import sqlite3

try:
    with sqlite3.connect(database) as conn:
        cur = conn.cursor()
        delete_stmt = 'DELETE FROM sample_table WHERE id = ?'
        cur.execute(delete_stmt, (id,))
        conn.commit()
except sqlite3.Error as e:
    print(e)Code language: SQL (Structured Query Language) (sql)

Deleting data from a table example

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

import sqlite3

try:
    with sqlite3.connect('my.db') as conn:
        cur = conn.cursor()
        delete_stmt = 'DELETE FROM tasks WHERE id = ?'
        cur.execute(delete_stmt, (1,))
        conn.commit()
except sqlite3.Error as e:
    print(e)Code language: SQL (Structured Query Language) (sql)

Note that you can comment/ un-comment the function call in the main() function to test each delete function individually.

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 delete data from a table permanently.