Last modified: Sep 22, 2026

Delete All Rows from SQLite Table in Python

When working with databases in Python, you may often need to remove all records from a table. This is especially common during testing or when resetting data. In this article, we will explain how to delete all rows from a SQLite table using Python. We will cover the correct syntax, provide real code examples, and show expected outputs.

SQLite is a lightweight, file-based database that integrates seamlessly with Python via the built-in sqlite3 module. One of the most common operations is clearing out a table. There are two main approaches:

Using the DELETE FROM SQL command.
Using the DROP TABLE and recreating the table.

We will focus on the first method, which is safer and more flexible.

Why Delete All Rows?

You might want to delete all rows for several reasons:

Testing: Clearing test data between runs.
Data Reset: Starting fresh without losing table structure.
Maintenance: Cleaning up outdated records.

Step-by-Step Guide to Deleting All Rows

Before deleting any data, always ensure you have a backup. Let’s walk through the process:

1. Connect to the Database

First, connect to your SQLite database using the sqlite3.connect() function.


import sqlite3

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

2. Create a Sample Table (Optional)

If the table doesn't exist, create one for demonstration purposes.


# Create a table
cursor.execute('''
CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER
)
''')

# Insert sample data
cursor.executemany("INSERT INTO users (name, age) VALUES (?, ?)", [
    ('Alice', 30),
    ('Bob', 25),
    ('Charlie', 35)
])
conn.commit()

3. View Existing Data

Check the current data before deletion.


# Fetch and print all rows
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
for row in rows:
    print(row)


(1, 'Alice', 30)
(2, 'Bob', 25)
(3, 'Charlie', 35)

4. Delete All Rows

Use the DELETE FROM statement to remove all rows from the table.


# Delete all rows from the table
cursor.execute("DELETE FROM users")
conn.commit()
print("All rows deleted successfully.")


All rows deleted successfully.

5. Verify Deletion

Confirm that all rows have been removed.


# Check if any rows remain
cursor.execute("SELECT * FROM users")
rows = cursor.fetchall()
print("Rows after deletion:", rows)


Rows after deletion: []

Important Notes

Commit Changes: Always call conn.commit() after executing a DELETE statement to save changes.
Dangerous Command: The DELETE FROM command without a WHERE clause deletes all rows permanently. Be cautious.
Auto-increment Reset: If your table uses AUTOINCREMENT, the counter will not reset automatically. To reset it, use DELETE FROM sqlite_sequence WHERE name='table_name'.

Alternative: Drop and Recreate Table

If you want to completely reset the table (including auto-increment values), drop and recreate it:


# Drop the table
cursor.execute("DROP TABLE IF EXISTS users")

# Recreate the table
cursor.execute('''
CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER
)
''')
conn.commit()
print("Table dropped and recreated.")

Best Practices

Always back up your database before bulk deletions.
Use IF EXISTS when dropping tables to avoid errors.
Wrap operations in try...except blocks for error handling.

Conclusion

Deleting all rows from a SQLite table in Python is straightforward using the DELETE FROM SQL command. By following the steps outlined above, you can safely and efficiently clear your table data. Always remember to commit your changes and verify the deletion. Whether you are testing or resetting data, this method ensures clean and predictable results.

For more advanced SQLite operations, check out our guides on Python SQLite Example: Simple Database Operations and Python SQLite Create Database Guide.

Delete All Rows from SQLite Table in Python