Python Program - Database - Performing Transactions:

• Transaction

  Transactions are a mechanism that ensures data consistency. Transactions should have the following four properties:
  1. Atomicity: Either a transaction completes or nothing happens at all.
  2. Consistency: A transaction must start in a consistent state and leave the system is a consistent state.
  3. Isolaion: Intermediate results of a transaction are not visible outside the current transaction.
  4. Durability: Once a transaction was committed, the effects are persistent, even after a system failure.
  5. The Python DB API 2.0 provides two methods to either commit or rollback a transaction.

• Example:

  # Prepare SQL query to DELETE required records
  sql = "DELETE FROM EMPLOYEE WHERE AGE > '%d'" % (20)
  try:
     # Execute the SQL command
     cursor.execute(sql)
     # Commit your changes in the database
     db.commit()
  except:
     # Rollback in case there is any error
     db.rollback()

• COMMIT Operation:

  Commit is the operation which gives a green signal to database to finalize the changes and after this operation no change can be reverted back.

  Example

  db.commit()

• ROLLBACK Operation:

  If you are not satisfied with one or more of the changes and you want to revert back those changes completely then use rollback method.

  Example

  db.rollback()

• Disconnecting Database:

  To disconnect Database connection, use close() method.
  db.close()
  
  If the connection to a database is closed by the user with the close() method, any outstanding transactions are rolled back by the DB.
  However, instead of depending on any of DB lower level implementation details, your application would be better off calling commit or rollback explicitly.