A database transaction is characterized as a set of various operations that are executed as one cohesive unit, ensuring that either all operations are completed successfully or none at all are executed. In other terms, every operation involved in a singular transaction must be finalized; should even a single operation fail to complete, the entire transaction will be halted.
Subsequently, it will revert everything to the state that existed prior to the execution of the transactions. This functionality is crucial for ensuring the security of all transactions and upholding data integrity, consistency, and reliability across different systems.
In this instructional guide, we will explore the commit and rollback functions, providing examples to manage various database transactions effectively.
Transaction ACID Properties
Database transactions follow a set of properties known as the ACID properties. These are important as they protect the security, accuracy and consistency of transactions and help to manage changes in a reliable way, preserving data integrity even when errors or failures occur.
- Atomicity : Either the transaction completes, or nothing happens. If a transaction contains 4 queries, then all these queries must be executed, or none of them must be executed.
- Consistency : The database must be consistent before the transaction starts, and the database must also be consistent after the transaction is completed.
- Isolation : Intermediate results of a transaction are not visible outside the current transaction.
- Durability : Once a transaction is committed, the effects are persistent, even after a system failure.
Python commit method
Python offers the commit method, which guarantees that any modifications made to the database are applied reliably. If the commit method is not utilized, none of the alterations will be preserved, and all updated information will be discarded as soon as the connection is terminated.
Python commit Method Syntax
The following illustrates the syntax for utilizing the commit method.
conn.commit() #conn is the connection object
Python rollback method
The rollback function serves the purpose of reversing alterations made to the database. This function proves to be beneficial, particularly in scenarios where an error arises during database operations, allowing us to revert that transaction to uphold the integrity of the database. Should a circumstance arise where the developer is dissatisfied with the modifications implemented in the database, or if a transaction fails for any reason, the rollback function is employed to retrieve the initial data that was altered via the commit function.
Python rollback Method Syntax
The syntax for invoking the rollback method is outlined below.
conn.rollback()
Closing the connection
It is essential to terminate the database connection after completing all operations related to the database. In Python, the close method is utilized to disconnect from the database.
Python close Method Syntax
The following syntax illustrates how to utilize the close method.
conn.close()
Python Transection Example
Let us explore all three concepts through the use of an example.
Example
import mysql.connector
from mysql.connector import Error
try:
# Connecting to MySQL server
db_connection = mysql.connector.connect(
host="localhost",
user="root",
password="Root",
database="mydatabase"
)
# Create a cursor object
db_cursor = db_connection.cursor()
# Writing a SQL UPDATE to prevent SQL injection
sql = "UPDATE customers SET address = %s WHERE address = %s"
val = ("Kolkata", "Goa") # Change address 'Goa' to 'Kolkata'
db_cursor.execute(sql, val)
# Commit the changes to make them permanent
db_connection.commit()
# Success message
print("Record updated successfully!")
except mysql.connector.Error as error:
# Error message if the transaction fails
print(f"Update failed! Error: {error}")
# Rollback changes if there's an error
db_connection.rollback()
finally:
# Closing cursor and connection
if db_cursor:
db_cursor.close()
if db_connection:
db_connection.close()
Output:
Explanation:
In the preceding program, subsequent to executing the UPDATE statement to modify the address from Goa to Kolkata, we utilized the dbconnection.commit method to ensure that the revised data is securely stored in the database. Should any errors arise during the execution process, the except block invokes dbconnection.rollback, which negates any uncommitted modifications and restores the database to its prior condition. Finally, we employed dbcursor.close and dbconnection.close to free up resources and correctly terminate the connection to the database.
Conclusion
Database transactions ensure that all operations performed on a database are executed correctly while safeguarding the system's security, integrity, and accuracy. The ACID principles—Atomicity, Consistency, Isolation, and Durability—are crucial for effective transaction management. These principles assist in managing alterations to the database in a dependable manner, preserving data integrity even in the event of errors or system failures.
Python MySQL offers the commit function, which allows developers to save changes permanently, while the rollback function is available to revert changes in the event of an error. This functionality aids developers in managing data securely and efficiently. After all operations are completed, the close method is utilized to terminate the database connection, ensuring effective use of resources and minimizing the risk of potential issues.
Python-MySQL Performing Transactions FAQs
1. What is the primary purpose of a database transaction?
A database transaction guarantees that a series of operations is handled as one cohesive unit, meaning that either every operation is successfully performed, or none are executed whatsoever. This principle is crucial for upholding the integrity and precision of the data.
2. What will happen if we don't use the commit method in our programs?
Should we neglect to utilize the commit method, any alterations executed within our program will remain unsaved in the database. Consequently, all modifications will be irrevocably lost once the connections are terminated.
3. Is it necessary to close the database connection?
Concluding the connection is considered a best practice, as it liberates resources and guarantees that the database remains closed when not in use, preventing it from being left open without necessity.
4. Which method is used to revert changes in case of a failure?
The rollback function is employed to undo modifications when any kind of failure occurs.