-
Notifications
You must be signed in to change notification settings - Fork 1
/
conn.py
179 lines (164 loc) · 5.99 KB
/
conn.py
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
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
#!C:\Python27\python.exe
# -*- coding: utf-8 -*-
# Author: Yantao
import sys
import ibm_db as db2
import datetime
import time
def connDb(dbname, dbuser, dbpasswd, host='182.29.0.1', port=51001, autocomm=1):
conn = db2.connect("HOSTNAME=%s;PROTOCOL=TCPIP;\
PORT=%d;DATABASE=%s;UID=%s;PWD=%s;" \
%(host, port, dbname, dbuser, dbpasswd), '', '')
if autocomm == 0:
db2.autocommit(conn, db2.SQL_AUTOCOMMIT_OFF)
return conn
def pconnDb(dbname, dbuser, dbpasswd, host='182.29.0.1', port=51001):
array = { db2.SQL_ATTR_AUTOCOMMIT : db2.SQL_AUTOCOMMIT_OFF }
conn = db2.pconnect("HOSTNAME=%s;PROTOCOL=TCPIP;\
PORT=%d;DATABASE=%s;UID=%s;PWD=%s;" \
%(host, port, dbname, dbuser, dbpasswd), '', '',array)
return conn
def getResByDict(conn, sql):
''' Call fetch_both: '''
print sql
stmt = db2.exec_immediate(conn, sql)
res = db2.fetch_both(stmt)
while(res):
print 'Result from :', res[1], res
res = db2.fetch_both(stmt)
def getResByTuple(conn, sql, commitnum = 500):
''' Call fetch_tuple: '''
num = 0
tbname = 'TEST'
stmt = db2.exec_immediate(conn, sql)
res = db2.fetch_tuple(stmt)
list1 = []
while res != False:
insql = "insert into %s values(?,?,?,?,?,?,\
?,?,?,?,?,?,?,?,?,?,\
?,?,?,?,?,?,?,?,?,?,?,\
?, ?, ?, ?)" %(tbname)
list1.append(res)
res = db2.fetch_tuple(stmt)
num += 1
if len(list1) == commitnum or res == False:
stmt2 = db2.prepare(conn, insql)
db2.execute_many(stmt2, tuple(list1))
list1 = []
print "insert into records[%d]... " %num
return num
def getBatResByTuple(conn, sql, commitnum=500):
''' Call fetch_tuple: '''
num = 0
tbname = 'TEST'
stmt = db2.exec_immediate(conn, sql)
res = db2.fetch_tuple(stmt)
while res != False:
insql = "insert into %s values('%s','%s','%s','%s','%s','%s',\
'%s','%s','%s','%s','%s','%s','%s','%s','%s','%s',\
'%s','%s','%s','%s','%s',%s,%s,%s,%s,%s,'%s',\
%s, %s, %s, %s)" \
%(tbname, res[0],res[1],res[2],res[3],res[4],res[5],\
res[6],res[7],res[8],res[9],res[10],res[11],\
res[12],res[13],res[14],\
res[15],res[16],res[17],res[18],res[19],res[20],\
res[21] or 'Null',\
res[22] or 'Null',res[23] or 'Null',\
res[24] or 'Null',res[25] or 'Null',res[26] or 'Null', \
res[27] or 'Null',res[28] or 'Null',res[29] or 'Null',\
res[30] or 'Null')
ret = insertRec(conn, insql)
if ret == True:
num += 1
if num % commitnum == 0:
print 'current records:%d, transcation commit.' %num
db2.commit(conn)
res = db2.fetch_tuple(stmt)
db2.commit(conn)
return num
def getBatRes(conn, sql, commitnum=500):
''' Call fetch_tuple: '''
num = 0
tbname = 'TEST'
stmt = db2.exec_immediate(conn, sql)
res = db2.fetch_tuple(stmt)
while res != False:
insql = "insert into %s values('%s','%s','%s','%s','%s','%s',\
'%s','%s','%s','%s','%s','%s','%s','%s','%s','%s',\
'%s','%s','%s','%s','%s',%s,%s,%s,%s,%s,'%s',\
%s, %s, %s, %s)" \
%(tbname, res[0],res[1],res[2],res[3],res[4],res[5],\
res[6],res[7],res[8],res[9],res[10],res[11],\
res[12],res[13],res[14],\
res[15],res[16],res[17],res[18],res[19],res[20],\
res[21] or 'Null',\
res[22] or 'Null',res[23] or 'Null',\
res[24] or 'Null',res[25] or 'Null',res[26] or 'Null', \
res[27] or 'Null',res[28] or 'Null',res[29] or 'Null',\
res[30] or 'Null')
ret = insertRec(conn, insql)
if ret == True:
num += 1
if num % commitnum == 0:
print 'current records:%d, transcation commit.' %num
db2.commit(conn)
res = db2.fetch_tuple(stmt)
db2.commit(conn)
return num
def getResByRow(conn, sql):
''' Call fetch_row '''
n = 0
try:
stmt = db2.exec_immediate(conn, sql)
res = db2.fetch_row(stmt)
while res != False:
print 'Result from :', db2.result(stmt, 0)
n += db2.result(stmt, 0)
res = db2.fetch_row(stmt)
except:
print "Transaction couldn't be completed:" , db2.stmt_errormsg()
else:
return n
def insertRec(conn, sql):
''' Call num_rows '''
try:
stmt = db2.exec_immediate(conn, sql)
#print 'Number of affected rows:', db2.num_rows(stmt)
except:
print sql
print "Transaction couldn't be completed:" , db2.stmt_errormsg()
return False
else:
return True
if __name__ == '__main__':
conn = connDb('TEST','TEST','TEST', autocomm=1)
# insert records
time.sleep(5)
fetchnum = 50000
sql = "select * from TEST \
where RESPONSEFLAG = '1' fetch first %d rows only" \
%fetchnum
starttime = datetime.datetime.now()
n = getResByTuple(conn, sql, 1000)
endtime = datetime.datetime.now()
print 'All cost time, All TPS, Success TPS: %s %f %f' \
%(endtime - starttime, \
fetchnum*(10.0**6)/((endtime - starttime).seconds*(10**6) + \
(endtime - starttime).microseconds),\
n*(10.0**6)/((endtime - starttime).seconds*(10**6) + \
(endtime - starttime).microseconds))
# check records
print 'start check result ...'
f = file('check_res.sql')
checksql = f.read()
time.sleep(10)
#res = getResByRow(conn, checksql)
res = 0
if res == 0:
print 'the result of test is ok'
else:
print 'the result of test is FAILED!'
print res
db2.close(conn)
f.close()
print '----- TEST END -----'