SQLite Python: Deleting Data
Summary: this tutorial shows you how to delete data in the SQLite database from a Python program using the sqlite3 module.
In order to delete data in the SQLite database from a Python program, you use the following steps:
- First, establish a connection the SQLite database by creating a
Connectionobject using theconnect()function. - Second, to execute a
DELETEstatement, you need to create aCursorobject using thecursor()method of theConnectionobject. - Third, execute the
DELETEstatement using theexecute()method of theCursorobject. In case you want to pass the arguments to the statement, you use a question mark (?) for each argument.
The following create_connection() function establishes a database connection to an SQLite database specified by a database file name:
def create_connection(db_file):
""" create a database connection to the SQLite database
specified by the db_file
:param db_file: database file
:return: Connection object or None
"""
conn = None
try:
conn = sqlite3.connect(db_file)
except Error as e:
print(e) return conn
Code language: Python (python)
The following delete_task() function deletes a task in the tasks table by id.
def delete_task(conn, id):
"""
Delete a task by task id
:param conn: Connection to the SQLite database
:param id: id of the task
:return:
"""
sql = 'DELETE FROM tasks WHERE id=?'
cur = conn.cursor()
cur.execute(sql, (id,))
conn.commit()Code language: Python (python)
The following delete_all_tasks() function deletes all rows in the tasks table.
def delete_all_tasks(conn):
"""
Delete all rows in the tasks table
:param conn: Connection to the SQLite database
:return:
"""
sql = 'DELETE FROM tasks'
cur = conn.cursor()
cur.execute(sql)
conn.commit()Code language: Python (python)
This main() function calls the create_connection() function and the delete_task() function to delete the task with id 2 from the tasks table:
def main():
database = r"C:\sqlite\db\pythonsqlite.db" # create a database connection
conn = create_connection(database)
with conn:
delete_task(conn, 2);
# delete_all_tasks(conn);
if __name__ == '__main__':
main()
Code language: Python (python)
Here is the full program:
import sqlite3
from sqlite3 import Errordef create_connection(db_file):
""" create a database connection to the SQLite database
specified by the db_file
:param db_file: database file
:return: Connection object or None
"""
conn = None
try:
conn = sqlite3.connect(db_file)
except Error as e:
print(e)
return conn
def delete_task(conn, id):
"""
Delete a task by task id
:param conn: Connection to the SQLite database
:param id: id of the task
:return:
"""
sql = 'DELETE FROM tasks WHERE id=?'
cur = conn.cursor()
cur.execute(sql, (id,))
conn.commit()
def delete_all_tasks(conn):
"""
Delete all rows in the tasks table
:param conn: Connection to the SQLite database
:return:
"""
sql = 'DELETE FROM tasks'
cur = conn.cursor()
cur.execute(sql)
conn.commit()
def main():
database = r"C:\sqlite\db\pythonsqlite.db"
# create a database connection
conn = create_connection(database)
with conn:
delete_task(conn, 2);
# delete_all_tasks(conn);
if __name__ == '__main__':
main()
Code language: Python (python)
Note that you can comment/ un-comment the function call in the main() function to test each delete function individually.
In this tutorial, you have learned how to delete data in the SQLite database from a Python program using the sqlite3 module.