Managing Financial Transactions
Transactions guarantee that the database will always contain consistent data. Throughout the execution of the database operations, we have to take precautions to ensure that no more than one application modifies any of the entries. The following is included in the transactions:
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 has a function called commit(), which guarantees that any modifications made will be saved.
the database be maintained in a consistent manner.
It is provided here with the syntax to utilise the commit() function.
below.
-
conn.commit()
#conn is the connection object
Until the commit() function is called, none of the activities that would change the database’s entries would be carried out.
called.
Python rollback() method
It is possible to undo the modifications that have been made to the database by using the rollback() function. We are able to rollback that transaction in the event that an issue happens when we are working with the database, which allows us to keep the database consistent. This approach is helpful because of this feature.
The syntax for using the rollback() function is shown below.
below.
-
Conn.rollback()
Closing the connection
After we have finished all of the actions that pertain to the database, we will need to terminate our connection to the database. Python has a function called close() that you may use. The syntax that must be used in order to call the close() function is shown below.
below.
-
conn.close()
In the following example, we are eliminating all of the staff members that are currently employed by 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 !