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 theConnectionobject. - Second, create a
Cursorobject by calling thecursor()method of theConnectionobject. - Third, execute the
UPDATEstatement by calling theexecute()method of theCursorobject.
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:
Use these command to format the output:
sqlite> .header on
sqlite> .mode columnCode 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)
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.