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
- WHERE
- 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
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|ExecutedThe following example Python program deletes expired Orders from an Order Table.
#---- 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
[(1, 24521, 10, 89, 'Buy', 'MSFT', 'Ordered'),
(4, 31234, 10, 89.4, 'Buy', 'MSFT', 'Ordered'),
(6, 31234, 10, 90, 'Buy', 'MSFT', 'Executed')]