Starting and Committing Transactions
You'll learn how to explicitly begin a database transaction, perform multiple operations within it, and commit those changes to make them permanent.
The Core Idea
In the previous lesson, you learned the difference between autocommit mode (where every statement is immediately permanent) and explicit transactions (where you control when changes become permanent). Now you'll learn the practical mechanics: how to start a transaction block and how to commit it to save your work.
Think of it like editing a document: starting a transaction is like opening a draft, executing queries is like making edits, and committing is like hitting "Save" to make those edits permanent. Until you commit, the changes exist only in your session.
Starting a Transaction
In Python database libraries like sqlite3 and psycopg2, you typically start a transaction by turning off autocommit (which may happen automatically) or by simply executing your first query after a connection is opened:
import sqlite3
conn = sqlite3.connect('my_database.db')
cursor = conn.cursor()
# Transaction begins implicitly with the first statement
cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE user_id = 1")
cursor.execute("UPDATE accounts SET balance = balance + 100 WHERE user_id = 2")
At this point, the changes exist but aren't permanent yet.
Committing the Transaction
To make your changes permanent, call the commit() method on your connection object:
conn.commit() # Now both updates are saved permanently
If you don't commit before closing the connection, your changes will be lost (rolled back automatically).
Key Takeaway: Start a transaction by executing statements on a connection, then call conn.commit() to permanently save all changes made during that transaction block—until you commit, changes remain temporary and reversible.