sqlite3-python/72db_transaction.py at main · zwieratko/sqlite3-python · GitHub

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
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
#!/usr/bin/env python

import sqlite3
import time

totalRecords = 100000
totalTables = 4
mem = sqlite3.connect(":memory:")
print("OK: in-memory DB opened")

try:
    with mem:
        cur = mem.cursor()
        for tableNr in range(totalTables):
            query = """CREATE TABLE IF NOT EXISTS t{}(
                            id  INTEGER PRIMARY KEY,
                            num INTEGER
                        )""".format(tableNr)
            cur.execute(query)
        print("OK: {} tables created".format(totalTables))
except sqlite3.Error as error:
    print("Problem to create table:", error)
print("---")

# insertmany / no explicit transaction
start = time.time()
try:
    with mem:
        cur = mem.cursor()
        query = "INSERT INTO t0 VALUES(?, ?)"
        newRecords = [(x, x) for x in range(totalRecords)]
        cur.executemany(query, newRecords)
        print("OK: insertmany / no explicit transaction")
        print("Last rowid:", cur.lastrowid)
        print("Modified rows:", cur.rowcount)
except sqlite3.Error as error:
    print("Problem to insert new records:", error)
end = time.time()
totalTime = end - start
print("Time:", round(totalTime, 5))
print("---")

# insertmany / with explicit transaction
start = time.time()
try:
    with mem:
        cur = mem.cursor()
        query = "INSERT INTO t1 VALUES(?, ?)"
        newRecords = [(x, x) for x in range(totalRecords)]
        cur.execute("BEGIN;")
        cur.executemany(query, newRecords)
        print("OK: insertmany / with explicit transaction")
        print("Last rowid:", cur.lastrowid)
        print("Modified rows:", cur.rowcount)
        cur.execute("COMMIT;")
except sqlite3.Error as error:
    print("Problem to insert new records:", error)
end = time.time()
totalTime = end - start
print("Time:", round(totalTime, 5))
print("---")

# insertone / no explicit transaction
start = time.time()
try:
    with mem:
        cur = mem.cursor()
        query = "INSERT INTO t2 VALUES(?, ?)"
        # newRecords = [(x,x) for x in range(totalRecords)]
        for iteration in range(totalRecords):
            newRecord = (iteration, iteration)
            cur.execute(query, newRecord)
        print("OK: insertone / no explicit transaction")
        print("Last rowid:", cur.lastrowid)
        print("Modified rows:", cur.rowcount)
except sqlite3.Error as error:
    print("Problem to insert new records:", error)
end = time.time()
totalTime = end - start
print("Time:", round(totalTime, 5))
print("---")

# insertone / with many explicit transaction
start = time.time()
try:
    with mem:
        cur = mem.cursor()
        query = "INSERT INTO t3 VALUES(?, ?)"
        # newRecords = [(x,x) for x in range(totalRecords)]
        for iteration in range(totalRecords):
            newRecord = (iteration, iteration)
            cur.execute("BEGIN;")
            cur.execute(query, newRecord)
            cur.execute("COMMIT;")
        print("OK: insertone / with many explicit transaction")
        print("Last rowid:", cur.lastrowid)
        print("Modified rows:", cur.rowcount)
except sqlite3.Error as error:
    print("Problem to insert new records:", error)
end = time.time()
totalTime = end - start
print("Time:", round(totalTime, 5))
print("---")

# show last records from all tables

try:
    with mem:
        cur = mem.cursor()
        for tableNr in range(totalTables):
            query = "SELECT * FROM t{} ORDER BY id DESC LIMIT 2".format(
                tableNr)
            result = cur.execute(query).fetchall()
            print(result)
except sqlite3.Error as error:
    print("Problem with simple query:", error)

print("...end...")