SQL Transactions – How to Implement COMMIT, ROLLBACK, and Savepoints With Python

In the development of the modern database, tools like Datagrip Facilitate manipulation of transactions by providing graphic interfaces to commit, roll up or define backup points.

In this article, we will define COMMIT,, DeclineAnd Backup point in SQL and demonstrate how to implement these transaction controls when working with Oracle,, MysqlOr PostgreSql In Python.

Key transaction concepts

1.

• Definition: Finalizes a transaction, making all permanent changes in the database.

• Use case: Use Commit when all the operations of a transaction are successful, ensuring that the database reflects the modifications.

2. Rollback

• Definition: Return all the changes made during a transaction, restaurant the database in its previous condition.

• Use case: Use Rollback to manage errors or cancel a stranded transaction.

3. Savepoint

• Definition: Defines a control point named in a transaction, allowing partial mirrors to this point without canceling the entire transaction.

• Use case: Use SavePoint to manage complex transactions with several steps, by returning it selectively if necessary.

Python and database transactions

When you work with databases by program, Python's database libraries (for example, CX_Racle, MySQL-CONNECORTOR-PYTY, PSYCOPG2) provide methods to explicitly control transactions.

Current steps for python transactions

1. Start a transaction: Start automatically with the connection unless autocommit is activated.
2. Execute SQL instructions: Perform the necessary database operations.
3. Engage or come back: Use Commit () to finalize changes or rollback () to return them.
4. Use backup points: For a finer control, define and behind to save the views if supported.

Example: Python Transactions with Oracle databases

Configuration

import cx_Oracle

# Connect to Oracle Database
connection = cx_Oracle.connect("user/password@localhost/XEPDB1")
cursor = connection.cursor()

Use of commuting and hindsight

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}")

Use of 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 effectively manage database transactions in real world applications!

Thank you for taking the time to explore data related to me with me. I appreciate your commitment. If you find this information useful, I invite you to follow me or connect with me on LinkedIn. Happy to explore! 👋