python-sqlite3事务

在connect()中不传入 isolation_level

事务处理:

使用connection.commit()

<span style="color: #008000">#</span><span style="color: #008000">!/usr/bin/env python</span><span style="color: #008000">
#</span><span style="color: #008000"> -*- coding:utf-8 -*-</span>
<span style="color: #800000">‘‘‘</span><span style="color: #800000">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
</span><span style="color: #800000">‘‘‘</span>
<span style="color: #0000ff">import</span><span style="color: #000000"> sys
</span><span style="color: #0000ff">import</span><span style="color: #000000"> time
</span><span style="color: #0000ff">class</span><span style="color: #000000"> Elapse_time(object):
    </span><span style="color: #800000">‘‘‘</span><span style="color: #800000">耗时统计工具</span><span style="color: #800000">‘‘‘</span>
    <span style="color: #0000ff">def</span> <span style="color: #800080">__init__</span>(self, prompt=<span style="color: #800000">‘‘</span><span style="color: #000000">):
        self.prompt </span>=<span style="color: #000000"> prompt
        self.start </span>=<span style="color: #000000"> time.time()
        
    </span><span style="color: #0000ff">def</span> <span style="color: #800080">__del__</span><span style="color: #000000">(self):
        </span><span style="color: #0000ff">print</span>(<span style="color: #800000">‘</span><span style="color: #800000">%s耗时: %.3f</span><span style="color: #800000">‘</span> % (self.prompt, time.time() -<span style="color: #000000"> self.start))
