A brief analysis of the transaction processing functions of MySQLdb in Python

preface

Any application can't live without data, so when you learn python, you should also learn how to use python to operate a database. MySQLdb is the module that python operates on mysql database. Today I wrote a tool to insert data from csv into a database. In the first part, two data need to be inserted into two tables respectively. If the data in the second table already exists, then the data in the first table need not be inserted.

Then through baidu search found that, in fact, MySQLdb library, with the function of transaction processing, pymysql library is also a kind.

conn = MySQLdb.connect() return 1 Connection objects 

cursor = conn.cursor() return 1 Vernier objects 

When we use cursor.execute(SQL) After executing an Insert/Update/Delete statement, we need to use conn.commit() Commit the transaction, otherwise the statement will not take effect.

Actually we use it a lot commit() That is, it includes a concept of transaction processing, if you're in commit() Previously, multiple statements were executed, only when commit() After that, it will all take effect.

So if, as mentioned at the beginning of this article, we execute two insert statements, we can put conn.commit() The statement is placed in the try statement block after the execution of the two insert statements. If there is an exception, we can use:

conn.rollback()

This statement rolls the transaction back up to the last one conn.commit() After execution.

So, here's what my code looks like, and I'm just going to write it here, without using 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()

conclusion

This will serve our purpose. In fact, a more elegant method is to encapsulate the transaction processing, write the process in the method of its own class, and accept a sequence of SQL statements (possibly including one or more SQL statements) as parameters, and return the transaction execution status. The above is the entire content of this article, I hope the content of this article to your study or work can bring 1 definite help.