Delete the rows of a SQLite table using Python

The Delete statement of SQLite removes one are rows from a table as per the specified clauses. Delete is explain with a Python example.

SQLite Updated 16 Jan 2018 2 min read

Overview

  • The DELETE SQL statement deletes one are more rows of an SQLite table.
  • The number of rows deleted and the order in which the rows are deleted depend upon the following clauses.
    • WHERE
      • LIMIT
    • OFFSET
    • ORDER BY
  • How various clauses of DELETE statement affects the number of rows deleted:
    • WHERE clause accepts a Boolean expression and all the rows for which the Boolean expression evaluates to TRUE, are deleted from the table.
    • LIMIT clause accepts an expression that evaluates to an integer value and “only” that many number of rows will be deleted from the table.
    • OFFSET clause will exclude the first n number of rows specified by it and the remaining rows will be deleted.
    • The ORDER BY clause further complements the LIMIT and OFFSET clauses in effectively deciding the LIMIT and OFFSET, while deleting the rows.

Deleting the rows of a SQLite table from a Python Program

  • The sqlite3 module implements the Python Database Programming specification and provides the interfaces to connect to the SQLite database and perform SQL operations.
  • To delete the rows of an SQLite table from a Python Program the first thing required is to import the sqlite3 module.
  • By calling the connect() method of sqlite3 module and by specifying the database file name as the argument a connection object to a SQLite database can be obtained.
  • Invoking the cursor() method on the connection object returns a cursor object using which any SQL statement supported by SQLite can be executed.
  • A string containing the DELETE statement can be passed as an argument to the execute() method of the cursor object which deletes one or more rows of a SQLite table.

Example

Before the Python Program is executed the contents of the Orders table are given by

python
1|24521|10|89|Buy|MSFT|Ordered

4|31234|10|89.4|Buy|MSFT|Ordered

5|31234|10|89.4|Buy|GOOG|Expired

6|31234|10|90|Buy|MSFT|Executed

The following example Python program deletes expired Orders from an Order Table.

python

#---- Sample program to delete rows from a SQLITE Table"

            import sqlite3

# Create a connection object

            connection             = sqlite3.connect("./main.db")

# Create a cursor object

            cursor                = connection.cursor()

# The DELETE SQL statement

            deleteSQLStatememnt = "DELETE from Orders where Order_Status = \"Expired\""

# Execute the delete statement

            cursor.execute(deleteSQLStatememnt)

# Query the table back

            sqlQuery    = "Select * from Orders"

            cursor.execute(sqlQuery)

            recs = cursor.fetchall()

# Printing the remaining rows from the order table after deleting expired orders

            print(recs)

Output

Output
[(1, 24521, 10, 89, 'Buy', 'MSFT', 'Ordered'),

 (4, 31234, 10, 89.4, 'Buy', 'MSFT', 'Ordered'),

 (6, 31234, 10, 90, 'Buy', 'MSFT', 'Executed')]