Delete The Rows Of A SQLite Table Using Python

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

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

Output: