Python sqlite3 Transaction Processing Method Instance analysis, pythonsqlite3

Python sqlite3 Transaction Processing Method Instance analysis, pythonsqlite3

This article describes how to handle Python sqlite3 transactions. We will share this with you for your reference. The details are as follows:

Sqlite3 transaction summary:

Isolation_level is not passed in connect ().

Transaction processing:

Useconnection.commit()

#! /Usr/bin/env python #-*-coding: UTF-8-*-''' sqlite3 transaction Summary: do not import isolation_level transaction processing in connect (): Use connection. commit () Analysis: intelligent commit status: Generation Method: isolation_level is not passed in connect (). At this time, isolation_level = ''is executing Data Modification Language (DML) when an operation (INSERT/UPDATE/DELETE/REPLACE) is performed, a transaction is automatically opened. When a non-DML or non-query (non-SELECT and above) Statement is executed, you can use connection to implicitly execute commit. commit using the COMMIT () method. Note: you cannot share the automatic commit status with cur.exe cute ("commit "). Method: If isolation_level = None is input in connect (), the connection.exe cute ("begin transaction") connection.exe cute ("COMMIT") will be automatically submitted for TRANSACTION processing in any DML operation ") if you do not use transactions, batch addition of data is very slow data comparison: two methods, the transaction time difference is not big count = 100000 smart commit instant commit time: 0.621 automatic commit time consumption: 0.601 smart commit instant submission time: 0.588 automatic commit time consumed: 0.581 smart commit instant submission time consumed: 0.598 automatic commit time consumed: 0.588 smart commit instant submission time consumed: 0.589 automatic commit time consumed: 0.602 smart commit instant submission time: 0.588 automatic commit time: 0.622 ''' import sy Simport timeclass Elapse_time (object): ''' time-consuming statistical tool ''' def _ init _ (self, prompt = ''): self. prompt = prompt self. start = time. time () def _ del _ (self): print ('% s elapsed time: %. 3f' % (self. prompt, time. time ()-self. start) CElapseTime = Elapse_timeimport sqlite3 # ------------------------------------------------------------------------- # test # filename = 'e:/temp/. db 'def prepare (isolation_level = ''): connec Tion = sqlite3.connect (filename, isolation_level = isolation_level) connection.exe cute ("create table if not exists people (num, age)") connection.exe cute ('delete from others') connection. commit () return connection, connection. cursor () def db_insert_values (cursor, count): num = 1 age = 2 * num while num <= count: cursor.exe cute ("insert into people values (?, ?) ", (Num, age) num + = 1 age = 2 * numdef study_case1_intelligent_commit (count): ''' in the smart commit state, cannot match cur.exe cute (" COMMIT ") share '''connection, cursor = prepare () elapse_time = Elapse_time ('smart commit ') db_insert_values (cursor, count) against cursor.exe cute ("COMMIT ") # exception cursor.exe cute ("select count (*) from people") print (cursor. fetchone () def study_case2_autocommit (count): connection, cursor = prepare (isolation_level = None) elapse_time = Elapse_time ('automatic commit ') db_insert_values (cursor, count) cursor.exe cute ("select count (*) from people") print (cursor. fetchone () def submit (count): connection, cursor = prepare () elapse_time = Elapse_time ('smart commit instant commit ') db_insert_values (cursor, count) connection. commit () cursor.exe cute ("select count (*) from people") print (cursor. fetchone () def Merge (count): connection, cursor = prepare (isolation_level = None) elapse_time = Elapse_time ('automatic commit ') connection.exe cute ("begin transaction ;") # key point db_insert_values (cursor, count) connection.exe cute ("COMMIT;") # key point cursor.exe cute ("select count (*) from people;") print (cursor. fetchone () if _ name _ = '_ main _': count = 10000 prepare () for I in range (5): # study_case1_intelligent_commit (count) # Do not submit data # study_case2_autocommit (count) # Very slow study_case3_intelligent_commit_manual (count) study_case4_autocommit_transaction (count)