mysql.connector事务总结:
connection.autocommit = 0 (默认值)
事务处理
使用 connection.commit()方法
#!/usr/bin/env python # -*- coding:utf-8 -*- '''mysql.connector事务总结: connection.autocommit = 0 (默认值) 事务处理 使用 connection.commit()方法 分析: 智能commit状态: connection.autocommit = 0 (默认值) 默认不提交 事务处理 可以使用 connection.commit()方法来进行提交 自动commit状态: connection.autocommit = 0 这样,在任何DML操作时,都会自动提交 事务处理 connection.execute("BEGIN;") connection.commit() 如果不使用事务, 批量添加数据相对缓慢 两种方式, 事务耗时差别不大 自动commit的速度受网络传输影响大 比较数据: 192.168.1.107, count=100 默认commit事务耗时: 0.152 自动commit, cursor.execute("COMMIT;")耗时: 0.139 自动commit2, connection.commit()耗时: 0.143 自动commit,非事务耗时: 0.397 192.168.1.107, count=1000 默认commit事务耗时: 1.365 自动commit, cursor.execute("COMMIT;")耗时: 1.389 自动commit2, connection.commit()耗时: 1.291 自动commit,非事务耗时: 3.871 192.168.6.226, count=100 默认commit事务耗时: 0.178 自动commit, cursor.execute("COMMIT;")耗时: 0.183 自动commit2, connection.commit()耗时: 0.192 自动commit,非事务耗时: 1.965 ''' import sys import time class Elapse_time(object): '''耗时统计工具''' def __init__(self, prompt=''): self.prompt = prompt self.start = time.time() def __del__(self): print('%s耗时: %.3f' % (self.prompt, time.time() - self.start)) CElapseTime = Elapse_time import mysql.connector # ------------------------------------------------------------------------------- # 测试 # db_parameters = {'host': '192.168.1.107', 'database': 'test', 'charset': 'utf8'} db_parameters1 = {'host': '192.168.6.226', 'database': 'test', 'charset': 'utf8'} def prepare(isolation_level = ''): connection = mysql.connector.MySQLConnection(**db_parameters) cursor = connection.cursor() cursor.execute("create table IF NOT EXISTS people (num int, age int)") cursor.execute('delete from people') connection.commit() return connection, connection.cursor() def db_insert_values(cursor, count): num = 1 age = 2 * num while num <= count: cursor.execute("insert into people values (%s, %s)", (num, age)) num += 1 age = 2 * num def study_case1_default_commit_manual(count): connection, cursor = prepare() elapse_time = Elapse_time(' 默认commit事务') db_insert_values(cursor, count) connection.commit() cursor.execute("select count(*) from people") print (cursor.fetchone()) def study_case2_autocommit_transaction(count): connection, cursor = prepare(isolation_level = None) connection.autocommit = 1 elapse_time = Elapse_time(' 自动commit, cursor.execute("COMMIT;")') cursor.execute("BEGIN;") # 关键点 db_insert_values(cursor, count) cursor.execute("COMMIT;") #关键点 cursor.execute("select count(*) from people;") print (cursor.fetchone()) def study_case3_autocommit_transaction2(count): connection, cursor = prepare(isolation_level = None) connection.autocommit = 1 elapse_time = Elapse_time(' 自动commit2, connection.commit()') cursor.execute("BEGIN;") # 关键点 db_insert_values(cursor, count) connection.commit() cursor.execute("select count(*) from people;") print (cursor.fetchone()) def study_case4_autocommit_no_transaction(count): connection, cursor = prepare(isolation_level = None) connection.autocommit = 1 elapse_time = Elapse_time(' 自动commit,非事务') db_insert_values(cursor, count) cursor.execute("select count(*) from people;") print (cursor.fetchone()) def main(config): output = [] db = mysql.connector.Connect(**config) cursor = db.cursor() # Drop table if exists, and create it new stmt_drop = "DROP TABLE IF EXISTS names" cursor.execute(stmt_drop) stmt_create = """ CREATE TABLE names ( id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(30) DEFAULT '' NOT NULL, cnt TINYINT UNSIGNED DEFAULT 0, PRIMARY KEY (id) ) ENGINE=InnoDB""" cursor.execute(stmt_create) warnings = cursor.fetchwarnings() if warnings: ids = [ i for l,i,m in warnings] output.append("Oh oh.. we got warnings..") if 1266 in ids: output.append(""" Table was created as MYISAM, no transaction support. Bailing out, no use to continue. Make sure InnoDB is available! """) db.close() return # Insert 3 records output.append("Inserting data") names = ( ('Geert',), ('Jan',), ('Michel',) ) stmt_insert = "INSERT INTO names (name) VALUES (%s)" cursor.executemany(stmt_insert, names) # Roll back!!!! output.append("Rolling back transaction") db.rollback() # There should be no data! stmt_select = "SELECT id, name FROM names ORDER BY id" cursor.execute(stmt_select) rows = None try: rows = cursor.fetchall() except mysql.connector.InterfaceError as e: raise if rows == []: output.append("No data, all is fine.") else: output.append("Something is wrong, we have data although we rolled back!") output.append(rows) cursor.close() db.close() return output # Do the insert again. cursor.executemany(stmt_insert, names) # Data should be already there cursor.execute(stmt_select) output.append("Data before commit:") for row in cursor.fetchall(): output.append("%d | %s" % (row[0], row[1])) # Do a commit db.commit() cursor.execute(stmt_select) output.append("Data after commit:") for row in cursor.fetchall(): output.append("%d | %s" % (row[0], row[1])) # Cleaning up, dropping the table again cursor.execute(stmt_drop) cursor.close() db.close() return output if __name__ == '__main__': #out = main(db_parameters) #print(' '.join(out)) count = 1000 prepare() for i in range(1): study_case1_default_commit_manual(count) study_case2_autocommit_transaction(count) study_case3_autocommit_transaction2(count) study_case4_autocommit_no_transaction(count)