本例以Windows, Python 3.4 为基础，来演示使用Python对事物的管理，当发生异常回滚提交。

相关

创建并：
Python(3.x) Create Table 创建表

创建SQL：

1
2
3
4
5
6
7
8
CREATE TABLE STUDENT (
    ID TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
    NAME VARCHAR (50) DEFAULT '' NOT NULL,
    AGE TINYINT DEFAULT NULL,
    BRD date DEFAULT NULL,
    CREATEDT datetime DEFAULT NULL,
    PRIMARY KEY (ID)
)

Transaction 事物的管理

一次提交多条数据，当发生异常时回滚整个事务，取消提交。

my_sql_transaction.py

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
import mysql.connector
from datetime import datetime
from datetime import date

#insert many records , roll back the commit when get exception 
def main(config):
    conn = mysql.connector.Connect(**config)
    
    cur = conn.cursor()
    
    tbl = "STUDENT"
    #insert data
    created = datetime.now()
    #has one age is to long , the age is invalid
    students = (("Jack", 27, date(1990, 3, 27), created), ("Zhao San", 21, date(1995, 10, 21), created),
                ("Zhao San", 1122, date(1995, 10, 21), created))
    
    stmt_insert = "Insert into {0} (name, age, brd, CREATEDT) values (%s, %s, %s, %s)".format(tbl)
    
    try:
        cur.executemany(stmt_insert, students)
    except (mysql.connector.errors.Error, TypeError) as exc:
        conn.rollback()
        print("Roll Back, Insert has error :", exc)
    else:
        conn.commit()
    finally:
        cur.close()
        conn.close()
        
        
if __name__ == '__main__':
    config = {
                'host': 'localhost',
                'port': 3306,
                'database': 'test',
                'user': 'root',
                'password': '!qaz2wsx',
                'charset': 'utf8',
                'use_unicode': True,
                'get_warnings': True,
            }   
    main(config)

运行结果：

1
Roll Back, Insert has error : 1264 (22003): Out of range value for column 'AGE' at row 3