Update Data from Database in Python using SQLite

In this tutorial, we will learn how to execute an UPDATE Query in Python program to update the data of SQLite’s table. we’ll also learn how to use Python’s built-in sqlite3 module to update the SQLite table.

This tutorial mainly focuses on:

• How to update single row and column of SQLite table in Python
• Using Parameterized query to Update records
• Update multiple rows of SQLite table in Python
• Update multiple columns of SQLite table in Python

In previous tutorial, we have already learnt about how to insert data and read data in python using SQLite? In this tutorial, we will learn to update records from database in SQLite in Python.

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

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

Note: We have four records in our database table. We will update these records.

Single Row & Column – Updating Records of SQLite table in Python

#Importing Database Library
import sqlite3

# Database Connectivity

try:
    con = sqlite3.connect("data.db")
    cursor = con.cursor()
    print("Connected to Database Successfully")
    #Data Updating Process-(Single Row & Column)
    query = "Update USERS set age = 26 where id = 3"
    cursor.execute(query)
    con.commit()
    cursor.close()
except:
    print("Database Error")

#END

Output: USERS table after updation of row:-

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 3 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 updating data. 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 one parameter “sql-query” to update data 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.

Using Parameterized Query – Updating Records of SQLite table in Python

#Importing Database Library
import sqlite3

# Database Connectivity
def updation(names,idd):
    try:
        con = sqlite3.connect("data.db")
        cursor = con.cursor()
        print("Connected to Database Successfully")
        #Data Updating Process-(Single Row & Column)
        query = "Update USERS set name = ? where id = ?"
        cursor.execute(query,(names,idd,))
        con.commit()
        cursor.close()
    except:
        print("Database Error")

updation("Newton",3)

#END

Output: USERS table after updation of row:-

Explanation: Program explanation is same as I have discussed above. Only difference is that here we have used parameterized function in which we have passed the data & some basic concepts of Core Python.

Multiple Rows – Updating Records of SQLite table in Python

#Importing Database Library
import sqlite3

# Database Connectivity
def updation(data):
    try:
        con = sqlite3.connect("data.db")
        cursor = con.cursor()
        print("Connected to Database Successfully")
        #Data Updating Process-(multiple-rows)
        query = "Update USERS set name = ? where id = ?"
        cursor.executemany(query,data)
        con.commit()
        cursor.close()
    except:
        print("Database Error")

data = [("Newton",3), ("Ricky",4)]
updation(data)

#END

Output: USERS table after updation of rows:-

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 list in function parameter as arguments to update multiple records.

Multiple Columns – Updating Records of SQLite table in Python

#Importing Database Library
import sqlite3

# Database Connectivity

try:
    con = sqlite3.connect("data.db")
    cursor = con.cursor()
    print("Connected to Database Successfully")
    #Data Updating Process-(multiple-columns)
    query = "Update USERS set name = ?, age = ? where id = ?"
    cursor.execute(query,("Jordan",28,4,))
    con.commit()
    cursor.close()
except:
    print("Database Error")

#END

Output: USERS table after updation of columns:-

Explanation: Program explanation is same as I have discussed above. Only difference is that here we have used query for updating more than one column .