Learn Python – Performing Transactions- Basic and advance

Transactions make certain the records consistency of the database. We have to make positive that extra than one functions must no longer adjust the data whilst performing the database operations. The transactions have the following properties.

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 was committed, the effects are persistent, even after a system failure.

Python commit() method

Python offers the commit() method which ensures the changes made to

the database consistently take place.

The syntax to use the commit() method is given below.

conn.commit() #conn is the connection object   

All the operations that regulate the files of the database do now not take place until the commit() is called.

Python rollback() method

The rollback() approach is used to revert the changes that are completed to the database. This technique is useful in the feel that, if some error takes place during the database operations, we can rollback that transaction to keep the database consistency.

The syntax to use the rollback() is given below.

Conn.rollback()  

Closing the connection

We need to shut the database connection once we have accomplished all the operations concerning the database. Python affords the close() method. The syntax to use the close() technique is given below.

conn.close()   

In the following example, we are deleting all the personnel who are working for the CS department.

Example

import mysql.connector  
  
#Create the connection object   
myconn = mysql.connector.connect(host = "localhost", user = "root",passwd = "google",database = "PythonDB")  
  
#creating the cursor object  
cur = myconn.cursor()  
  
try:  
    cur.execute("delete from Employee where Dept_id = 201")  
    myconn.commit()  
    print("Deleted !")  
except:  
    print("Can't delete !")  
    myconn.rollback()  
  
myconn.close()  

Output:

Deleted !