#!/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)