DELETE All Rows FROM TABLE - Python sqlite3 Examples

Python - Delete All Rows from Sqlite Table

To delete all rows from Sqlite3 table, you can execute SQL DELETE query.

In this tutorial, we shall learn how to delete all rows or records from a Table of Sqlite Database using sqlite3 library.

Steps to Delete All Rows of Sqlite Table

The detailed steps to delete rows from sqlite3 table are:

  1. Make a connection to sqlite3 database.
  2. Get cursor from the connection.
  3. Execute DELETE FROM table query.

Examples

1. Delete rows from sqlite3 table

In this example, we will learn how to remove or delete all the records from a sqlite3 database table using DELETE FROM TABLE query.

Python Program

import sqlite3

conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()

# Delete all rows from table
c.execute('DELETE FROM students;',);

print('We have deleted', c.rowcount, 'records from the table.')

# Commit the changes to db			
conn.commit()
# Close the connection
conn.close()

Explanation of the Code:

  1. Creating Connection and Cursor: The connection to the database is created using sqlite3.connect('mysqlite.db'), and a cursor object is obtained with conn.cursor().
  2. Executing the DELETE Query: The query 'DELETE FROM students;' deletes all rows from the students table. The execute() method is used to run the query.
  3. Commit Changes: After the query is executed, conn.commit() is called to save the changes to the database.
  4. Closing the Connection: Finally, we close the database connection using conn.close() to free up resources.

2. Delete all rows with a condition

In some cases, you might want to delete rows based on a specific condition. In the example below, we will delete all students with a specific grade.

Python Program

import sqlite3

conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()

# Delete students with grade 7
c.execute('DELETE FROM students WHERE grade = 7;')

print('We have deleted', c.rowcount, 'records from the table.')

# Commit the changes to db			
conn.commit()
# Close the connection
conn.close()

Explanation of the Code:

  1. Condition in DELETE Query: The query 'DELETE FROM students WHERE grade = 7;' deletes only those students whose grade is equal to 7. This is an example of how you can use conditions in your DELETE queries.
  2. Checking Affected Rows: The c.rowcount shows the number of rows that were deleted, and in this case, it will show the number of students with grade 7 that were removed.

3. Delete rows from multiple tables (JOIN query)

If you need to delete rows from multiple related tables, you can perform a delete operation using a join. Below is an example where we delete students from the "students" table where the student ID matches a specific condition from a related table "enrollments".

Python Program

import sqlite3

conn = sqlite3.connect('mysqlite.db')
c = conn.cursor()

# Delete students who are not enrolled in any courses
c.execute('''
DELETE FROM students WHERE id NOT IN (
    SELECT student_id FROM enrollments
);''')

print('We have deleted', c.rowcount, 'records from the table.')

# Commit the changes to db			
conn.commit()
# Close the connection
conn.close()

Explanation of the Code:

  1. JOIN in DELETE Query: The query uses a subquery to identify students who are not enrolled in any courses by comparing the student IDs between the "students" table and the "enrollments" table.
  2. Deleting with JOIN: The outer query deletes rows from the "students" table where the condition in the subquery is met, in this case, students who are not enrolled in any courses.
  3. Commit and Close: As before, after executing the delete query, the changes are committed, and the connection is closed.

Summary

In this tutorial, we learned how to delete rows from an sqlite3 table using the following techniques:

  • Deleting all rows from a table with a basic DELETE FROM query.
  • Using conditions in the DELETE query to delete specific rows.
  • Deleting rows using a JOIN between multiple tables to remove related data.

These methods allow you to efficiently manage and clean up your SQLite tables when using Python.



Python Libraries