Updating and Deleting operations (SQLite3)

Changes in DB are inevitable as data changes from time to time thus there are provisions to update the DB. The DB update is done using the UPDATE command as in the following query which updates the total employees in North region from 180 to 230 :

UPDATE Sales SET Total_employees = 230 WHERE Region = "NORTH"

The following program does the required update:

import sqlite3

con = sqlite3.connect('employees.db')

cur = con.cursor()

cur.execute('UPDATE Sales SET Total_employees = 230 WHERE Region = "NORTH"')

cur.execute('select * from sales')

In case we need to delete records from the DB we use the DELETE clause as shown below:

DELETE FROM Sales WHERE Region = "NORTH"

The following program does the required delete:

import sqlite3

con = sqlite3.connect('employees.db')

cur = con.cursor()

cur.execute('DELETE FROM Sales WHERE Region = "NORTH"')

cur.execute('select * from sales')

print(cur.fetchall())

To insert the record in to the DB use the INSERT clause as shown in the following program which insert the North region and the total_employees in the Sales table:

import sqlite3

con = sqlite3.connect('employees.db')

cur = con.cursor()

cur.execute('INSERT INTO Sales VALUES ("NORTH",180)')

cur.execute('select * from sales')

print(cur.fetchall())

To remove an entire table from the DB use the DROP command as shown below:

DROP TABLE TableName

Thus to delete the Sales table we can use the following query:

DROP TABLE Sales

The following program deletes the Sales table from employees DB:

import sqlite3

con = sqlite3.connect('employees.db')

cur = con.cursor()

cur.execute('DROP TABLE Sales')

Here I am ending today's discussion. In the next post we'll focus on combining Tables using Joins. Till we meet next keep practicing and learning Python as Python is easy to learn!