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): = 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(, 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 )