python Implementation in Mysql Data rollback rollback() And principle analysis
1.python Middle cursor multiple times execute Submit data code
import pymysql class MysqlDBUtils(object): def __init__(self, host, port,
username, password, db): self.host = host self.port = port self.username =
username self.password = password self.db = db self.conn = None self.cursor =
None def db_init(self, charset='utf8'): self.conn =
pymysql.connect(host=self.host, port=self.port, user=self.username,
password=self.password, db=self.db, charset=charset) self.cursor =
self.conn.cursor() def sql_execute(self): insert_sql1 = "INSERT INTO
t_user(user_id, user_name) VALUES (5, ' Hee hee ');" insert_sql2 = "INSERT INTO
T_HOBBY(user_id, hobby) VALUES (' I was wrong ID', ' riding ');" self.db_init() conn =
self.conn cursor = self.cursor try: cursor.execute(insert_sql1)
cursor.execute(insert_sql2) cursor.close() # Close cursor first except Exception as e:
#cursor.close() # Close cursor first conn.rollback() print(e) finally: cursor.close()
conn.commit() conn.close()
If insert_sql1 and insert_sql2 Without syntax errors , twice execute We'll be together at the end commit Make database submission , Write to database successfully
insert_sql1 = "INSERT INTO t_user(user_id, user_name) VALUES (5, ' peas ');"
insert_sql2 = "INSERT INTO T_HOBBY(user_id, hobby) VALUES ('2', ' riding ');"
But if insert_sql1 and insert_sql2 There is a syntax error
insert_sql1 = "INSERT INTO t_user(user_id, user_name) VALUES (5, ' Hee hee ');
" insert_sql2 = "INSERT INTO T_HOBBY(user_id, hobby) VALUES (' I was wrong ID', ' riding ');"
At this time, because there is db.rollback() Data rollback operation , therefore insert_sql1,insert_sql2 In cursor cursor Both operations in are cleared clear, So in the end, it's execution finally In db.commit() The operation will not insert_sql1 Successful implementation
You can try to db.rollback() Comment it out , that insert_sql1 The operation will be successful ,insert_sql1 Will be written to the database
summary ：db.rollback() The function of data rollback is to ensure the atomicity of database operation , Multiple operations are either executed , Or neither
Characteristics of database transaction
MySQL affair
MySQL Transactions are mainly used to handle large amount of operations , High complexity data . for instance , In the personnel management system , You delete a person , You need to delete the basic information of the personnel , Also delete the information related to the person , Such as mailbox , Articles and so on , such , These database operation statements constitute a transaction !
stay MySQL Only the Innodb Only databases or tables of the database engine support transactions .
Transaction processing can be used to maintain the integrity of the database , Mass production is guaranteed SQL Statements are either executed in full , Or not at all .
Transactions are used to manage insert,update,delete sentence
generally speaking , The transaction must be satisfied 4 Conditions （ACID）：： Atomicity （Atomicity, Or indivisibility ）, uniformity （Consistency）, Isolation （Isolation, Also known as independence ）, persistence （Durability）.
Atomicity ： A transaction （transaction） All operations in , Or it's all done , Or not at all , It won't end in the middle . An error occurred during the execution of the transaction , It will be rolled back （Rollback） To the state before the transaction begins , It's like this transaction has never been executed .
uniformity ： Before the transaction starts and after the transaction ends , The integrity of the database is not compromised . This means that the data written must fully comply with all the default rules , This includes the accuracy of the data , Serialization and subsequent databases can spontaneously complete the scheduled work .
Isolation ： Database allows multiple concurrent transactions to read, write and modify its data at the same time , Isolation can prevent data inconsistency caused by cross execution when multiple transactions are executed simultaneously . Transaction isolation is divided into different levels , Include read not submitted （Read uncommitted）, Read submit （read committed）, Repeatable reading （repeatable read） And serialization （Serializable）.
persistence ： After transaction , Modification of data is permanent , Even if the system fails, it will not be lost .( Nature reference )
Technology