How to Delete Data in Python using SQLite

Delete Data from Database in Python using SQLite

python-video-tutorials

In this tutorial, we will learn how to execute DELETE Query in Python program to delete the data of SQLite’s table.

deleting-data-from-sqlite-table-in-python

This tutorial mainly focuses on:

Delete a single row of SQLite table in Python
Delete a multiple rows of SQLite table in Python
Parameterized query to delete records from SQLite table in Python
Delete a single column of SQLite table in Python
Delete multiple columns of SQLite table in Python
Query to delete records in bulk.
Delete all rows and all columns of SQLite table in Python

Our database, and table present inside the database looks like:

deleting-data-from-database-in-python-sqlite

database name: “data.db”
table name: “users”
table columns: “id“, “name“, “age“, “gender”

Note: We have four records in our database table. We will perform operations on these records.

1). Single Row – Deleting Records from Database Table in SQLite in Python

#Importing Database Library
import sqlite3

# Database Connectivity
try:
    con = sqlite3.connect("data.db")
    cursor = con.cursor()
    print("Connected to Database Successfully")
    #Data Deletion Process-( Single-Row )
    query = "DELETE from USERS where id = ?"
    cursor.execute(query,(3,))
    con.commit()
    cursor.close()
except:
    print("Database Error")

#END

Output: USERS table after deletion of row:-

delete-data-in-sqlite-in-python

delete-data-in-database-in-python

Explanation: I will only explain the main points or the points which are totally new for us. Because as we are working with databases, so I’m assuming that you guys are aware of core concepts of python.

At line 4 we have imported our sqlite3 library, because the functions we used in our program is already pre-defined in sqlite3 library.
At line 7 & 16 we used try: & except: method which is useful for handling sql exception.
At line 8 & 9 we initialized “con” variable with connect() method with our database name “data.db“. The connect() method used here is pre-defined in sqlite3 library.
At line 12 we initialized variable “query” with sql query for data deletion. Here “users” is the name of our table present inside our database “data.db”.
At line 13 cursor.execute(query) here cursor storing the database connection & execute() is the function defined in sqlite3 library. The function execute() takes two parameter one is “sql-query” an second is the value which is to be passed as “id” to delete data related to that particular “id” from database table.
At line 14 it is necessary to call commit() function after making changes to our database.
At line 15 it is also necessary to close() our cursor. Basically cursor acts as pointer.

2). Multiple Rows – Deleting Records from Database Table in SQLite in Python

#Importing Database Library
import sqlite3

# Database Connectivity
def deletion(ids):
    try:
        con = sqlite3.connect("data.db")
        cursor = con.cursor()
        print("Connected to Database Successfully")
        #Data Deletion Process - ( Multiple - Rows )
        query = "DELETE from USERS where id = ?"
        cursor.executemany(query,ids)
        con.commit()
        cursor.close()
    except:
        print("Database Error")

ids = [(1,),(5,)]
deletion(ids)

#END

Output: USERS table after deletion of rows:-

deleting-multiple-rows-from-sqlite-table-in-python

Explanation: Program explanation is same as I have discussed above. Only difference is that here we have used executemany() function instead of execute() function. And also we have passed ids list in function parameter as arguments to delete multiple rows.

Important: Our table has only left with one record, so I’m going to add 5 more records in our table.

Our table after adding new records:

sqlite-table-in-python

3). Parameterized Query – Deleting Records from Database Table in SQLite in Python

#Importing Database Library
import sqlite3

# Database Connectivity
def deletion(ids):
    try:
        con = sqlite3.connect("data.db")
        cursor = con.cursor()
        print("Connected to Database Successfully")
        #Data Deletion Process - ( Parameterized - Query )
        query = "DELETE from USERS where id = ?"
        cursor.execute(query,(ids,))
        con.commit()
        cursor.close()
    except:
        print("Database Error")

deletion(4)

#END

Output: USERS table after deletion of rows:-

delete-data-from-sqlite-table-in-python

Explanation: Program explanation is same as I have discussed above. Only difference is that here we have used parameterized function to pass id as arguments to delete row with respect to id.