Analysis of the transaction processing function of MySQLdb in Python, pythonmysqldb

Analysis of the transaction processing function of MySQLdb in Python, pythonmysqldb

Preface

Any application cannot do without data. Therefore, when learning python, you must also learn how to use python to operate databases. MySQLdb is a python module for mysql database operations. Today I wrote a tool to insert csv data into the database. One part of the data is to insert two data entries into two tables respectively. If the data in the second table already exists, the data in the first table does not need to be inserted.

Then Baidu finds out that the MySQLdb library comes with the transaction processing function, and the pymysql library is the same.

Conn = MySQLdb. connect () returns a connection object. cursor = conn. cursor () returns a cursor object.

When we usecursor.execute(SQL)After executing an Insert/Update/Delete statement, we need to useconn.commit()Commit the transaction. Otherwise, the statement will not take effect.

In fact, we often usecommit() Contains the concept of transaction processing.commit()Previously, multiple statements were executed, only whencommit()And then all of them will take effect.

If we execute two insert statements as mentioned in the articleconn.commit()The statement is placed after two insert statements are executed and placed in the try statement block. If there is an exception, we can use:

conn.rollback()

This statement can roll back the transaction until the previous oneconn.commit()Location after execution.

Therefore, my code is written in this way. Here I will simply write it, and I will not use classes to encapsulate it:

try:cursor = conn.cursor()cursor.execute(insertSQL1)cursor.execute(insertSQL2)cursor.close()conn.commit()except Exception, e:cursor.close()conn.rollback()

Summary

This can achieve our goal. In fact, the more elegant way is to encapsulate transaction processing and write the processing process in your own class method, and accept a sequence of SQL statements (which may contain one or more SQL statements) as the parameter and return the transaction execution status. The above is all the content of this article. I hope the content of this article will help you in your study or work.