#!/usr/bin/env python
# -*- coding:utf-8 -*-
'''sqlite3事務總結:
在connect()中不傳入 isolation_level
事務處理:
  使用connection.commit()
分析:
  智能commit狀態(tài):
    生成方式: 在connect()中不傳入 isolation_level, 此時isolation_level==''
      在進行 執(zhí)行Data Modification Language (DML) 操作(INSERT/UPDATE/DELETE/REPLACE)時, 會自動打開一個事務,
      在執(zhí)行 非DML, 非query (非 SELECT 和上面提到的)語句時, 會隱式執(zhí)行commit
      可以使用 connection.commit()方法來進行提交
    注意:
      不能和cur.execute("COMMIT")共用
  自動commit狀態(tài):
    生成方式: 在connect()中傳入 isolation_level=None
      這樣,在任何DML操作時,都會自動提交
    事務處理
      connection.execute("BEGIN TRANSACTION")
      connection.execute("COMMIT")
    如果不使用事務, 批量添加數(shù)據(jù)非常緩慢
數(shù)據(jù)對比:
  兩種方式, 事務耗時差別不大
  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 sys
import time
class Elapse_time(object):
  '''耗時統(tǒng)計工具'''
  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 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 * num
def study_case1_intelligent_commit(count):
  '''
  在智能commit狀態(tài)下, 不能和cur.execute("COMMIT")共用
  '''
  connection, cursor = prepare()
  elapse_time = Elapse_time(' 智能commit')
  db_insert_values(cursor, count)
  #cursor.execute("COMMIT") #產(chǎn)生異常
  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) #不提交數(shù)據(jù)
    #study_case2_autocommit(count) #非常緩慢
    study_case3_intelligent_commit_manual(count)
    study_case4_autocommit_transaction(count)