def update_min_region(table_name, area_name):
region_num = find_min_region(table_name, area_name)
update_sql = "UPDATE %s SET PLACEMENT_STATUS ='%d' WHERE REGION_NUM='%s'" % (
table_name, 1, region_num)
ibm_db.exec_immediate(conn, update_sql)
ibm_db.commit(conn)
return region_num
def summation(q, txtBefore, txtAfter):
sum_str = q[2]
# Connect to DB
conn = ibm_db.pconnect(DATABASE, USERNAME, PASSWORD)
if conn is None:
raise Usage(ibm_db.conn_errormsg())
ibm_db.autocommit(ibm_db.SQL_AUTOCOMMIT_OFF)
# Set isolation level
ret = ibm_db.exec_immediate(conn, "SET CURRENT ISOLATION = " + ISOL_LEVEL)
# Prepare statement
sum_stmt = ibm_db.prepare(conn, sum_str)
if sum_stmt == False:
raise Usage("Failed to prepare sum query")
# Execute statement
if ibm_db.execute(sum_stmt) == False:
raise Usage("Failed to execute the sum query")
sum = ibm_db.fetch_tuple(sum_stmt)
ibm_db.commit(conn)
# Print result set to output file
try:
f = open(OUTPUT_FILE_PATH + "/output.txt", "a")
f.write(txtBefore + str(sum) + txtAfter)
f.close()
except IOError, e:
raise Usage("Failed to manipulate output.txt.\n")
def db_search(self, param):
timestamp = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
level = 'LEV' + '%s' % param['level']
user = g.user.uid
if user == 'admin':
source = 'Atas'
sql = "SELECT LEV10,LEV15,LEV17,LEV20,LEV30 from CMRDC.PRODUCTS_IMAGE WHERE %s = '%s'" % (
level, param['prod_id'])
stmt = ibm_db.exec_immediate(self.conn, sql)
results = ibm_db.fetch_both(stmt)
while results:
level10 = results[0]
level15 = results[1]
level17 = results[2]
level20 = results[3]
level30 = results[4]
results = ibm_db.fetch_both(stmt)
try:
insert_sql = "INSERT INTO CMRDC.LINEITEM(RLI_ID, MPP_NUMBER, VERSION, COUNTRY, DELETED, LEVEL10, LEVEL15, LEVEL17, LEVEL20, LEVEL30, SOURCE, DATE_ENTERED, DATE_MODIFIED) VALUES('%s','%s','%s','%s','%d','%s','%s','%s','%s','%s','%s','%s','%s')" % (
param['rli_id'], param['account_mpp'], ESUtil().rule_index,
param['account_country'], param['deleted'], level10, level15,
level17, level20, level30, source, timestamp, timestamp)
ibm_db.exec_immediate(self.conn, insert_sql)
ibm_db.commit(self.conn)
except Exception as ex:
ibm_db.rollback(self.conn)
finally:
ibm_db.close(self.conn)
def summation(q):
sum_str = q[2]
# Connect to DB
start=time.time()
#sys.stderr.write("Start sum %s\n"%(start))
for n in range(int(round(NBSWAPS/NBSWAPTHREADS))):
#sys.stderr.write("Sum %d"%n)
conn = ibm_db.pconnect(DATABASE, USERNAME, PASSWORD)
if conn is None: raise Usage(ibm_db.conn_errormsg())
ibm_db.autocommit(ibm_db.SQL_AUTOCOMMIT_OFF)
# Set isolation level
ret = ibm_db.exec_immediate(conn, "SET CURRENT ISOLATION = "+ISOL_LEVEL)
# Prepare statement
sum_stmt = ibm_db.prepare(conn, sum_str)
if (sum_stmt == False): raise Usage("Failed to prepare sum query")
# Execute statement
if ibm_db.execute(sum_stmt) == False: raise Usage("Failed to execute the sum query")
sum= ibm_db.fetch_tuple(sum_stmt)
ibm_db.commit(conn)
# Print result set to output file
try:
f = open(OUTPUT_FILE_PATH, 'a')
f.write(str(sum)+'\n')
# f.close
except IOError, e:
raise Usage("Failed to manipulate sum.sql.\n")
finally:
def write(q,data):
# initialize vars
write_str = q
# Connect to DB
conn = ibm_db.pconnect(DATABASE, USERNAME, PASSWORD)
if conn is None: raise Usage(ibm_db.conn_errormsg())
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
# Set isolation level
ret = ibm_db.exec_immediate(conn, "SET CURRENT ISOLATION = "+ISOL_LEVEL)
if TL:
ret = ibm_db.exec_immediate(conn, TLSTMT)
# Prepare Statements
write_stmt = ibm_db.prepare(conn, write_str)
if (write_stmt == False): raise Usage("Failed to prepare write statement")
# Perform insertions/updates
for t in data:
# execute insertN statement
if (WRITE_MODE == 'insertN'):
if ibm_db.execute(write_stmt, t) == False:
raise Usage("Failed to execute insertN statement")
elif (WRITE_MODE == 'updateN'):
l = list(t)
u = [l[j] for j in range(len(l)) if j in ATTLIST]
if ibm_db.execute(write_stmt, tuple(u)) == False:
raise Usage("Failed to execute updateN statement")
if (TRANS_MODE == 'N'):
ibm_db.commit(conn)
# commit if TRANS_MODE == 1
ibm_db.commit(conn)
# Disconnect from DB
status = ibm_db.close(conn)
if status == False: raise Usage("Failed to close db connection.\n")
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 update_current(table_name, area_name):
old_result = select_data(table_name, area_name)
new_current = old_result[0] + 1
update_sql = "UPDATE %s SET CURRENT_NO='%d' WHERE STOCK_NAME='%s'" % (
table_name, new_current, area_name)
ibm_db.exec_immediate(conn, update_sql)
ibm_db.commit(conn)
return new_current
def insert_record(self, tablename, record):
conn = self.get_conn()
insertsql = "insert into %s(...) values(?,?,?,?,?,?,\
?,?,?,?,?,?,?,?,?,?,\
?,?,?,?,?,?,?,?,?,?,?,\
?, ?, ?, ?)" % tablename
stmt = ibm_db.prepare(conn, insertsql)
ibm_db.execute(stmt, record)
ibm_db.commit(conn)
def insert_data(self, sql):
try:
print(sql)
ibm_db.exec_immediate(self.conn, sql)
except:
import traceback
traceback.print_exc()
pass
else:
ibm_db.commit(self.conn)
def __exit__(self, exc_type, exc_val, exc_tb):
if self.boundary:
if exc_type is not None:
ibm_db.rollback(self.db_connection)
logging.error(f'{exc_type.__name__}: {exc_val}')
else:
ibm_db.commit(self.db_connection)
print("commit transaction")
ibm_db.close(self.db_connection)
delattr(t_local, '_conn')
elif exc_type is not None:
raise exc_type(exc_val)
def write_log_with_zhengxin(self):
'''
记录登陆人行征信查询页面的次数及日志。用于以后的记录查询
:return:
'''
try:
orgcode = os.getenv('orgcode')
# 获取所要查询机构代码的查询用户
# USER_CODE = ibm_db.exec_immediate(self.conn,"select USER_CODE,CUST_NAME,CLIENT_TYPE from RH.PRIORITY_CLIENT_LIST where ORG_CODE='{0}'".format(orgcode))
# usercode = ibm_db.fetcha_assoc(USER_CODE)
# 获取查询用户的机构和部门
# ENT_NAME 企业名称
# USER_NAME 用户名称
# USER_CODE 用户CODE
# USER_ORG_NAME 用户归属机构名称
# USER_BRANCH_NAME 用户所属分行名称
# 判断改客户是否已经爬取过
query = "select distinct ORG_CODE,MIDSIGNCODE,CUS_NO from {0}.RH_CUST_QUEUE where MIDSIGNCODE='{1}';".format(
base_config.tabschema, orgcode)
org_Info = ibm_db.exec_immediate(self.conn, query)
row = ibm_db.fetch_assoc(org_Info)
if row:
status = '0'
if not row:
status = '1'
# 添加登陆信息到记录表中
sql = "select ENT_NAME,USER_NAME,USER_CODE,USER_ORG_NAME,USER_BRANCH_NAME,QUERY_REASON from RH.RISK_MNIT_QUERY_RH_LIST where MID_SIGN_CODE='{0}'".format(
orgcode)
result = ibm_db.exec_immediate(self.conn, sql)
orgdetail = ibm_db.fetch_assoc(result)
if not isinstance(orgdetail, bool):
current_data = time.strftime("%Y-%m-%d %H:%M:%S",
time.localtime(time.time()))
insertsql = "insert into RH.RH_LOGIN_ZHENGXIN_WEB_LOG" \
" (USER_CODE,CUST_NAME,ORGCODE,USERID,PASSWORD,STATUS,UPLOADTIME,CLIENT_TYPE,ORG_NAME,SEARCH_TYPE,USER_NAME,USER_BRANCH_NAME,CUST_CODE) " \
"values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}')".format(
orgdetail.get('USER_CODE', ''),
orgdetail.get('ENT_NAME', ''), base_config.login_post_data.get('orgCode'),
base_config.login_post_data.get('userid'),
hashlib.md5(base_config.login_post_data.get('password').encode('utf8')).hexdigest(),
os.getenv('credit_status'), current_data, status,
orgdetail.get('USER_ORG_NAME', ''), orgdetail.get('QUERY_REASON', ''), orgdetail.get('USER_NAME', ''),
orgdetail.get('USER_BRANCH_NAME', ''), orgcode)
# print(insertsql)
ibm_db.exec_immediate(self.conn, insertsql)
ibm_db.commit(self.conn)
else:
print("企业信息为空")
except Exception as e:
logger.error("添加登陆记录时出错error", e)
def run_test_021(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
res = ibm_db.fetch_tuple(stmt)
rows = res[0]
print(rows)
ibm_db.autocommit(conn, 0)
ac = ibm_db.autocommit(conn)
if ac != 0:
print("Cannot set ibm_db.AUTOCOMMIT_OFF\nCannot run test")
#continue
ibm_db.exec_immediate(conn, "DELETE FROM animals")
stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
res = ibm_db.fetch_tuple(stmt)
rows = res[0]
print(rows)
ibm_db.commit(conn)
stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
res = ibm_db.fetch_tuple(stmt)
rows = res[0]
print(rows)
# Populate the animal table
animals = (
(0, 'cat', 'Pook', 3.2),
(1, 'dog', 'Peaches', 12.3),
(2, 'horse', 'Smarty', 350.0),
(3, 'gold fish', 'Bubbles', 0.1),
(4, 'budgerigar', 'Gizmo', 0.2),
(5, 'goat', 'Rickety Ride', 9.7),
(6, 'llama', 'Sweater', 150)
)
insert = 'INSERT INTO animals (id, breed, name, weight) VALUES (?, ?, ?, ?)'
stmt = ibm_db.prepare(conn, insert)
if stmt:
for animal in animals:
result = ibm_db.execute(stmt, animal)
ibm_db.commit(conn)
ibm_db.close(conn)
else:
print("Connection failed.")
def run_test_021(self):
conn = ibm_db.connect(config.database, config.user, config.password)
if conn:
stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
res = ibm_db.fetch_tuple(stmt)
rows = res[0]
print rows
ibm_db.autocommit(conn, 0)
ac = ibm_db.autocommit(conn)
if ac != 0:
print "Cannot set ibm_db.AUTOCOMMIT_OFF\nCannot run test"
#continue
ibm_db.exec_immediate(conn, "DELETE FROM animals")
stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
res = ibm_db.fetch_tuple(stmt)
rows = res[0]
print rows
ibm_db.commit(conn)
stmt = ibm_db.exec_immediate(conn, "SELECT count(*) FROM animals")
res = ibm_db.fetch_tuple(stmt)
rows = res[0]
print rows
# Populate the animal table
animals = (
(0, 'cat', 'Pook', 3.2),
(1, 'dog', 'Peaches', 12.3),
(2, 'horse', 'Smarty', 350.0),
(3, 'gold fish', 'Bubbles', 0.1),
(4, 'budgerigar', 'Gizmo', 0.2),
(5, 'goat', 'Rickety Ride', 9.7),
(6, 'llama', 'Sweater', 150)
)
insert = 'INSERT INTO animals (id, breed, name, weight) VALUES (?, ?, ?, ?)'
stmt = ibm_db.prepare(conn, insert)
if stmt:
for animal in animals:
result = ibm_db.execute(stmt, animal)
ibm_db.commit(conn)
ibm_db.close(conn)
else:
print "Connection failed."
def save_records(self, processor_type, data):
if isinstance(data, list):
for subdata in data:
if base_config.database_type == 'mysql':
self.save_dict_into_mysql(processor_type, subdata)
else:
self.save_dict_into_ibmdb(processor_type, subdata)
elif isinstance(data, dict):
if base_config.database_type == 'mysql':
self.save_dict_into_mysql(processor_type, data)
else:
self.save_dict_into_ibmdb(processor_type, data)
if base_config.database_type == 'mysql':
self.conn.commit()
else:
ibm_db.commit(self.conn)
def write(q,index,mutex):
# initialize vars
write_str = q[0]
matchList = re.findall('\?', write_str)
nbParams = len(matchList)
write_data = q[1]
# Connect to DB
conn = ibm_db.pconnect(DATABASE, USERNAME, PASSWORD)
if conn is None: raise Usage(ibm_db.conn_errormsg())
ibm_db.autocommit(conn,ibm_db.SQL_AUTOCOMMIT_OFF)
# Set isolation level
ret = ibm_db.exec_immediate(conn, "SET CURRENT ISOLATION = "+ISOL_LEVEL)
ret2 = None
if TL:
ret2 = ibm_db.exec_immediate(conn, "LOCK TABLE accounts in exclusive mode")
print "Lock: %s"%ret
# Prepare Statements
write_stmt = ibm_db.prepare(conn, write_str)
if (write_stmt == False): raise Usage("Failed to prepare write statement")
# Perform insertions/updates
for i in range(NBWRITES/NBTHREADS):
mutex.acquire()
v = index.value
index.value -= 1
mutex.release()
t = write_data.getWrite(v)
if v >= 0 and t != None:
# execute insertN statement
if (WRITE_MODE == 'insertN'):
if ibm_db.execute(write_stmt, t) == False:
raise Usage("Failed to execute insertN statement")
elif (WRITE_MODE == 'updateN'):
l = list(t)
u = [l[j] for j in range(len(l)) if j in ATTLIST]
if ibm_db.execute(write_stmt, tuple(u)) == False:
raise Usage("Failed to execute updateN statement")
if (TRANS_MODE == 'N'):
print "commit"
ibm_db.commit(conn)
if (TRANS_MODE == '1'):
print "commit"
ibm_db.commit(conn)
# Disconnect from DB
status = ibm_db.close(conn)
if status == False: raise Usage("Failed to close db connection.\n")
def update1(q):
write_str = q[0]
# Connect to DB
conn = ibm_db.pconnect(DATABASE, USERNAME, PASSWORD)
if conn is None: raise Usage(ibm_db.conn_errormsg())
ibm_db.autocommit(conn,ibm_db.SQL_AUTOCOMMIT_OFF)
# Set isolation level
ret = ibm_db.exec_immediate(conn, "SET CURRENT ISOLATION = "+ISOL_LEVEL)
# Prepare statement
write_stmt = ibm_db.prepare(conn, write_str)
if (write_stmt == False): raise Usage("Failed to prepare sum query")
# Execute statement
if ibm_db.execute(write_stmt) == False:
raise Usage("Failed to execute the sum query")
ibm_db.commit(conn)
# Disconnect from DB
status = ibm_db.close(conn)
if status == False: raise Usage("Failed to close db connection.\n")
def update_spider_done(self, orgcode):
'''爬取完成的企业进行入库记录'''
try:
#1.更新待爬清单的状态为爬取完成
update_spider_queue_table_sql = "update {0}.RH_SPIDER_QUEUE_LIST set STATUS='2' where ORGCODE='{1}' and SEARCHDATE='{2}';".format(
base_config.tabschema.upper(), orgcode,
os.getenv('last_quarter_end'))
ibm_db.exec_immediate(self.conn, update_spider_queue_table_sql)
ibm_db.commit(self.conn)
#2.更新爬取完成清单的内容，如果没有出错，就插入爬取完成表。出错就不插入
error_spider_record_sql = "select ORGCODE from {0}.RH_ERROR_SPIDER where SEARCHDATE='{1}' and ORGCODE='{2}' and date(UPLOADTIME)='{3}';".format(
base_config.tabschema.upper(),
os.getenv('last_quarter_end'), orgcode,
time.strftime('%Y-%m-%d', time.localtime(time.time())))
error_spider_record = ibm_db.exec_immediate(
self.conn, error_spider_record_sql)
row = ibm_db.fetch_assoc(error_spider_record)
# if row:
# pass
# if not row:
# 成功与否，都添加记录到RH_CUST_QUEUE中
#select CUSTNAME,CUSTID from DB2IIASS.DESK_SXGL0431_D where ORGCERTCODE='93125730-6';
sql = "select distinct CUSTNAME,CUSTID from {0}.DESK_SXGL0431_D where LNCARDNO='{1}';".format(
base_config.hongduntabschema.upper(), orgcode)
result_query = ibm_db.exec_immediate(self.conn, sql)
row = ibm_db.fetch_assoc(result_query)
while (row):
# print(row)
insert_sql = "insert into {0}.RH_CUST_QUEUE(ENT_NAME,CUS_NO,ORG_CODE,MIDSIGNCODE,SEARCHDATE,FINISH_DATE) values ('{1}','{2}','{3}','{4}','{5}','{6}')".format(
base_config.tabschema, row.get('CUSTNAME'),
row.get('CUSTID'), orgcode, os.getenv('midsigncode'),
os.getenv('last_quarter_end'),
time.strftime("%Y-%m-%d %H:%M:%S",
time.localtime(time.time())))
ibm_db.exec_immediate(self.conn, insert_sql)
ibm_db.commit(self.conn)
row = ibm_db.fetch_assoc(result_query)
#os.environ['credit_status'] = '0'
#HtmlOutputer().write_log_with_zhengxin()
except Exception as e:
logger.error("爬取完成的企业进行入库记录error", e)
pass
def parseCommit(sql):
global _hdbc, _hdbi, _connected, _stmt, _stmtID, _stmtSQL
if (_connected == False): return # Nothing to do if we are not connected
cParms = sql.split()
if (len(cParms) == 0): return # Nothing to do but this shouldn't happen
keyword = cParms[0].upper() # Upper case the keyword
if (keyword == "COMMIT"): # Commit the work that was done
try:
result = ibm_db.commit(_hdbc) # Commit the connection
if (len(cParms) > 1):
keyword = cParms[1].upper()
if (keyword == "HOLD"):
return
del _stmt[:]
del _stmtID[:]
except Exception as err:
db2_error()
return
if (keyword == "ROLLBACK"): # Rollback the work that was done
try:
result = ibm_db.rollback(_hdbc) # Rollback the connection
del _stmt[:]
del _stmtID[:]
except Exception as err:
db2_error()
return
if (keyword == "AUTOCOMMIT"): # Is autocommit on or off
if (len(cParms) > 1):
op = cParms[1].upper() # Need ON or OFF value
else:
return
try:
if (op == "OFF"):
ibm_db.autocommit(_hdbc, False)
elif (op == "ON"):
ibm_db.autocommit(_hdbc, True)
return
except Exception as err:
db2_error()
return
return
def swap(q):
swap1_str = q[0]
swap2_str = q[1]
# Connect to DB
conn = ibm_db.pconnect(DATABASE, USERNAME, PASSWORD)
if conn is None:
raise Usage(ibm_db.conn_errormsg())
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
# Set isolation level
ret = ibm_db.exec_immediate(conn, "SET CURRENT ISOLATION = " + ISOL_LEVEL)
# Prepare Statements
swap1_stmt = ibm_db.prepare(conn, swap1_str)
if swap1_stmt == False:
raise Usage("Failed to prepare swap1 query")
swap2_stmt = ibm_db.prepare(conn, swap2_str)
if swap2_stmt == False:
raise Usage("Failed to prepare swap2 update")
# Execute Statements
nbrep = int(round(NBSWAPS / NBSWAPTHREADS))
for i in range(nbrep):
x = random.randint(RANGE_LOW, RANGE_UP / 2)
y = random.randint(x, RANGE_UP)
if ibm_db.execute(swap1_stmt, (x,)) == False:
raise Usage("Failed to execute the swap1 query (x)")
valX = ibm_db.fetch_tuple(swap1_stmt)
if valX == False:
raise Usage("Failed to iterate over the swap1 result set (x)")
if ibm_db.execute(swap1_stmt, (y,)) == False:
raise Usage("Failed to execute the swap1 query (y)")
valY = ibm_db.fetch_tuple(swap1_stmt)
if valY == False:
raise Usage("Failed to iterate over the swap1 result set (y)")
time.sleep(0.1)
if ibm_db.execute(swap2_stmt, (valY[0], x)) == False:
raise Usage("Failed to execute the swap2 query (x, valY)")
if ibm_db.execute(swap2_stmt, (valX[0], y)) == False:
raise Usage("Failed to execute the swap1 query (y, valX)")
ibm_db.commit(conn)
# Disconnect from DB
status = ibm_db.close(conn)
if status == False:
raise Usage("Failed to close db connection.\n")
def insert_many_records(self, recordslist, commitnum, tablename=None):
conn = self.get_conn()
for i in xrange(0, len(recordslist), commitnum):
time.sleep(0.05)
if tablename is None:
import random
tbname = 'xxx%d' % (random.randint(1, 5))
else:
tbname = tablename
insertsql = "insert into %s(...) values(?, ?, ?, ?, ?, ?, \
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, \
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, \
?, ?, ?, ?)" % tbname
try:
stmt = ibm_db.prepare(conn, insertsql)
ibm_db.execute_many(stmt, tuple(recordslist[i:i + commitnum]))
except:
print ibm_db.stmt_error(), ibm_db.stmt_errormsg()
finally:
ibm_db.commit(conn)
def delete_all_table_data(self):
query_tables_sql = "select trim(tabschema) as tabschema, tabname FROM syscat.tables WHERE tabschema = '{}'".format(
tabschema.upper() or user.upper())
result_query = ibm_db.exec_immediate(self.conn, query_tables_sql)
row = ibm_db.fetch_assoc(result_query)
while (row):
if row.get('TABNAME').startswith("RH_") and row.get('TABNAME') not in ['RH_CUST_QUEUE','RH_ERROR_SPIDER','RH_SPIDER_QUEUE_LIST']:
# delete_sql = "truncate table {TABSCHEMA}.{TABNAME} immediate;".format(**row)
# print(delete_sql)
sql_delete = "delete from {TABSCHEMA}.{TABNAME} where ='1101000002264820'".format(**row)
print(sql_delete)
# delete_sql2 = "delete from {TABSCHEMA}.{TABNAME} where MIDSIGNCODE='3309060000195576' and date(uploadtime)='2016-12-15';".format(**row)
# print(delete_sql2)
try:
# ibm_db.exec_immediate(self.conn, sql_delete)
ibm_db.exec_immediate(self.conn, sql_delete)
# ibm_db.exec_immediate(self.conn, delete_sql2)
except Exception as e:
print('error state code:{}'.format(ibm_db.stmt_error()),"error state=",e)
# time.sleep(0.1)
row = ibm_db.fetch_assoc(result_query)
ibm_db.commit(self.conn)
def update_area_ratio(table_name, area_name, new_ratio):
update_ratio = "UPDATE %s SET CURRENT_RATIO = '%f' WHERE STOCK_NAME = '%s' " % (
table_name, new_ratio, area_name)
ibm_db.exec_immediate(conn, update_ratio)
ibm_db.commit(conn)
return area_name
def commit(self):
self.isautapp()
global db
return ibm_db.commit(db)
ibm_db.execute(stmt, (max_len, ))
tpl = ibm_db.fetch_tuple(stmt)
a.append(tpl)
while tpl:
tpl = ibm_db.fetch_tuple(stmt)
a.append(tpl)
print()
h = ['#', 'Index names', 'Cost', 'Improvement']
print(tabulate(a[:-1], headers=h))
print()
question = input("Output Index Combination? (N, 2,3,...)\n")
if question == "N" or question == "1":
print("Bye")
sys.exit(1)
else:
choice = a[int(question) - 1][1]
indexes = choice.split(",")
stmt = ibm_db.prepare(conn, DISPLAY_INDEXES)
print()
for i in indexes:
ibm_db.execute(stmt, (i, ))
tpl = ibm_db.fetch_tuple(stmt)[0]
result = tpl.replace('"', '').replace(' ASC', '')
print(result)
print()
ibm_db.commit(conn)
# Otherwise, Complete The Status Message
else:
print("Done!\n")
# Display The Number Of Rows That Exist In The DEPARTMENT Table Now
# (The Number Returned Should Change)
returnCode = get_row_count(dbConnection, 'DEPARTMENT')
if returnCode is False:
conn.closeConnection()
exit(-1)
# Commit The Changes Just Made (To Make Them Permanent)
print("Commiting changes made to the database ... ", end="")
resultSet = False
try:
resultSet = ibm_db.commit(dbConnection)
except Exception:
pass
# If The Commit Operation Could Not Be Performed, Display An Error Message And Exit
if resultSet is False:
print("\nERROR: Unable to commit the previous operation.")
conn.closeConnection()
exit(-1)
# Otherwise, Complete The Status Message
else:
print("Done!\n")
# Display The Number Of Rows That Exist In The DEPARTMENT Table Now
# (The Number Should Not Change)
def doEverything():
commands = []
# connect to database
host = ""
port = "9090"
username = ""
password = "******"
database = "s0523215"
url = "HOSTNAME=" + host + ";PORT=" + port + ";DATABASE="+ database + ";PROTOCOL=TCPIP;UID=" + username +";PWD="+ password + ";"
conn = ibm_db.connect(url, '', '')
commands.append("Connected to " + url)
# set up variables and data
tableName = "pythonDRDAGalaxy"
# 1 Create table
commands.append("\n#1 Create table")
sql = "create table if not exists " + tableName + "(name VARCHAR(255), population INTEGER, longitude DECIMAL(8,4), latitude DECIMAL(8,4),countryCode INTEGER)"
ibm_db.exec_immediate(conn, sql)
commands.append( "\tCreate a table named: " + tableName)
commands.append("\tCreate Table SQL: " + sql)
# 2 Inserts
commands.append("\n#2 Inserts")
# 2.1 Insert a single document into a table
commands.append("#2.1 Insert a single document into a table")
sql = "insert into " + tableName + " values(?,?,?,?,?)"
statement = ibm_db.prepare(conn, sql)
ibm_db.bind_param(statement, 1, kansasCity.name)
ibm_db.bind_param(statement, 2, kansasCity.population)
ibm_db.bind_param(statement, 3, kansasCity.longitude)
ibm_db.bind_param(statement, 4, kansasCity.latitude)
ibm_db.bind_param(statement, 5, kansasCity.countryCode)
ibm_db.execute(statement)
commands.append("\tCreate Document -> " + kansasCity.name + " , " + str(kansasCity.population) +
" , " + str(kansasCity.longitude) + " , " + str(kansasCity.latitude) + " , " + str(kansasCity.countryCode))
commands.append("\tSingle Insert SQL: " + sql)
# 2.2 Insert multiple documents into a table
# Currently there is no support for batch inserts with ibm_db
commands.append("#2.2: Insert multiple documents into a table. \n\tCurrently there is no support batch inserts")
sql = "insert into " + tableName + " values(?,?,?,?,?)"
statement = ibm_db.prepare(conn, sql)
ibm_db.bind_param(statement, 1, seattle.name)
ibm_db.bind_param(statement, 2, seattle.population)
ibm_db.bind_param(statement, 3, seattle.longitude)
ibm_db.bind_param(statement, 4, seattle.latitude)
ibm_db.bind_param(statement, 5, seattle.countryCode)
ibm_db.execute(statement)
sql = "insert into " + tableName + " values(?,?,?,?,?)"
statement = ibm_db.prepare(conn, sql)
ibm_db.bind_param(statement, 1, newYork.name)
ibm_db.bind_param(statement, 2, newYork.population)
ibm_db.bind_param(statement, 3, newYork.longitude)
ibm_db.bind_param(statement, 4, newYork.latitude)
ibm_db.bind_param(statement, 5, newYork.countryCode)
ibm_db.execute(statement)
#Alternate way to insert without bindings
sql = "insert into " + tableName + " values" + tokyo.toSQL()
ibm_db.exec_immediate(conn, sql)
sql = "insert into " + tableName + " values" + madrid.toSQL()
ibm_db.exec_immediate(conn, sql)
sql = "insert into " + tableName + " values" + melbourne.toSQL()
ibm_db.exec_immediate(conn, sql)
# 3 Queries
commands.append("\n#3 Queries")
# 3.1 Find one document in a table that matches a query condition
commands.append("#3.1 Find one document in a table that matches a query condition")
sql = "select * from " + tableName + " where population > 8000000 and countryCode = 1"
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
commands.append("\tFirst document matching query -> name: " + str(dictionary[0]) + ", population: " + str(dictionary[1]) + ", longitude: " +
str(dictionary[2]) + ", latitude: " + str(dictionary[3]) + ", countryCode: " + str(dictionary[4]))
commands.append("\tQuery By name SQL: " + sql)
# 3.2 Find documents in a table that match a query condition
commands.append("#3.2 Find documents in a table that match a query condition")
sql = "select * from " + tableName + " where population > 8000000 and longitude > 40.0"
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
commands.append("\tFind all documents with name: " + kansasCity.name)
while dictionary != False:
commands.append("\tFound document name -> name: " + str(dictionary[0]) + ", population: " + str(dictionary[1]) + ", longitude: " +
str(dictionary[2]) + ", latitude: " + str(dictionary[3]) + ", countryCode: " + str(dictionary[4]))
dictionary = ibm_db.fetch_both(stmt)
commands.append( "\tQuery All By name SQL: " + sql)
# 3.3 Find all documents in a table
commands.append("#3.3 Find all documents in a table")
sql = "select * from " + tableName
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
commands.append( "\tFind all documents in table: " + tableName)
while dictionary != False:
commands.append("\tFound document name -> name: " + str(dictionary[0]) + ", population: " + str(dictionary[1]) + ", longitude: " +
str(dictionary[2]) + ", latitude: " + str(dictionary[3]) + ", countryCode: " + str(dictionary[4]))
dictionary = ibm_db.fetch_both(stmt)
commands.append("\tFind All Documents SQL: " + sql)
commands.append("#3.4 Count documents in a table")
sql = "select count(*) from " + tableName + " where longitude < 40.0"
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
commands.append("Documents in table with longitude less than 40.0: " + str(len(dictionary)))
commands.append("#3.5 Order documents in a table")
sql = "select * from " + tableName + " order by population"
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
while dictionary != False:
commands.append("\tFound Document -> name: " + str(dictionary[0]) + " population: " + str(dictionary[1]) + " latitude: " +
str(dictionary[2]) + " longitude: " + str(dictionary[3]) + " countryCode: " + str(dictionary[4]))
dictionary = ibm_db.fetch_both(stmt)
commands.append("#3.6 Joins")
tableJoin = "country";
sql = "create table if not exists " + tableJoin + " (countryCode INTEGER, countryName VARCHAR(255))";
stmt = ibm_db.exec_immediate(conn, sql)
sql = "insert into " + tableJoin + " values (1,\"United States of America\")";
stmt = ibm_db.exec_immediate(conn, sql)
sql = "insert into " + tableJoin + " values (44,\"United Kingdom\")";
stmt = ibm_db.exec_immediate(conn, sql)
sql = "insert into " + tableJoin + " values (81,\"Japan\")";
stmt = ibm_db.exec_immediate(conn, sql)
sql = "insert into " + tableJoin + " values (34,\"Spain\")";
stmt = ibm_db.exec_immediate(conn, sql)
sql = "insert into " + tableJoin + " values (61,\"Australia\")";
stmt = ibm_db.exec_immediate(conn, sql)
sql = ("select table1.name, table1.population, table1.longitude, table1.latitude, table1.countryCode, table2.countryName from " +
tableName + " table1 inner join " + tableJoin + " table2 on table1.countryCode=table2.countryCode")
stmt =ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
while dictionary != False:
commands.append("\tJoined -> name: " + str(dictionary[0]) + ", population: " + str(dictionary[1]) + ", latitude: " +
str(dictionary[2]) + ", longitude: " + str(dictionary[3]) + ", countryCode: " + str(dictionary[4]) + ", countryName: " + str(dictionary[5]))
dictionary = ibm_db.fetch_both(stmt)
commands.append("#3.7 Distinct documents in a table")
sql = "select distinct countryCode from " + tableName + " where longitude > 40.0"
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
commands.append("Distinct countryCodes of documents in table with longitude greater than 40.0: ")
while dictionary != False:
commands.append("\tJoined -> countryCode: " + str(dictionary[0]))
dictionary = ibm_db.fetch_both(stmt)
commands.append("#3.8 Projection Clause")
sql = "select distinct name, countryCode from " + tableName + " where population > 8000000"
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
commands.append("Projection of name and countryCode where population is greater than 8000000: ")
while dictionary != False:
commands.append("\tJoined -> name: " + str(dictionary[0]) + ", countryCode: " + str(dictionary[1]))
dictionary = ibm_db.fetch_both(stmt)
# 4 Update documents in a table
commands.append("\n#4 Update documents in a table")
sql = "update " + tableName + " set countryCode = ? where name = ?"
statement = ibm_db.prepare(conn, sql)
ibm_db.bind_param(statement, 1, 999)
ibm_db.bind_param(statement, 2, seattle.name)
ibm_db.execute(statement)
commands.append( "\tDocument to update: " + seattle.name)
commands.append("\tUpdate By name SQL: " + sql)
# 5 Delete documents in a table
commands.append("\n#5 Delete documents in a table")
sql = "delete from " + tableName + " where name like '" + newYork.name + "'"
ibm_db.exec_immediate(conn, sql)
commands.append("\tDelete documents with name: " + newYork.name)
commands.append("\tDelete By name SQL: " + sql)
commands.append("\n#6 Transactions")
ibm_db.autocommit(conn, False)
sql = "insert into " + tableName + " values" + sydney.toSQL()
ibm_db.exec_immediate(conn, sql)
ibm_db.commit(conn)
sql = "update " + tableName + " set countryCode = 998 where name = 'Seattle'"
ibm_db.exec_immediate(conn, sql)
ibm_db.rollback(conn)
ibm_db.autocommit(conn, True)
commands.append( "\tFind all documents in table: " + tableName)
sql = "select * from " + tableName
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
while dictionary != False:
commands.append("\tFound document name -> name: " + str(dictionary[0]) + ", population: " + str(dictionary[1]) + ", longitude: " +
str(dictionary[2]) + ", latitude: " + str(dictionary[3]) + ", countryCode: " + str(dictionary[4]))
dictionary = ibm_db.fetch_both(stmt)
commands.append("\n#7 Commands")
commands.append("\n#7.1 Count")
sql = "select count(*) from " + tableName
stmt = ibm_db.exec_immediate(conn, sql)
dictionary = ibm_db.fetch_both(stmt)
commands.append("Number of documents in table " + str(len(dictionary)))
# 8 Drop a table
commands.append("\n#8 Drop a table")
sql = "drop table " + tableName;
ibm_db.exec_immediate(conn, sql)
sql = "drop table " + tableJoin;
ibm_db.exec_immediate(conn, sql)
commands.append("\tDrop table: " + tableName)
commands.append("\tDrop Table SQL: " + sql)
ibm_db.close(conn)
commands.append("\nConnection closed")
return commands
def commitTrans(connection, outFile):
if ibm_db.commit(connection):
outFile.write("Commit successful \n")
else:
outFile.write("Commit FAILED \n")
def main(self):
args = self.get_option()
dbname = args.dbname # args1
input_file = args.input_file # args2
rows = args.rows # args3
username = connect_conf.username
password = connect_conf.password
schema = username.upper()
l_list = []
g_list = []
col_types = []
col_sizes = []
col_size_fractions = []
col_num = 0
count = 0
COMMIT_ROW = 1000
# connect
try:
conn = ibm_db.pconnect(dbname, username, password)
except Exception as e:
print("Connection failed")
traceback.print_exc()
return
ibm_db.autocommit(conn, ibm_db.SQL_AUTOCOMMIT_OFF)
# get the sql
try:
insert_sql = self.get_sql(input_file)
except Exception as e:
print("Cannot read sqlfile.")
traceback.print_exc()
return
stmt = ibm_db.prepare(conn, insert_sql)
if not stmt:
print("Failed in prepare.")
return
# get the table name from sql, and check
tabname = re.split("[ ]*INSERT INTO[ ]+", insert_sql)[1]
tabname = re.split("[ ]+VALUES", tabname)[0]
if tabname is None:
print("Error in contents of sqlfile.")
return
# verify whether the table exist
rc = self.chk_table(conn, schema, tabname)
if not rc:
print("table name " + tabname + " is not found in " + dbname)
return
# get the number of the columns
result = ibm_db.columns(conn, None, None, tabname)
col = ibm_db.fetch_tuple(result)
while (col):
col_num = col_num + 1
col = ibm_db.fetch_tuple(result)
# list of column type and column size
col_info = ibm_db.columns(conn, None, None, tabname)
col = ibm_db.fetch_tuple(col_info)
while (col):
col_types.append(col[5])
col_sizes.append(col[6])
col_size_fractions.append(col[8])
col = ibm_db.fetch_tuple(col_info)
for i in range(rows):
count = 0
random.seed()
for j in range(len(col_types)):
count = count + 1
if self.chk_par_mark(count, insert_sql):
if col_types[j] == "CHAR":
param = self.get_random_char(col_sizes[j])
elif col_types[j] == "VARCHAR":
param = self.get_random_char(col_sizes[j])
elif col_types[j] == "SMALLINT":
param = self.get_random_smallint()
elif col_types[j] == "INTEGER":
param = self.get_random_integer()
elif col_types[j] == "BIGINT":
param = self.get_random_long()
elif col_types[j] == "REAL":
param = self.get_random_real()
elif col_types[j] == "FLOAT":
param = self.get_random_double()
elif col_types[j] == "DOUBLE":
param = self.get_random_double()
elif col_types[j] == "DECIMAL":
digit = col_sizes[j] - col_size_fractions[j]
param = self.get_random_decimal(digit)
elif col_types[j] == "NUMERIC":
digit = col_sizes[j] - col_size_fractions[j]
param = self.get_random_decimal(digit)
elif col_types[j] == "DATE":
param = self.get_random_date()
elif col_types[j] == "TIME":
param = self.get_random_time()
elif col_types[j] == "TIMESTAMP":
param = self.get_random_timestamp()
elif col_types[j] == "BLOB":
param = self.get_random_byte(col_sizes[j])
elif col_types[j] == "CLOB":
param = self.get_random_char(col_sizes[j])
else:
param = ''
# set the parameter to the list
self.set_param_list(param, l_list)
# end of the columns
if count == col_num:
self.concat_list(g_list, l_list)
l_list = []
if ((i + 1) % COMMIT_ROW == 0):
#print g_list
rc = ibm_db.execute_many(
stmt, tuple(tuple(x) for x in g_list))
rc = ibm_db.commit(conn)
g_list = []
if len(g_list) != 0:
print g_list
rc = ibm_db.execute_many(stmt, tuple(tuple(x) for x in g_list))
rc = ibm_db.commit(conn)
ibm_db.close(conn)
