SQL Transactions - COMMIT, ROLLBACK, and Savepoints with Python
In modern database development, tools like DataGrip make transaction handling easier by providing graphical interfaces to commit, rollback, or set savepoints. In this article, we’ll define COMMIT, ROLLBACK, and SAVEPOINT in SQL and demonstrate how to implement these transaction controls when working with Oracle, MySQL, or PostgreSQL in Python. Key Transaction Concepts 1. COMMIT • Definition: Finalizes a transaction, making all changes permanent in the database. • Use Case: Use COMMIT when all operations in a transaction are successful, ensuring the database reflects the changes. 2. ROLLBACK • Definition: Reverts all changes made during a transaction, restoring the database to its previous state. • Use Case: Use ROLLBACK to handle errors or undo a failed transaction. 3. SAVEPOINT • Definition: Sets a named checkpoint within a transaction, allowing partial rollbacks to that point without undoing the entire transaction. • Use Case: Use SAVEPOINT to manage complex transactions with multiple steps, rolling back selectively when needed. Python and Database Transactions When working with databases programmatically, Python’s database libraries (e.g., cx_Oracle, mysql-connector-python, psycopg2) provide methods to control transactions explicitly. Common Steps for Transactions in Python Start a Transaction: Automatically starts with the connection unless autocommit is enabled. Execute SQL Statements: Perform the necessary database operations. Commit or Rollback: Use commit() to finalize changes or rollback() to revert them. Use Savepoints: For finer control, define and rollback to savepoints if supported. Example: Python Transactions with Oracle Databases Setup Connection import cx_Oracle # Connect to Oracle Database connection = cx_Oracle.connect("user/password@localhost/XEPDB1") cursor = connection.cursor() Using COMMIT and ROLLBACK try: # Start Transaction cursor.execute("UPDATE Accounts SET Balance = Balance - 100 WHERE Name = 'Alice'") cursor.execute("UPDATE Accounts SET Balance = Balance + 100 WHERE Name = 'Bob'") # Commit the transaction connection.commit() print("Transaction committed successfully!") except Exception as e: # Rollback in case of error connection.rollback() print(f"Transaction failed. Rolled back changes. Error: {e}") Using SAVEPOINT try: # Start Transaction cursor.execute("UPDATE Accounts SET Balance = Balance - 200 WHERE Name = 'Alice'") connection.commit() # Savepoint cursor.execute("SAVEPOINT Savepoint_After_Alice") # Add 200 to Bob (intentional error to demonstrate rollback) cursor.execute("UPDATE Accounts SET Balance = Balance + 200 WHERE Name = 'Unknown'") # Commit if successful connection.commit() except Exception as e: # Rollback to savepoint cursor.execute("ROLLBACK TO Savepoint_After_Alice") connection.commit() print(f"Rolled back to savepoint. Error: {e}") Summary Mastering these concepts allows you to manage database transactions efficiently in real-world applications! Explore more Luca LiuFollow Hello there!
In modern database development, tools like DataGrip make transaction handling easier by providing graphical interfaces to commit, rollback, or set savepoints.
In this article, we’ll define COMMIT, ROLLBACK, and SAVEPOINT in SQL and demonstrate how to implement these transaction controls when working with Oracle, MySQL, or PostgreSQL in Python.
Key Transaction Concepts
1. COMMIT
• Definition: Finalizes a transaction, making all changes permanent in the database.
• Use Case: Use COMMIT when all operations in a transaction are successful, ensuring the database reflects the changes.
2. ROLLBACK
• Definition: Reverts all changes made during a transaction, restoring the database to its previous state.
• Use Case: Use ROLLBACK to handle errors or undo a failed transaction.
3. SAVEPOINT
• Definition: Sets a named checkpoint within a transaction, allowing partial rollbacks to that point without undoing the entire transaction.
• Use Case: Use SAVEPOINT to manage complex transactions with multiple steps, rolling back selectively when needed.
Python and Database Transactions
When working with databases programmatically, Python’s database libraries (e.g., cx_Oracle, mysql-connector-python, psycopg2) provide methods to control transactions explicitly.
Common Steps for Transactions in Python
- Start a Transaction: Automatically starts with the connection unless autocommit is enabled.
- Execute SQL Statements: Perform the necessary database operations.
- Commit or Rollback: Use commit() to finalize changes or rollback() to revert them.
- Use Savepoints: For finer control, define and rollback to savepoints if supported.
Example: Python Transactions with Oracle Databases
Setup Connection
import cx_Oracle
# Connect to Oracle Database
connection = cx_Oracle.connect("user/password@localhost/XEPDB1")
cursor = connection.cursor()
Using COMMIT and ROLLBACK
try:
# Start Transaction
cursor.execute("UPDATE Accounts SET Balance = Balance - 100 WHERE Name = 'Alice'")
cursor.execute("UPDATE Accounts SET Balance = Balance + 100 WHERE Name = 'Bob'")
# Commit the transaction
connection.commit()
print("Transaction committed successfully!")
except Exception as e:
# Rollback in case of error
connection.rollback()
print(f"Transaction failed. Rolled back changes. Error: {e}")
Using SAVEPOINT
try:
# Start Transaction
cursor.execute("UPDATE Accounts SET Balance = Balance - 200 WHERE Name = 'Alice'")
connection.commit()
# Savepoint
cursor.execute("SAVEPOINT Savepoint_After_Alice")
# Add 200 to Bob (intentional error to demonstrate rollback)
cursor.execute("UPDATE Accounts SET Balance = Balance + 200 WHERE Name = 'Unknown'")
# Commit if successful
connection.commit()
except Exception as e:
# Rollback to savepoint
cursor.execute("ROLLBACK TO Savepoint_After_Alice")
connection.commit()
print(f"Rolled back to savepoint. Error: {e}")
Summary
Mastering these concepts allows you to manage database transactions efficiently in real-world applications!
Explore more
