SQLite Python: Updating Data

Summary: in this tutorial, you will learn how to update data in the SQLite database from a Python program using the sqlite3 module.

Updating data in SQLite using Python

To update data in a table from a Python program, you follow these steps:

First, open a database connection to an SQLite database file by calling the connect() function of the sqlite3 module:

conn = sqlite3.connect(database)Code language: Python (python)

The connect() function returns a Connection object that represents a database connection to a specified SQLite database.

Next, create a cursor object from Connection object by calling the cursor() method:

cur = conn.cursor()Code language: Python (python)

Then, execute an UPDATE statement by calling the execute() method of the Cursor object:

cur.execute(update_sattement)Code language: Python (python)

If you want to bind the arguments to the UPDATE statement, use a question mark (?) for each argument. For example:

UPDATE table_name
SET column1 = ?, column2 = ?
WHERE id = ?Code language: SQL (Structured Query Language) (sql)

In this syntax, the question marks (?) are the placeholders that will be replaced by actual values for the column1, column2, and id.

In this case, you need to pass the second argument as a tuple to the execute() method:

cur.execute(update_statement, (value2, value2, id,))Code language: Python (python)

After that, call the commit() method of the Connection object to apply the change to the database permanently:

conn.commit()Code language: Python (python)

Finally, close the database connection by calling the close() method of the Connection object:

conn.close()Code language: Python (python)

Here are the complete steps:

update_statement = 'UPDATE sample_table SET column1=?, column2=? WHERE id = ?'

conn = sqlite3.connect(database)
cur = conn.cursor()
cur.execute(update_statement, (value1, value2, id,))
conn.commit()
conn.close()Code language: Python (python)

If you use a context manager, you can implicitly close the database connection:

import sqlite3

sql = 'UPDATE sample_table SET column1=?, column2=? WHERE id = ?'

with sqlite3.connect(database) as conn:
    cur = conn.cursor()
    cur.execute(sql, (value1, value2, id,))
    conn.commit()Code language: Python (python)

An error may occur when deleting the data. To handle it, you can use the try…except statement:

import sqlite3

sql  = 'UPDATE sample_table SET column1=?, column2=? WHERE id = ?'

try:
    with sqlite3.connect(database) as conn:
        cur = conn.cursor()
        cur.execute(sql, (value1, value2, id,))
        conn.commit()
except sqlite3.OperationalError as e:
    print(e)Code language: Python (python)

Updating data examples

We’ll use the tasks table with the following contents: