本文實例講述了Python sqlite3事務處理方法。分享給大家供大家參考，具體如下：
sqlite3事務總結:
在connect()中不傳入 isolation_level
事務處理:
使用connection.commit()
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
|
#!/usr/bin/env python# -*- coding:utf-8 -*-'''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'''import sysimport timeclass Elapse_time(object): '''耗時統計工具''' def __init__(self, prompt=''): self.prompt = prompt self.start = time.time() def __del__(self): print('%s耗時: %.3f' % (self.prompt, time.time() - self.start))CElapseTime = Elapse_timeimport sqlite3# -------------------------------------------------------------------------------# 測試#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 * numdef study_case1_intelligent_commit(count): ''' 在智能commit狀態下, 不能和cur.execute("COMMIT")共用 ''' connection, cursor = prepare() elapse_time = Elapse_time(' 智能commit') db_insert_values(cursor, count) #cursor.execute("COMMIT") #產生異常 cursor.execute("select count(*) from people") print (cursor.fetchone())def study_case2_autocommit(count): connection, cursor = prepare(isolation_level = None) elapse_time = Elapse_time(' 自動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(' 智能commit即時提交') 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(' 自動commit') connection.execute("BEGIN TRANSACTION;") # 關鍵點 db_insert_values(cursor, count) connection.execute("COMMIT;") #關鍵點 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) #不提交數據 #study_case2_autocommit(count) #非常緩慢 study_case3_intelligent_commit_manual(count) study_case4_autocommit_transaction(count) |
希望本文所述對大家Python程序設計有所幫助。
