SQLite Python: Updating Data
Summary: in this tutorial, we will show you how to update data in the SQLite database from a Python program using the sqlite3 module.
To update data in a table from a Python program, you follow these steps:
- First, create a database connection to the SQLite database using the
connect()
function. Once the database connection created, you can access the database using theConnection
object. - Second, create a
Cursor
object by calling thecursor()
method of theConnection
object. - Third, execute the
UPDATE
statement by calling theexecute()
method of theCursor
object.
In this example we will update the priority, begin date, and end date of a specific task in the tasks
table.
To create a database connection, you use the following create_connection()
function:
def create_connection(db_file):
""" create a database connection to the SQLite database
specified by the db_file
:param db_file: database file
:return: Connection object or None
"""
conn = None
try:
conn = sqlite3.connect(db_file)
except Error as e:
print(e) return conn
Code language: SQL (Structured Query Language) (sql)
This update_task()
function update a specific task:
def update_task(conn, task):
"""
update priority, begin_date, and end date of a task
:param conn:
:param task:
:return: project id
"""
sql = ''' UPDATE tasks
SET priority = ? ,
begin_date = ? ,
end_date = ?
WHERE id = ?'''
cur = conn.cursor()
cur.execute(sql, task)
conn.commit()
Code language: Python (python)
The following main()
function creates a connection to the database located in C:\sqlite\db\pythonsqlite.db folder and call the update_task()
function to update a task with id 2:
def main():
database = r"C:\sqlite\db\pythonsqlite.db" # create a database connection
conn = create_connection(database)
with conn:
update_task(conn, (2, '2015-01-04', '2015-01-06', 2))
if __name__ == '__main__':
main()
Code language: Python (python)
Here is the full program:
import sqlite3
from sqlite3 import Errordef create_connection(db_file):
""" create a database connection to the SQLite database
specified by the db_file
:param db_file: database file
:return: Connection object or None
"""
conn = None
try:
conn = sqlite3.connect(db_file)
except Error as e:
print(e)
return conn
def update_task(conn, task):
"""
update priority, begin_date, and end date of a task
:param conn:
:param task:
:return: project id
"""
sql = ''' UPDATE tasks
SET priority = ? ,
begin_date = ? ,
end_date = ?
WHERE id = ?'''
cur = conn.cursor()
cur.execute(sql, task)
conn.commit()
def main():
database = r"C:\sqlite\db\pythonsqlite.db"
# create a database connection
conn = create_connection(database)
with conn:
update_task(conn, (2, '2015-01-04', '2015-01-06', 2))
if __name__ == '__main__':
main()
Code language: Python (python)
After executing the program, you can connect to the database via sqlite3 command shell:
![](https://i0.wp.com/www.sqlitetutorial.net/wp-content/uploads/2019/08/sqlite-python-connect.png?resize=334%2C67&ssl=1)
Use these command to format the output:
sqlite> .header on
sqlite> .mode column
Code language: CSS (css)
And use the following statement to get the task with id 2:
SELECT * FROM tasks WHERE id = 2;
Code language: SQL (Structured Query Language) (sql)
![](https://i0.wp.com/www.sqlitetutorial.net/wp-content/uploads/2019/08/sqlite-python-update-data.png?resize=935%2C92&ssl=1)
As shown clearly from the screenshot, the task with id 2 has been updated successfully.
In this tutorial, you have learned how to update data in a table from a Python program.