j2ihbA8N

1import psycopg2, time, sys
2
3profileList = []
4
5numInserts = 1000
6
7#connect to database
8conn = psycopg2.connect(dbname="test", user="postgres", password="passgres")
9
10#cursors hold a current situation w.r.t. the database
11cur = conn.cursor()
12
13#reset database
14cur.execute("DROP TABLE IF EXISTS prices")
15
16#create a table value. SERIAL is basically autoincrement integer
17cur.execute("CREATE TABLE prices (id SERIAL PRIMARY KEY, price VARCHAR(255))")
18
19#prepare
20cur.execute("PREPARE massInsert AS INSERT INTO prices (price) VALUES ($1)")
21
22def t2():
23
24
25
26    #insert values using parameter to prevent sql injection vulnerabilities
27    for i in range(numInserts):
28        cur.execute("INSERT INTO prices (price) VALUES (%s)", ["test"])
29    
30    conn.commit()
31
32
33profileList.append(["simple insert", t2, 0])
34
35def t3():
36
37
38
39
40
41
42
43
44    #insert values using parameter to prevent sql injection vulnerabilities
45    for i in range(numInserts):
46        cur.execute("EXECUTE massInsert (%s)", ["test"])
47    conn.commit()
48    
49
50
51profileList.append(["prepared simple insert", t3, 0])
52
53def t4():
54
55
56
57
58
59
60
61
62    #insert values using parameter to prevent sql injection vulnerabilities
63    l = [["test"] for i in range(numInserts)]
64
65    cur.executemany("EXECUTE massInsert (%s)",l)
66    conn.commit()
67    
68
69
70profileList.append(["prepared executemany", t4, 0])
71
72def t5():
73
74
75
76
77
78
79
80    #insert values using parameter to prevent sql injection vulnerabilities
81    l = [["test"] for i in range(numInserts)]
82
83    cur.executemany("INSERT INTO prices (price) VALUES(%s)",l)
84    conn.commit()
85    
86
87
88profileList.append(["executemany", t5, 0])
89
90import psycopg2.extras
91
92def t6():
93
94
95
96
97
98
99
100    #insert values using parameter to prevent sql injection vulnerabilities
101    l = [["test"] for i in range(numInserts)]
102
103    psycopg2.extras.execute_values(cur, "INSERT INTO prices (price) VALUES %s",l)
104    conn.commit()
105    
106
107profileList.append(["execute_values", t6, 0])
108
109print("Num inserts: "+str(numInserts))
110
111print("Preparing...")
112for i in range(1,2):
113    for profile in profileList:
114        profile[1]()
115
116print("Running...")
117numRuns = 30
118for i in range(1,numRuns):
119    itr= 0
120    for profile in profileList:#interleaf tests
121        itr+=1
122        sys.stdout.write("Run "+str(i)+"/"+str(numRuns)+" "+str(itr)+"/"+str(len(profileList))+"... ")
123        sys.stdout.flush()
124        s = time.clock()
125        profile[1]()
126        e = time.clock()
127        print(e-s)
128        profile[2] += e-s
129
130print("Overall results:")
131overallResults = sorted(profileList, key= lambda a : a[2], reverse=True)
132for res in overallResults:
133    print(res[0]+" "+str(res[2]/numRuns))
134
135
136#good practice but not necessary
137cur.close()
138conn.close()