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 aDELETE
statement that deletes a row from a table. - Always call the
commit()
method of the Connection object to permanently delete data from a table.