Python Data Persistence – Deleting Rows

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.