Python Update SQLite Table

In this lesson, learn to execute an UPDATE Query from a Python application to update the SQLite table’s data. You’ll learn how to use Python’s sqlite3 module to update the SQLite table.

Prerequisites

If a table is not present in your SQLite database, then please refer to the following articles: –

• Create SQLite table from Python.
• Insert data into SQLite Table from Python

Steps to update a single row of SQLite table

As of now, the ‘SqliteDb_developers’ table contains six rows, so let’s update the salary of a developer whose id is 4. To perform SQLite UPDATE query from Python, you need to follow these simple steps:

Example

import sqlite3

def updateSqliteTable():
    try:
        sqliteConnection = sqlite3.connect('SQLite_Python.db')
        cursor = sqliteConnection.cursor()
        print("Connected to SQLite")

        sql_update_query = """Update SqliteDb_developers set salary = 10000 where id = 4"""
        cursor.execute(sql_update_query)
        sqliteConnection.commit()
        print("Record Updated successfully ")
        cursor.close()

    except sqlite3.Error as error:
        print("Failed to update sqlite table", error)
    finally:
        if sqliteConnection:
            sqliteConnection.close()
            print("The SQLite connection is closed")

updateSqliteTable()

Output

Note: Note: If you are doing multiple update operations and wanted to revert your change in case of failure of any operations, use the rollback() method of a connection class to revert the changes. Use the rollback() method of a connection class. in except block.

Using Python variables in SQLite UPDATE query

Most of the time, we need to update a table with some runtime values. For example, when users update their profile or any other details through a user interface, we need to update a table with those new values. In such cases, It is always best practice to use a parameterized query.

The parameterized query uses placeholders (?) inside SQL statements that contain input from users. It helps us to update runtime values and prevent SQL injection concerns.

import sqlite3

def updateSqliteTable(id, salary):
    try:
        sqliteConnection = sqlite3.connect('SQLite_Python.db')
        cursor = sqliteConnection.cursor()
        print("Connected to SQLite")

        sql_update_query = """Update SqliteDb_developers set salary = ? where id = ?"""
        data = (salary, id)
        cursor.execute(sql_update_query, data)
        sqliteConnection.commit()
        print("Record Updated successfully")
        cursor.close()

    except sqlite3.Error as error:
        print("Failed to update sqlite table", error)
    finally:
        if sqliteConnection:
            sqliteConnection.close()
            print("The sqlite connection is closed")

updateSqliteTable(3, 7500)

Output

Let’s understand the above program

• We used two placeholders in the update query, one for the salary column and the other is for the id column.
• Next, We prepared a data tuple by specifying two Python variables in sequential order.
• Next, we passed the SQL update query and data tuple to the cursor.execute() method. Remember variables order in the tuple is sequential as per column placeholders order.

Note: If you have a date column in the SQLite table, and you want to update the Python DateTime variable into a column, then please refer to working with SQLite data time values in Python.

Update multiple rows of SQLite table using cursor’s executemany()

In the above example, we have used execute() method of cursor object to update a single record. But sometimes, we need to update multiple rows of the SQLite table. For example, you want to increase the salary of developers by 20%.

Instead of executing the UPDATE query every time to update each record, you can perform bulk update operations in a single query using the cursor.executemany() method.

The executemany(query, seq_param) method accepts the following two parameters

• SQL query
• list of records to be updated.

Now, let see the example. In this example, we are updating three rows.

import sqlite3

def updateMultipleRecords(recordList):
    try:
        sqliteConnection = sqlite3.connect('SQLite_Python.db')
        cursor = sqliteConnection.cursor()
        print("Connected to SQLite")

        sqlite_update_query = """Update SqliteDb_developers set salary = ? where id = ?"""
        cursor.executemany(sqlite_update_query, recordList)
        sqliteConnection.commit()
        print("Total", cursor.rowcount, "Records updated successfully")
        sqliteConnection.commit()
        cursor.close()

    except sqlite3.Error as error:
        print("Failed to update multiple records of sqlite table", error)
    finally:
        if sqliteConnection:
            sqliteConnection.close()
            print("The SQLite connection is closed")

records_to_update = [(9700, 4), (7800, 5), (8400, 6)]
updateMultipleRecords(records_to_update)

Output:

You can verify the result by selecting data from a SQLite table using Python.

Let’s understand the above example

• We prepared the SQLite update query with two placeholders (“salary” and “Id” column ) and a list of records to update in tuple format.
• Each element of a list is nothing but a tuple for each row. Each tuple contains two values, i.e., salary and id of a developer.
• We passed SQLite update query and record list to executemany() as arguments.
• To get to know the number of records updated, we used a cursor.rowcount function.

Update multiple Columns of SQLite table

We can also update multiple columns of an SQLite table in a single query. Just prepare a parameterized query using a placeholder to update multiple columns. Let see this with an example program.

import sqlite3

def updateMultipleColumns(id, salary, email):
    try:
        sqliteConnection = sqlite3.connect('SQLite_Python.db')
        cursor = sqliteConnection.cursor()
        print("Connected to SQLite")

        sqlite_update_query = """Update new_developers set salary = ?, email = ? where id = ?"""
        columnValues = (salary, email, id)
        cursor.execute(sqlite_update_query, columnValues)
        sqliteConnection.commit()
        print("Multiple columns updated successfully")
        sqliteConnection.commit()
        cursor.close()

    except sqlite3.Error as error:
        print("Failed to update multiple columns of sqlite table", error)
    finally:
        if sqliteConnection:
            sqliteConnection.close()
            print("sqlite connection is closed")

updateMultipleColumns(3, 6500, 'ben_stokes@gmail.com')

Output

Next Steps:

To practice what you learned in this article, Please solve a Python Database Exercise project to Practice and master the Python Database operations.