How to Update Data in SQLite Using Python: Simple Guide

To update data in SQLite using Python, use the UPDATE SQL statement with a WHERE clause inside a cursor's execute() method, then call commit() on the connection to save changes. This modifies existing rows based on your conditions.

Syntax

The basic syntax to update data in SQLite using Python involves these steps:

• Connect to the database.
• Create a cursor object to execute SQL commands.
• Use the UPDATE statement with SET to specify new values.
• Use WHERE to select which rows to update.
• Call commit() on the connection to save changes.
• Close the connection.

python

import sqlite3

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

cursor.execute('''
    UPDATE table_name
    SET column1 = new_value1, column2 = new_value2
    WHERE condition
''')

conn.commit()
conn.close()

Example

This example updates the age of a user named 'Alice' in a users table.

python

import sqlite3

# Connect to SQLite database
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Create table and insert sample data (for demonstration)
cursor.execute('CREATE TABLE IF NOT EXISTS users (name TEXT, age INTEGER)')
cursor.execute('INSERT INTO users (name, age) VALUES (?, ?)', ('Alice', 25))
cursor.execute('INSERT INTO users (name, age) VALUES (?, ?)', ('Bob', 30))
conn.commit()

# Update Alice's age to 26
cursor.execute('UPDATE users SET age = ? WHERE name = ?', (26, 'Alice'))
conn.commit()

# Verify update
cursor.execute('SELECT name, age FROM users WHERE name = ?', ('Alice',))
user = cursor.fetchone()
print(user)  # Output the updated row

# Clean up
cursor.execute('DROP TABLE users')
conn.commit()
conn.close()

Output

('Alice', 26)

Common Pitfalls

Common mistakes when updating data in SQLite using Python include:

• Forgetting to call commit() after execute(), so changes are not saved.
• Not using a WHERE clause, which updates all rows unintentionally.
• Passing parameters incorrectly, risking SQL injection or errors.
• Not closing the connection, which can lock the database.

python

import sqlite3

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Wrong: Missing WHERE clause updates all rows
# cursor.execute('UPDATE users SET age = 40')

# Correct: Use WHERE clause and parameters
cursor.execute('UPDATE users SET age = ? WHERE name = ?', (40, 'Bob'))
conn.commit()
conn.close()

Quick Reference

Remember these key points when updating SQLite data with Python:

• Always use WHERE to target specific rows.
• Use parameterized queries to avoid errors and security issues.
• Call commit() to save changes.
• Close the connection when done.

Key Takeaways

Use the UPDATE statement with a WHERE clause inside cursor.execute() to modify specific rows.

Always call connection.commit() after executing an update to save changes.

Use parameterized queries to safely pass values and avoid SQL injection.

Forgetting WHERE updates all rows, so always double-check your conditions.

Close the database connection to free resources and avoid locks.