Python update SQLite table

Updating table records in SQLite with Python involves utilizing the sqlite3 module and employing the UPDATE statement to modify existing data within a database table. The process typically involves several steps:

Import SQLite library: First, you need to import the sqlite3 module, which comes built-in with Python.

Connect to the SQLite Database: Use sqlite3.connect() to connect to the SQLite database. If the database does not exist, this command will create it.

Create a Cursor Object: A cursor allows you to execute SQL commands in a Python script.

Execute an Update Statement: Use the cursor object to execute an UPDATE SQL statement. This statement updates the records in the table based on your specified conditions.

Commit the Changes: After executing the UPDATE command, you need to commit the changes to the database.

Close the Connection: Finally, close the database connection once the update is complete.

Here’s a sample Python script demonstrating these steps:

import sqlite3

# Connect to SQLite database (or create it if it doesn't exist)
connection = sqlite3.connect('my_database.db')

# Create a cursor object
cursor = connection.cursor()

# Prepare the UPDATE SQL query
update_query = """
UPDATE my_table
SET column_name = 'new_value'
WHERE condition;
"""

# Execute the update query
cursor.execute(update_query)

# Commit the changes to the database
connection.commit()

# Close the connection
connection.close()

In this script, my_database.db is the name of your SQLite database, my_table is the name of the table you want to update, column_name is the name of the column you want to update, ‘new_value’ is the new value you want to set, and condition is the condition that specifies which records should be updated (like id = 1).

Here is an example of updating a record in a table named employees, where you’re setting the salary to 90000 for the employee with id equal to 1:

import sqlite3

try:
    conn = sqlite3.connect('example.db')
    cursor = conn.cursor()
    print("Connected to SQLite")

    sql_update_query = """Update employees set salary = 90000 where id = 1"""
    cursor.execute(sql_update_query)
    conn.commit()
    print("Record Updated successfully")
    cursor.close()

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

This script updates the salary of a specific employee in the employees table.

Remember to replace these placeholders with your actual database name, table name, column names, and conditions as per your requirements.

This comprehensive guide provides a structured and professional approach to updating SQLite table records with Python. It emphasizes the importance of importing the necessary module, establishing a database connection, creating a cursor object, constructing the UPDATE statement, executing it, committing the changes, and closing the cursor and connection.