unit-5 SQL 4.1 TRANSACTIONS in sql .pptx

Sql transactions

1. CHAPTER-5 INTERFACE PYTHON WITH SQL

2. MYSQL TRANSACTIONS

3. MYSQL TRANSACTIONS

4. MYSQL TRANSACTIONS NOTE: The behaviour of the transactions is controlled by using Autocommit session variable. By default, whenever you execute a SQL statement, MySQL immediately writes the result of the statement to the database. Another way to think about this is that the MySQL executes each statement as a separate transaction which implicitly starts when you execute the statement and ends as soon as the statement is done executing. This behaviour is called the *autocommit* mode. To view the current autocommit setting, type the following in the MySQL shell : As you can see, the @@autocommit system variable is set to 1 which means that the autocommit mode is enabled. If you disable the autocommit mode then the SQL statements that follow will be part of a transaction, and you will have to commit them manually using the COMMIT statement before the MySQL session ends , otherwise, the changes made by the statements will be rolled back.

5. MYSQL TRANSACTIONS CLOSING A CONNECTION Since the database can only keep a limited number of connections open at a time, we must close the connection. cursor.close() OR connection.close()

6. PRACTICAL IMPLEMENTATION:18 MYSQL TRANSACTIONS OUTPUT: Write all the queries in try only set auto-commit to false, i.e., we need to commit the transaction only when both the transactions complete successfully. After successful execution of both the queries, we committed our changes to the database Refer next page for details about format() in python In case of an exception or failure of one of the queries, we can revert our changes using a connection.rollback () Closing the connection of database We placed all our code in the “try-except” block to catch the database exceptions that may occur during the process.

7. PRACTICAL IMPLEMENTATION:18

8. PRACTICAL IMPLEMENTATION:18