本文实例讲述了Python sqlite3事务处理方法。分享给大家供大家参考，具体如下：
sqlite3事务总结:
在connect()中不传入 isolation_level
事务处理:
使用connection.commit()
#!/usr/bin/env python# -*- coding:utf-8 -*-'''sqlite3事务总结:在connect()中不传入 isolation_level事务处理: 使用connection.commit()分析: 智能commit状态: 生成方式: 在connect()中不传入 isolation_level, 此时isolation_level=='' 在进行 执行Data Modification Language (DML) 操作(INSERT/UPDATe/DELETE/REPLACE)时, 会自动打开一个事务, 在执行 非DML, 非query (非 SELECT 和上面提到的)语句时, 会隐式执行commit 可以使用 connection.commit()方法来进行提交 注意: 不能和cur.execute("COMMIT")共用 自动commit状态: 生成方式: 在connect()中传入 isolation_level=None 这样,在任何DML操作时,都会自动提交 事务处理 connection.execute("BEGIN TRANSACTION") connection.execute("COMMIT") 如果不使用事务, 批量添加数据非常缓慢数据对比: 两种方式, 事务耗时差别不大 count = 100000 智能commit即时提交耗时: 0.621 自动commit耗时: 0.601 智能commit即时提交耗时: 0.588 自动commit耗时: 0.581 智能commit即时提交耗时: 0.598 自动commit耗时: 0.588 智能commit即时提交耗时: 0.589 自动commit耗时: 0.602 智能commit即时提交耗时: 0.588 自动commit耗时: 0.622'''import sysimport timeclass 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_timeimport sqlite3# -------------------------------------------------------------------------------# 测试#filename = 'e:/temp/a.db'def prepare(isolation_level = ''): connection = sqlite3.connect(filename, isolation_level = isolation_level) connection.execute("create table IF NOT EXISTS people (num, age)") connection.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 (?, ?)", (num, age)) num += 1 age = 2 * numdef study_case1_intelligent_commit(count): ''' 在智能commit状态下, 不能和cur.execute("COMMIT")共用 ''' connection, cursor = prepare() elapse_time = Elapse_time(' 智能commit') db_insert_values(cursor, count) #cursor.execute("COMMIT") #产生异常 cursor.execute("select count(*) from people") print (cursor.fetchone())def study_case2_autocommit(count): connection, cursor = prepare(isolation_level = None) elapse_time = Elapse_time(' 自动commit') db_insert_values(cursor, count) cursor.execute("select count(*) from people") print (cursor.fetchone())def study_case3_intelligent_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_case4_autocommit_transaction(count): connection, cursor = prepare(isolation_level = None) elapse_time = Elapse_time(' 自动commit') connection.execute("BEGIN TRANSACTION;") # 关键点 db_insert_values(cursor, count) connection.execute("COMMIT;") #关键点 cursor.execute("select count(*) from people;") print (cursor.fetchone())if __name__ == '__main__': count = 10000 prepare() for i in range(5): #study_case1_intelligent_commit(count) #不提交数据 #study_case2_autocommit(count) #非常缓慢 study_case3_intelligent_commit_manual(count) study_case4_autocommit_transaction(count)
