mysql.connector 事务总结
时间：2021-07-01 10:21:17
帮助过：5人阅读
!/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(‘\n‘.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)
mysql.connector 事务总结
标签：begin connector ids order by 工具 val nod opp odi
