Python Data Persistence – Deleting Rows
Similarly, we can programmatically delete a certain record from a table. This operation is also more often than not conditional. Hence, the ‘WHERE’ clause appears in the parameterized DELETE query. Following script (deleteqry.py) deletes row belonging to the user-specified product.
Example
import sqlite3
conn=sqlite3.connect('mydb.sqlite')
nm=input('enter product to delete:1)
qry='delete from Products where name=?'
cur=conn.cursor( )
try:
cur.execute(qry, (nm,))
print ('record deleted')
conn.commit ( )
except:
print ('error in delete operation')
conn.rollback( )
conn.close( )To delete the user input product, run the above script from the command prompt.
E:\python37>python deleteqry.py enter product to delete: Printer record deleted
Execute select query in SQLite console to verify that deleted product doesn’t appear in the list.
sqlite> select * from products; ProductID   Name     Price ---------    ---------   --------   1        Laptop    25000   2          TV      32000   3       Router      2000   4        Scanner    5000   6        Mobile     15000
The next section explains how to programmatically retrieve records from a table.