Updating, Deleting & ACID Transactions in SQLite3

In real applications, user profiles get updated, account balances shift, and inactive records are deleted.

This lesson covers UPDATE, DELETE, and how to safely use Transactions & rollback() to prevent partial database corruption.

---

1. Modifying Records with SQL UPDATE

import sqlite3

with sqlite3.connect("university.db") as conn:
    cursor = conn.cursor()
    
    update_sql = "UPDATE students SET gpa = ? WHERE email = ?"
    cursor.execute(update_sql, (9.85, "rahul@gmail.com"))
    
    # cursor.rowcount tells you how many records were modified
    print(f"✅ Records updated: {cursor.rowcount}")

Always Include a WHERE Clause!

If you write UPDATE students SET gpa = 9.85 without a WHERE clause, every single student in the entire university database will be updated to GPA 9.85!

---

2. Removing Records with SQL DELETE

import sqlite3

with sqlite3.connect("university.db") as conn:
    cursor = conn.cursor()
    
    delete_sql = "DELETE FROM students WHERE student_id = ?"
    cursor.execute(delete_sql, (1,))
    
    if cursor.rowcount > 0:
        print("🗑️ Student record removed successfully.")
    else:
        print("⚠️ No record found matching that ID.")

---

3. Database Transactions: commit() and rollback()

A Transaction is a unit of work that contains multiple database steps. Under ACID principles, either all steps succeed, or none of them take effect (all-or-nothing).

Real-World Example: Bank Account Transfer

Imagine transferring ₹1,000 from Rahul to Sai:

1. Deduct ₹1,000 from Rahul.
2. Add ₹1,000 to Sai.

If the computer loses power or errors occur during Step 2, Rahul loses money, but Sai never gets it. rollback() undoes Step 1 automatically:

import sqlite3

conn = sqlite3.connect("bank.db")
cursor = conn.cursor()

try:
    # Step 1: Deduct from Rahul
    cursor.execute("UPDATE accounts SET balance = balance - 1000 WHERE name = 'Rahul'")
    
    # Step 2: Add to Sai (Simulate an error like network crash)
    cursor.execute("UPDATE accounts SET balance = balance + 1000 WHERE name = 'Sai'")
    
    # If both steps pass with zero errors, commit permanently
    conn.commit()
    print("✅ Bank transfer completed successfully!")

except Exception as err:
    # Undo step 1 so Rahul's money is restored!
    conn.rollback()
    print(f"❌ Transfer failed! Transaction rolled back: {err}")

finally:
    conn.close()

---

Quick Summary

Operation	SQL Command	Python Execution
Create	INSERT INTO ...	cursor.execute(sql, (val1, val2))
Read	SELECT ... FROM ...	cursor.fetchall() or cursor.fetchone()
Update	UPDATE ... SET ... WHERE ...	cursor.execute(sql, (new_val, id))
Delete	DELETE FROM ... WHERE ...	cursor.execute(sql, (id,))
Rollback	conn.rollback()	Reverts pending transaction on errors

---

What's Next?

Now let's explore concurrent computing, parallel tasks, race conditions, and thread safety in Module 23: Multithreading & Concurrency!

Module 23: Multithreading & Concurrency

Master threading basics, thread pools, locks, race conditions, and multiprocessing vs multithreading