How to Delete Data from SQLite Using Python: Simple Guide

To delete data from an SQLite database using Python, use the DELETE FROM SQL statement with a WHERE clause inside a cursor's execute() method. Always commit the changes with connection.commit() to save the deletion.

Syntax

The basic syntax to delete data from an SQLite table in Python is:

• DELETE FROM table_name: Specifies the table to delete from.
• WHERE condition: Filters which rows to delete. Without this, all rows will be deleted.
• cursor.execute(sql_query): Runs the SQL command.
• connection.commit(): Saves the changes to the database.

python

cursor.execute("DELETE FROM table_name WHERE condition")
connection.commit()

Example

This example shows how to delete a user with a specific ID from a table named users. It connects to the database, deletes the row where id equals 2, commits the change, and closes the connection.

python

import sqlite3

# Connect to SQLite database (or create it)
connection = sqlite3.connect('example.db')
cursor = connection.cursor()

# Create table and insert sample data (for demonstration)
cursor.execute('CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT)')
cursor.execute('INSERT OR IGNORE INTO users (id, name) VALUES (1, "Alice")')
cursor.execute('INSERT OR IGNORE INTO users (id, name) VALUES (2, "Bob")')
cursor.execute('INSERT OR IGNORE INTO users (id, name) VALUES (3, "Charlie")')
connection.commit()

# Delete user with id = 2
cursor.execute('DELETE FROM users WHERE id = ?', (2,))
connection.commit()

# Verify deletion
cursor.execute('SELECT * FROM users')
rows = cursor.fetchall()
for row in rows:
    print(row)

# Close connection
connection.close()

Output

(1, 'Alice')
(3, 'Charlie')

Common Pitfalls

Common mistakes when deleting data from SQLite using Python include:

• Forgetting to use WHERE clause, which deletes all rows.
• Not calling connection.commit(), so changes are not saved.
• Not using parameterized queries, which can cause SQL injection risks.
• Closing the connection before committing changes.

python

import sqlite3

connection = sqlite3.connect('example.db')
cursor = connection.cursor()

# Wrong: Deletes all rows because WHERE clause is missing
# cursor.execute('DELETE FROM users')

# Right: Use WHERE clause and parameterized query
cursor.execute('DELETE FROM users WHERE id = ?', (3,))
connection.commit()
connection.close()

Quick Reference

Remember these key points when deleting data from SQLite using Python:

• Always use WHERE to target specific rows.
• Use parameterized queries to avoid SQL injection.
• Call connection.commit() after DELETE to save changes.
• Close the connection after finishing database operations.

Key Takeaways

Use the DELETE FROM statement with a WHERE clause to remove specific rows.

Always commit changes with connection.commit() to save deletions.

Use parameterized queries to keep your database safe from SQL injection.

Avoid deleting all data by always including a WHERE condition unless intentional.

Close the database connection after completing your operations.