- 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.
- 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.
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.
#---- Sample program to delete rows from a SQLITE Table"
# Create a connection object
# Create a cursor object
# The DELETE SQL statement
# Execute the delete statement
# Query the table back
# Printing the remaining rows from the order table after deleting expired orders
[(1, 24521, 10, 89, 'Buy', 'MSFT', 'Ordered'),
(4, 31234, 10, 89.4, 'Buy', 'MSFT', 'Ordered'),
(6, 31234, 10, 90, 'Buy', 'MSFT', 'Executed')]