Mysql python cursor commit

Use Commit and Rollback to Manage MySQL Transactions in Python

This lesson mainly focuses on how to manage Database transactions while working with the MySQL database in Python. Learn Python MySQL transaction management using commit and rollback using ‘Mysql connector python’ module.

Further Reading:

Table of contents

What is Database Transaction

The database transaction represents a single unit of work. Any operation which modifies the state of the MySQL database is a transaction. Let see in detail what is database transaction. For example, take a sample of a Bank amount transfer, which involves two significant transactions.

If the first Transaction is executed successfully but the second failed, in this case, we need to re-deposit money back to account A. To manage such instances, we need transaction management.

Using ACID properties, we can study transaction management well. ACID stands for Atomicity, Consistency, isolation, and durability.

• Atomicity: means all or nothing. Either all transactions are successful or none. You can group SQL statements as one logical unit, and if any query fails, the whole transaction fails.
• Consistency: It ensures that the database remains in a consistent state after performing a transaction.
• Isolation: It ensures that the transaction is isolated from other transactions.
• Durability: It means once a transaction has been committed, it persists in the database irrespective of power loss, error, or restart system.

Python MySQL Commit(), rollback() and setAutoCommit() to manage transactions

Please follow the below steps to manage MySQL transactions in Python: –

• Create MySQL database connections in Python.
• Prepare the SQL queries that you want to run as a part of a transaction. For example, we can combine two SQL queries(withdrawal money and deposit money query) in a single transaction.
• Set an auto-commit property of MySQL connection to false.
• Execute all queries one by one using the cursor.execute()
• If all queries execute successfully, commit the changes to the database
• If one of the queries failed to execute, then rollback all the changes.
• Catch any SQL exceptions that may occur during this process
• Close the cursor object and MySQL database connection

Methods to manage MySQL Database Transactions in Python

Python MySQL Connector provides the following method to manage database transactions.

• commit() : MySQLConnection.commit() method sends a COMMIT statement to the MySQL server, committing the current transaction. After the successful execution of a query make changes persistent into a database using the commit() of a connection class.
• rollback() : MySQLConnection.rollback revert the changes made by the current transaction. When one of the transactions fails to execute, and you want to revert or undo all your changes, call a rollback method of MySQL connection object.
• autoCommit() : MySQLConnection.autocommit value can be as True or False to enable or disable the auto-commit feature of MySQL. By default its value is False.

Python example to manage MySQL transactions using commit and rollback

import mysql.connector try: conn = mysql.connector.connect(host='localhost', database='python_db', user='pynative', password='pynative@#29') conn.autocommit = False cursor = conn.cursor() # withdraw from account A sql_update_query = """Update account_A set balance = 1000 where cursor.execute(sql_update_query) # Deposit to account B sql_update_query = """Update account_B set balance = 1500 where cursor.execute(sql_update_query) print("Record Updated successfully ") # Commit your changes conn.commit() except mysql.connector.Error as error: print("Failed to update record to database rollback: <>".format(error)) # reverting changes because of exception conn.rollback() finally: # closing database connection. if conn.is_connected(): cursor.close() conn.close() print("connection is closed") 

Output if the query executes successfully.

Record Updated successfully

You should get the following output if a query fails to execute.

Failed to update record to database rollback

Let’s understand the above code: –

• We imported the MySQL connector python module so we can use its API to communicate with MySQL Database.
• After a successful MySQL connection, we set auto-commit to False , i.e., we need to commit the transaction only when both the transactions complete successfully.
• We prepared two update SQL queries as a part of a single transaction to deposit money to account B from account A.
• We executed both the queries one by one using a cursor.execute() method.
• After successful execution of both 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.

Next Steps:

To practice what you learned in this article, Please solve a Python Database Exercise project to Practice and master the Python Database operations.

Did you find this page helpful? Let others know about it. Sharing helps me continue to create free Python resources.

About Vishal

I’m Vishal Hule, Founder of PYnative.com. I am a Python developer, and I love to write articles to help students, developers, and learners. Follow me on Twitter

Related Tutorial Topics:

Python Exercises and Quizzes