CElapseTime </span>=<span style="color: #000000"> Elapse_time
</span><span style="color: #0000ff">import</span><span style="color: #000000"> sqlite3
</span><span style="color: #008000">#</span><span style="color: #008000"> -------------------------------------------------------------------------------</span><span style="color: #008000">
#</span><span style="color: #008000"> 测试</span><span style="color: #008000">
#
</span><span style="color: #000000">
filename </span>= <span style="color: #800000">‘</span><span style="color: #800000">e:/temp/a.db</span><span style="color: #800000">‘</span>
<span style="color: #0000ff">def</span> prepare(isolation_level = <span style="color: #800000">‘‘</span><span style="color: #000000">):
    connection </span>= sqlite3.connect(filename, isolation_level =<span style="color: #000000"> isolation_level)
    connection.execute(</span><span style="color: #800000">"</span><span style="color: #800000">create table IF NOT EXISTS  people (num, age)</span><span style="color: #800000">"</span><span style="color: #000000">)
    connection.execute(</span><span style="color: #800000">‘</span><span style="color: #800000">delete from people</span><span style="color: #800000">‘</span><span style="color: #000000">)
    connection.commit()
    </span><span style="color: #0000ff">return</span><span style="color: #000000"> connection, connection.cursor()
</span><span style="color: #0000ff">def</span><span style="color: #000000"> db_insert_values(cursor, count):
    num </span>= 1<span style="color: #000000"> 
    age </span>= 2 *<span style="color: #000000"> num 
    
    </span><span style="color: #0000ff">while</span> num <=<span style="color: #000000"> count:
        cursor.execute(</span><span style="color: #800000">"</span><span style="color: #800000">insert into people values (?, ?)</span><span style="color: #800000">"</span><span style="color: #000000">, (num, age))
        num </span>+= 1<span style="color: #000000">
        age </span>= 2 *<span style="color: #000000"> num 
</span><span style="color: #0000ff">def</span><span style="color: #000000"> study_case1_intelligent_commit(count):
    </span><span style="color: #800000">‘‘‘</span><span style="color: #800000">
    在智能commit状态下, 不能和cur.execute("COMMIT")共用
    </span><span style="color: #800000">‘‘‘</span><span style="color: #000000">
    connection, cursor </span>=<span style="color: #000000"> prepare()
    
    elapse_time </span>= Elapse_time(<span style="color: #800000">‘</span><span style="color: #800000">  智能commit</span><span style="color: #800000">‘</span><span style="color: #000000">)
    db_insert_values(cursor, count)
    </span><span style="color: #008000">#</span><span style="color: #008000">cursor.execute("COMMIT")  #产生异常</span>
<span style="color: #000000">    
    cursor.execute(</span><span style="color: #800000">"</span><span style="color: #800000">select count(*) from people</span><span style="color: #800000">"</span><span style="color: #000000">)
    </span><span style="color: #0000ff">print</span><span style="color: #000000"> (cursor.fetchone())
    
</span><span style="color: #0000ff">def</span><span style="color: #000000"> study_case2_autocommit(count):
    connection, cursor </span>= prepare(isolation_level =<span style="color: #000000"> None)
    
    elapse_time </span>= Elapse_time(<span style="color: #800000">‘</span><span style="color: #800000">  自动commit</span><span style="color: #800000">‘</span><span style="color: #000000">)
    db_insert_values(cursor, count)
    
    cursor.execute(</span><span style="color: #800000">"</span><span style="color: #800000">select count(*) from people</span><span style="color: #800000">"</span><span style="color: #000000">)    
    </span><span style="color: #0000ff">print</span><span style="color: #000000"> (cursor.fetchone())
</span><span style="color: #0000ff">def</span><span style="color: #000000"> study_case3_intelligent_commit_manual(count):
    connection, cursor </span>=<span style="color: #000000"> prepare()
    
    elapse_time </span>= Elapse_time(<span style="color: #800000">‘</span><span style="color: #800000">  智能commit即时提交</span><span style="color: #800000">‘</span><span style="color: #000000">)
    db_insert_values(cursor, count)
    connection.commit()
    
    cursor.execute(</span><span style="color: #800000">"</span><span style="color: #800000">select count(*) from people</span><span style="color: #800000">"</span><span style="color: #000000">)
    </span><span style="color: #0000ff">print</span><span style="color: #000000"> (cursor.fetchone())
</span><span style="color: #0000ff">def</span><span style="color: #000000"> study_case4_autocommit_transaction(count):
    connection, cursor </span>= prepare(isolation_level =<span style="color: #000000"> None)
    
    elapse_time </span>= Elapse_time(<span style="color: #800000">‘</span><span style="color: #800000">  自动commit</span><span style="color: #800000">‘</span><span style="color: #000000">)
    connection.execute(</span><span style="color: #800000">"</span><span style="color: #800000">BEGIN TRANSACTION;</span><span style="color: #800000">"</span>) <span style="color: #008000">#</span><span style="color: #008000"> 关键点</span>
<span style="color: #000000">    db_insert_values(cursor, count)
    connection.execute(</span><span style="color: #800000">"</span><span style="color: #800000">COMMIT;</span><span style="color: #800000">"</span>)  <span style="color: #008000">#</span><span style="color: #008000">关键点</span>
<span style="color: #000000">    
    cursor.execute(</span><span style="color: #800000">"</span><span style="color: #800000">select count(*) from people;</span><span style="color: #800000">"</span><span style="color: #000000">)
    </span><span style="color: #0000ff">print</span><span style="color: #000000"> (cursor.fetchone())
</span><span style="color: #0000ff">if</span> <span style="color: #800080">__name__</span> == <span style="color: #800000">‘</span><span style="color: #800000">__main__</span><span style="color: #800000">‘</span><span style="color: #000000">:
    count </span>= 10000<span style="color: #000000">
    prepare()
    </span><span style="color: #0000ff">for</span> i <span style="color: #0000ff">in</span> range(5<span style="color: #000000">):
        </span><span style="color: #008000">#</span><span style="color: #008000">study_case1_intelligent_commit(count) #不提交数据</span>
        <span style="color: #008000">#</span><span style="color: #008000">study_case2_autocommit(count)  #非常缓慢</span>
<span style="color: #000000">        study_case3_intelligent_commit_manual(count)
        study_case4_autocommit_transaction(count)</span>

python-sqlite3事务

标签：class   select   nbsp   不能   批量添加数据   exe   sts   etc   tab