sqlite3 Transaction Summary:
Do not pass in Isolation_level in Connect ()
Transaction processing:
Using Connection.commit ()
#!/usr/bin/env python#-*-coding:utf-8-*-" "sqlite3 transaction summary: Do not pass in Isolation_level transaction in Connect (): Use Connection.commit () Analysis: Smart Commit Status: Generated by: Do not pass in the Isolation_level in Connect (), at which point isolation_level== "executes the data modification Language (DML) operation (INS Ert/update/delete/replace), a transaction is automatically opened when executing non-DML, non-query (non-SELECT and above-mentioned) statements, and the commit can be executed implicitly using conn Ection.commit () method to commit note: Cannot share automatic commit state with Cur.execute ("commit"): generated by: Incoming Isol in Connect () Ation_level=none this will automatically commit the transaction Connection.Execute ("BEGIN TRANSACTION") when any DML operation Connection.Execute ("COMMIT") if you do not use transactions, bulk add data is very slow data comparison: Two ways, transaction time difference is not small count = 100000 Smart Commit Instant Commit time: 0. 621 Automatic commit time: 0.601 Smart Commit Instant Commit time: 0.588 Automatic commit time: 0.581 Smart Commit Instant Commit time: 0.598 automatic C Ommit Time: 0.588 Smart Commit Instant Commit time: 0.589 Automatic commit time: 0.602 Smart Commit Instant Commit time: 0.588 Automatic commit time: 0.622 " "ImportSYSImport TimeclassElapse_time (object):" "time-consuming statistics tool" " def __init__(Self, prompt="'): Self.prompt=prompt Self.start=time.time ()def __del__(self):Print('%s Time:%.3f'% (Self.prompt, time.time ()-Self.start)) Celapsetime=Elapse_timeImportSqlite3# -------------------------------------------------------------------------------#Test#filename='e:/temp/a.db'defPrepare (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 ()returnconnection, Connection.cursor ()defdb_insert_values (cursor, count): Num= 1 Age= 2 *Num whileNum <=Count:cursor.execute ("INSERT into people values (?,?)", (num, age)) Num+ = 1 Age= 2 *NumdefStudy_case1_intelligent_commit (count):" "cannot be shared with Cur.execute ("commit") in smart commit state" "connection, cursor=prepare () Elapse_time= Elapse_time ('Smart Commit') db_insert_values (cursor, count)#cursor.execute ("COMMIT") #产生异常Cursor.execute ("Select COUNT (*) from people") Print(Cursor.fetchone ())defStudy_case2_autocommit (count): connection, cursor= Prepare (Isolation_level =None) Elapse_time= Elapse_time ('Automatic Commit') db_insert_values (cursor, count) cursor.execute ("Select COUNT (*) from people") Print(Cursor.fetchone ())defstudy_case3_intelligent_commit_manual (count): connection, cursor=prepare () Elapse_time= Elapse_time ('Smart Commit Instant Commit') db_insert_values (cursor, count) connection.commit () Cursor.execute ("Select COUNT (*) from people") Print(Cursor.fetchone ())defstudy_case4_autocommit_transaction (count): connection, cursor= Prepare (Isolation_level =None) Elapse_time= Elapse_time ('Automatic Commit') Connection.Execute ("BEGIN TRANSACTION;")#Key pointsdb_insert_values (cursor, count) Connection.Execute ("COMMIT;")#Key pointsCursor.execute ("Select COUNT (*) from people;") Print(Cursor.fetchone ())if __name__=='__main__': Count= 10000Prepare () forIinchRange (5): #Study_case1_intelligent_commit (count) #不提交数据 #Study_case2_autocommit (count) #非常缓慢study_case3_intelligent_commit_manual (count) study_case4_autocommit_transaction (Count )
Python-sqlite3 transactions