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.