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

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.

 

#---- 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')]


Copyright 2020 © pythontic.com