Performing Transaction Using Python MySQL

Transaction

A Transaction contains a set of SQL commands used to change the data in the dataset. If we say transaction, it means that the data in the database has changed. It is used to change the database from one consistent state to another. It is a logical set of instructions to modify or change the dataset and maintain its consistency.

A transaction can be understood with an example if we want to withdraw money from account X and deposit it in account Y, then this can be a transaction.

A transaction is made up of ACID property, and these four properties are:

1. Atomicity: In this property, all transactions will be successful or none. It’s either all or none. If any of the commands fail, then the entire transaction will fail. Either transaction will complete, or nothing will happen at all.
2. Consistency: In this property, a transaction should always acquire a consistent state. From the start to the end, a transaction should be consistent.
3. Isolation: In this property, any transaction that is happening should not be visible to any other transaction. It should be isolated. Its intermediate result should not be visible to any other transaction.
4. Durability: In this property, if a transaction has been committed, its effect must be persistent even if the system fails.

Python provides two methods to perform transactions which are commit and rollback.

Steps to perform transactions in Python MySQL

• Create MySQL database in Python.
• Collect or prepare the set of queries you want to perform. It can be multiple queries in one transaction.
• Set an auto-commit property of MySQL to false.
• Execute all the queries one by one using cursor.execute().
• If the queries get executed successfully, then commit the changes to the database.
• If any of the queries get blocked or failed, then we will rollback all the changes.
• We will also catch the SQL exception in this process.
• Close the cursor and MySQL database.

Python commit () method

The python commit() method is used to maintain the database’s consistency. In the database, if any changes occur in the transaction, then it must be consistent; for that, we use the commit method.

The method will send a COMMIT statement to the MySQL server, which then commits the current transaction. When execution of the query is successful, it makes permanent changes in a database using the commit() of the connection class.

The syntax for commit() method is given below:

connec.commit()   # connec is the connection object

Any operation that we perform in a transaction to modify the database will not occur until we don’t call the commit method.

Python rollback() method

The Python rollback() method is used if any of the queries in the transaction doesn’t get executed or fail. In that case, we use the rollback method.

The syntax for rollback() method is given below:

connec.rollback()  # connec is the connection object

Python autoCommit() method

It is used to enable or disable the value of auto-commit as true or false. By default, its value is taken as False.

The syntax for autoCommit() method is given below:

connec.autocommit() # connec is the connection object

In the python DB-API, we perform transaction processing through the connection object model. We will invoke begin() to begin the transaction in the database and after that, we can use commit or rollback to end it. The begin() call and the commit() call goes into the same block whereas the rollback() block goes into the corresponding except block which will cancel the transaction if any error occurs.

Let’s understand with an example. In this transaction we will withdraw and deposit money from one person to another.

try:
   conn.begin ( )
   cursor = conn.cursor ( )
   # move some money from one person to the other
   cursor.execute ("UPDATE money SET amt = amt - 10 WHERE name =    'Sam'")
   cursor.execute ("UPDATE money SET amt = amt + 10 WHERE name = 'Eva'")
   cursor.close ( )
   conn.commit( )
except MySQLdb.Error, er:
   print "Transaction failed, rolling back. Error was:"
   print er.args
   try: 
     # if the rollback fails then we will use exception handler
     conn.rollback ( )
   except:
     pass

Explanation

• In the above code, we can see that after successful execution of the queries, we committed our changes to the database using a conn.commit().
• In case of an exception or failure of one of the queries, we can revert our changes using a conn.rollback().
• We placed all our code in the try-except block to catch the database exceptions that may occur during the process.