Free coding exercises and quizzes cover Python basics, data structure, data analytics, and more.

• 15+ Topic-specific Exercises and Quizzes
• Each Exercise contains 10 questions
• Each Quiz contains 12-15 MCQ

Источник

Выполнение транзакций в MySQL Python – свойства и методы

Выполнение транзакций в MySQL Python обеспечивает согласованность данных в базе данных. Мы должны убедиться, что несколько приложений не должны изменять записи при выполнении операций с базой данных. Транзакции обладают следующими свойствами:

1. Атомарность.Либо транзакция завершается, либо ничего не происходит. Если транзакция содержит 4 запроса, то все эти запросы должны быть выполнены или ни один из них не должен быть выполнен.
2. Последовательность.База данных должна быть согласованной до начала транзакции, а также после завершения транзакции.
3. Изоляция.Промежуточные результаты транзакции не видны за пределами текущей сессии.
4. Долговечность.После того, как транзакция была зафиксирована, эффекты сохраняются даже после сбоя системы.

Метод commit() в Python

Python предоставляет метод commit(), который обеспечивает внесение изменений в базу данных последовательно.

Синтаксис для использования метода commit() приведен ниже.

conn.commit() #conn is the connection object

Все операции, которые изменяют записи базы данных, не выполняются до тех пор, пока не будет вызвана функция commit().

Метод rollback()

Метод rollback() используется для отмены изменений, внесенных в базу данных. Этот метод полезен в том смысле, что, если во время операций с базой данных возникает какая-то ошибка, мы можем откатить эту транзакцию для поддержания согласованности базы данных.

Синтаксис использования функции rollback() приведен ниже.

Закрытие соединения

Нам нужно закрыть соединение с базой данных после того, как мы выполнили все операции с ней. Python предоставляет метод close(). Синтаксис использования метода close() представлен ниже.

В следующем примере мы удалим всех сотрудников, которые работают в отделе CS.

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()

Источник

What is Python commit() method in MySQL?

The commit() method is one among the various methods in Python which is used to make the database transactions.

Here, we will discuss about the commit() method. The commit() method is used to confirm the changes made by the user to the database. Whenever any change is made to the database using update or any other statements, it is necessary to commit the changes. If we donot use the commit() method after making any changes to the database, the database will not not be updated and changes will not be reflected.

Syntax

db refers to the database connection object.

Given below is an example to update value in a table and commit the changes to the database.

Steps invloved to update data and commit change made in a table using MySQL in python

• import MySQL connector
• establish connection with the connector using connect()
• create the cursor object using cursor() method
• create a query using the appropriate mysql statements
• execute the SQL query using execute() method
• commit the changes made using commit() method
• close the connection

Suppose we have a table named “Student” as follows −

+----------+---------+-----------+------------+ | Name | Class | City | Marks | +----------+---------+-----------+------------+ | Karan | 4 | Amritsar | 95 | | Sahil | 6 | Amritsar | 93 | | Kriti | 3 | Batala | 88 | | Khushi | 9 | Delhi | 90 | | Kirat | 5 | Delhi | 85 | +----------+---------+-----------+------------+

Example

Suppose, we have the above table of students and we want to update the city of Kriti from Batala to Kolkata. And commit the changes to the database.

import mysql.connector db=mysql.connector.connect(host="your host", user="your username", password="your password",database="database_name") cursor=db.cursor() query="UPDATE Students SET City='Kolkata' WHERE Name='Kriti'" cursor.execute(query) db.commit() query="SELECT * FROM Students" cursor.execute(query) for row in cursor: print(row) db.close()

The above code updates the city name of Kriti and commits this change to the database.

Output

(‘Karan’, 4 ,’Amritsar’ , 95) (‘Sahil’ , 6 , ‘Amritsar’ ,93) (‘Kriti’ , 3 , ‘Kolkata’ ,88) (‘Amit’ , 9 , ‘Delhi’ , 90) (‘Priya’ , 5 , ‘Delhi’ ,85)

NOTE

The db.commit() in the above code is important. It is used to commit the changes made to the table. Without using commit(), no changes will be made in the table.

Источник