Python executes MySQL transaction

One, MySQL business

A transaction is a logical set of SQL operations that make up the individual SQL statements of this set of operations, either completely successful or fail at execution time.

For example, Xiao Ming to the small red transfer 100 yuan, the transfer process is actually Xiao Ming's account to reduce 100 yuan, Red's account increased by 100 yuan, the corresponding SQL statement is:

Update account set money=money-5 where name= ' xiaoming ';
Update account set money=money+5 where name= ' Xiaohong ';

The above two SQL operations, the operation in the transaction is either the execution of success, or all failed, if only the first success, then Xiaoming lost 100 yuan, and Little Red did not receive 100 yuan, this is not advisable, so this is the transaction, the transaction refers to a logical set of SQL operations, The individual SQL statements that make up this set of operations are either fully successful or fail at execution time. Transactions can be used to maintain the integrity of the database, to ensure that the batch of SQL statements are either all executed or not, and that only databases or tables using the INNODB library engine in MySQL support transactions, so in many cases we use the INNODB engine.

The characteristics of the transaction are as follows:

Atomicity: A transaction is an indivisible unit, and all SQL operations in a transaction either succeed or fail
Consistency: Data integrity must be consistent before and after a transaction occurs
Isolation: When a database is concurrently accessed, an executing transaction is not visible to other sessions until the execution is complete, and the data between multiple concurrent transactions is isolated from each other
Persistence: Once a transaction is committed, its changes to the data in the database are permanent, and if an error is made, the transaction is not allowed to be revoked, only through the "compensatory transaction"

Mysql> begin      # Open transaction mysql> rollback   # ROLLBACK TRANSACTION mysql> commit     # COMMIT TRANSACTION

The database default transaction is automatically committed, that is, when we perform operations such as Select,insert,update,delete, the transaction is automatically committed, if the transaction is closed automatically commit, then we finish select,insert,update, A commit is required after the delete operation to commit the transaction, otherwise it will not execute

Second, the cursor

A cursor is a data buffer opened by the system for the user, which holds the results of the execution of the SQL statement as follows:

in [1]: Import Mysqldbin [2]: c = MySQLdb.connect (user='Root', passwd='pzk123', db='MySQL') # Connect database in [3]: cus =c.cursor () # Creates a cursor object in [4]: Cus.execute ('select * from user;' # Use the Execute () method to execute the SQL statement, and the result of the execution will be a buffer out[4]:4LIn [5]: RESULT1 =Cus.fetchone () # can use Fetchone () to view a record of a buffer in [6]: Result2 = Cus.fetchmany (3 # You can use Fetchmany () to view multiple records of a buffer in [7]: RESULT3 = Cus.fetchall ()# You can use Fetchall () to view all the records

Third, execute MySQL transaction according to Cursor

#!/usr/bin/env pythonimport mysqldbdef connect_mysql (): Db_config= {        'Host':'127.0.0.1',        'Port':3306,        'User':'Root',        'passwd':'pzk123'} C= MySQLdb.connect (* *db_config)returnCif__name__ = ='__main__': C=Connect_mysql () # First connect the database cus=c.cursor () # Generate Cursor object SQL='drop database test;'# define the SQL statement to executeTry: Cus.execute (SQL) # Execute SQL statement c.commit () # COMMIT TRANSACTION if execution succeeds Except Exception asE:c.rollback () # ROLLBACK TRANSACTION if execution fails raise efinally: C.close () # Finally, remember to close the database connection 

Python executes MySQL transaction