python-sqlite3事務

標籤：class   select   nbsp   不能   大量新增資料   exe   sts   etc   tab   

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 * num def 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)

 

python-sqlite3事務