Transaction processing in the "MySQL" MySQLdb

The MySQL database has a concept of autocommit transactions, autocommit. The implication is that if autocommit is turned on, each statement is automatically committed after execution. That is, a statement is treated as a transaction.

In the mysqldb used by Python, autocommit is not turned on by default. Therefore, the database operation is actually committed only after the commit is displayed. Or, after rollback (), roll back to the state of the last commit.

Cases:

#!/Bin/Env python#coding=Utf-8Import Mysqldbclass MYSQL (object): Def __init__ (self): self.db=MySQLdb.connect ("localhost", "root", "12345678"," TestTable ", CharSet='UTF8') self.cursor =Self.db.cursor() def test (self): Try:sql="Insert  intoTEST_DISTINCT (name, type)Values('T3','1') "Self.cursor.Execute(SQL) SQL="UpdateTest_distinctSetType='2' whereName='T3'"#raise #打开用于测试异常 self.cursor.Execute(SQL)except: self.db.rollback() #出现异常, no data changes will be submittedElse: self.db.Commit() #正常处理, submit data changes (without this sentence, will not commit any changes)if__name__=="__main__": obj=MYSQL () obj.test ()

Scenario: There is a query statement directly in the two-time INSERT statement

#!/bin/env python#Coding=utf-8ImportMySQLdbclassMYSQL (object):def __init__(self): self.db= MySQLdb.connect ("localhost","Root","12345678","testtable", charset='UTF8') Self.cursor=self.db.cursor ()defTest (self):Try: Name='T5'            #Insertsql ="INSERT into TEST_DISTINCT (name, type) values ('%s ', ' 1 ')"%name Self.cursor.execute (SQL)#Searchsql ="SELECT * from test_distinct where name = '%s '"%name #查询新插入的数据 self.cursor.execute (SQL) Res=Self.cursor.fetchone ()PrintRes#Insertsql ="Update test_distinct set type= ' 2 ' where name= '%s '"%nameRaise#引起异常 self.cursor.execute (SQL)except: Self.db.rollback ()Else: Self.db.commit ()if __name__=="__main__": obj=MYSQL () obj.test ()

Results:

The newly inserted data can be queried correctly, and the data is successfully rolled back without writing the data.

Conclusion: While there is no commit, the database will not really change, but there will be a temporary change in the version for us to query the data that has not yet been actually added.

Transaction processing in the "MySQL" MySQLdb