Example analysis of Python sqlite3 transaction processing method

  • 2020-06-07 04:42:12
  • OfStack

This article gives an example of the Python sqlite3 transaction processing method. To share for your reference, specific as follows:

sqlite3 Transaction Summary:

isolation_level is not passed in connect()

Transaction processing:

use connection.commit()


#!/usr/bin/env python
# -*- coding:utf-8 -*-
'''sqlite3 Transaction concluded :
 in connect() No incoming  isolation_level
 Transaction processing :
   use connection.commit()
 Analysis of the :
   smart commit state :
     generation :  in connect() No incoming  isolation_level,  At this time isolation_level==''
       In the   perform Data Modification Language (DML)  operation (INSERT/UPDATE/DELETE/REPLACE) when ,  It will open automatically 1 A transaction ,
       In the implementation   non DML,  non query ( non  SELECT  And the ones mentioned above ) statements ,  Will execute implicitly commit
       You can use  connection.commit() Method to do the commit 
     Pay attention to :
       Can't and cur.execute("COMMIT") sharing 
   automatic commit state :
     generation :  in connect() The incoming  isolation_level=None
       such , In any DML When operating , Will automatically submit 
     Transaction processing 
      connection.execute("BEGIN TRANSACTION")
      connection.execute("COMMIT")
     If you don't use transactions ,  Adding data in bulk is very slow 
 Data contrast :
   One of two ways ,  Transaction time makes little difference 
  count = 100000
     smart commit Instant commit time : 0.621
     automatic commit Time consuming : 0.601
     smart commit Instant commit time : 0.588
     automatic commit Time consuming : 0.581
     smart commit Instant commit time : 0.598
     automatic commit Time consuming : 0.588
     smart commit Instant commit time : 0.589
     automatic commit Time consuming : 0.602
     smart commit Instant commit time : 0.588
     automatic commit Time consuming : 0.622
'''
import sys
import time
class Elapse_time(object):
  ''' Time consuming statistics tool '''
  def __init__(self, prompt=''):
    self.prompt = prompt
    self.start = time.time()
  def __del__(self):
    print('%s Time consuming : %.3f' % (self.prompt, time.time() - self.start))
CElapseTime = Elapse_time
import sqlite3
# -------------------------------------------------------------------------------
#  test 
#
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):
  '''
   In the smart commit State, ,  Can't and cur.execute("COMMIT") sharing 
  '''
  connection, cursor = prepare()
  elapse_time = Elapse_time('  smart commit')
  db_insert_values(cursor, count)
  #cursor.execute("COMMIT") # Cause abnormal 
  cursor.execute("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.execute("select count(*) from people")
  print (cursor.fetchone())
def study_case3_intelligent_commit_manual(count):
  connection, cursor = prepare()
  elapse_time = Elapse_time('  smart commit Instant submitted ')
  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('  automatic commit')
  connection.execute("BEGIN TRANSACTION;") #  The key point 
  db_insert_values(cursor, count)
  connection.execute("COMMIT;") # The key point 
  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) # Non-submission of data 
    #study_case2_autocommit(count) # Very slow 
    study_case3_intelligent_commit_manual(count)
    study_case4_autocommit_transaction(count)

More about Python related content interested readers to view this site project: "common database operations Python skills summary", "Python coding skills summary", "Python data structure and algorithm tutorial", "Python Socket programming skills summary", "Python function using techniques", "Python string skills summary", "Python introduction and advanced tutorial" and "Python file and directory skills summary"

I hope this article has been helpful for Python programming.


Related articles: