def execParity(parity):
try:
if parity == 'ODD':
parity1='ODD'
parity2='EVEN'
else:
parity2='ODD'
parity1='EVEN'
Parity_sqllist=["insert into BILLING.ERR_FORMAT_STRM_BAK_%s (file_name ,file_sn ,thread_id ,error_code ,error_pos ,cdr_type ,nouser1 ,nouser2 ,file_id ,cdr_file_name ,nouser3 ,nouser4 ,nouser5 ,exception_file_id ,exception_cdr_id ,local_net ,nouser6 ,exception_type ,record_type ,self_number ,start_time ,end_time ,duration ,nouser7 ,fee_stream ,nouser8 ,service_id ,up_vol1 ,down_vol1 ,nouser9 ,nouser10 ,imsi ,imei ,self_lac_id ,self_cell_id ,nouser11 ,nouser12 ,nouser13 ,nouser14 ,nouser15 ,nouser16 ,sgsn_addr ,ggsn_addr ,network_init ,charging_id ,pdp_type ,nouser17 ,nouser18 ,sfee ,tfee ,nouser19 ,nouser20 ,ms_nc ,nouser21 ,nouser22 ,nouser23 ,apn_ni ,nouser24 ,apn_oi ,sgsn_change ,plmn_id ,nouser25 ,user_type ,sevice_class ,close_cause ,nouser26 ,nouser27 ,rat_type ,nouser28 ,nouser29 ,nouser30 ,user_id ,nouser31 ,nouser32 ,nas_ip ,ap_ssid ,ocs_info ,connection_id ,csg_info ,pdp_addr ,nouser33 ,nouser34 ,nouser35 ,nouser36 ,nouser37 ,nouser38 ,nouser39 ,nouser40 ,reserve1 ,reserve2 ,reserve3 ,reserve4 ,reserve5 ,redo_mark ,src_record ,record_serial ,redo_flag,ERR_BAKTIME,ERR_BAKMONTH,ERR_BAKDAY) select file_name ,file_sn ,thread_id ,error_code ,error_pos ,cdr_type ,nouser1 ,nouser2 ,file_id ,cdr_file_name ,nouser3 ,nouser4 ,nouser5 ,exception_file_id ,exception_cdr_id ,local_net ,nouser6 ,exception_type ,record_type ,self_number ,start_time ,end_time ,duration ,nouser7 ,fee_stream ,nouser8 ,service_id ,up_vol1 ,down_vol1 ,nouser9 ,nouser10 ,imsi ,imei ,self_lac_id ,self_cell_id ,nouser11 ,nouser12 ,nouser13 ,nouser14 ,nouser15 ,nouser16 ,sgsn_addr ,ggsn_addr ,network_init ,charging_id ,pdp_type ,nouser17 ,nouser18 ,sfee ,tfee ,nouser19 ,nouser20 ,ms_nc ,nouser21 ,nouser22 ,nouser23 ,apn_ni ,nouser24 ,apn_oi ,sgsn_change ,plmn_id ,nouser25 ,user_type ,sevice_class ,close_cause ,nouser26 ,nouser27 ,rat_type ,nouser28 ,nouser29 ,nouser30 ,user_id ,nouser31 ,nouser32 ,nas_ip ,ap_ssid ,ocs_info ,connection_id ,csg_info ,pdp_addr ,nouser33 ,nouser34 ,nouser35 ,nouser36 ,nouser37 ,nouser38 ,nouser39 ,nouser40 ,reserve1 ,reserve2 ,reserve3 ,reserve4 ,reserve5 ,redo_mark ,src_record ,record_serial ,redo_flag,SYSDATE,TO_CHAR(SYSDATE,'mm'),TO_CHAR(SYSDATE,'dd') FROM BILLING.ERR_FORMAT_STRM where redo_flag = 1" % parity1,"insert into BILLING.ERR_FORMAT_MBVC_BAK_%s (file_name ,file_sn ,thread_id ,error_code ,error_pos ,cdr_type ,nouse1 ,nouse2 ,file_id ,org_file_name ,nouse3 ,nouse4 ,nouse5 ,exception_file_id ,exception_cdr_id ,local_net ,nouse6 ,exception_type ,record_type ,org_area_code ,org_number ,nouse7 ,trim_area_code ,trim_number ,nouse8 ,third_number ,nouse9 ,self_number ,nouse10 ,start_time ,end_time ,duration ,imsi ,imei ,nouse11 ,roam_area_code ,roam_type ,self_lac_id ,self_cell_id ,in_trunk ,out_trunk ,tfee ,nouse12 ,nouse13 ,nouse14 ,nouse15 ,nouse16 ,nouse17 ,merge_flag ,nouse18 ,nouse19 ,nouse20 ,free_flag ,user_type ,long_type ,msc ,self_msc ,net_type ,nouse21 ,nouse22 ,turn_type ,nouse24 ,video_flag ,third_flag ,nouse25 ,nouse26 ,nouse27 ,bearer_service ,nouse28 ,nouse29 ,nouse30 ,pay_flag ,org_vpn_code ,org_subvpn_code ,org_scp_code ,trim_vpn_code ,trim_subvpn_code ,trim_scp_code ,closed_group_number ,gourp_number ,subtype ,nouse31 ,dm_flag ,nouse32 ,nouse33 ,nouse34 ,nouse35 ,nouse36 ,nouse37 ,nouse38 ,nouse39 ,nouse40 ,nouse41 ,nouse42 ,opp_lac_id ,opp_cell_id ,nouse43 ,nouse44 ,nouse45 ,nouse46 ,plmn ,reserve ,redo_mark ,src_record ,record_serial ,redo_flag,ERR_BAKTIME,ERR_BAKMONTH,ERR_BAKDAY) select file_name ,file_sn ,thread_id ,error_code ,error_pos ,cdr_type ,nouse1 ,nouse2 ,file_id ,org_file_name ,nouse3 ,nouse4 ,nouse5 ,exception_file_id ,exception_cdr_id ,local_net ,nouse6 ,exception_type ,record_type ,org_area_code ,org_number ,nouse7 ,trim_area_code ,trim_number ,nouse8 ,third_number ,nouse9 ,self_number ,nouse10 ,start_time ,end_time ,duration ,imsi ,imei ,nouse11 ,roam_area_code ,roam_type ,self_lac_id ,self_cell_id ,in_trunk ,out_trunk ,tfee ,nouse12 ,nouse13 ,nouse14 ,nouse15 ,nouse16 ,nouse17 ,merge_flag ,nouse18 ,nouse19 ,nouse20 ,free_flag ,user_type ,long_type ,msc ,self_msc ,net_type ,nouse21 ,nouse22 ,turn_type ,nouse24 ,video_flag ,third_flag ,nouse25 ,nouse26 ,nouse27 ,bearer_service ,nouse28 ,nouse29 ,nouse30 ,pay_flag ,org_vpn_code ,org_subvpn_code ,org_scp_code ,trim_vpn_code ,trim_subvpn_code ,trim_scp_code ,closed_group_number ,gourp_number ,subtype ,nouse31 ,dm_flag ,nouse32 ,nouse33 ,nouse34 ,nouse35 ,nouse36 ,nouse37 ,nouse38 ,nouse39 ,nouse40 ,nouse41 ,nouse42 ,opp_lac_id ,opp_cell_id ,nouse43 ,nouse44 ,nouse45 ,nouse46 ,plmn ,reserve ,redo_mark ,src_record ,record_serial ,redo_flag,SYSDATE,TO_CHAR(SYSDATE,'mm'),TO_CHAR(SYSDATE,'dd') FROM BILLING.ERR_FORMAT_MBVC where redo_flag = 1" % parity1,"insert into BILLING.ERR_FORMAT_SMSG_BAK_%s (file_name ,file_sn ,thread_id ,error_code ,error_pos ,cdr_type ,nouser1 ,nouser2 ,file_id ,cdr_file_name ,nouser3 ,nouser4 ,nouser5 ,exception_file_id ,exception_cdr_id ,local_net ,nouser6 ,exception_type ,record_type ,org_number ,trim_number ,nouser8 ,start_time ,end_time ,nouser9 ,imsi ,imei ,nouser10 ,ifee ,mfee ,gfee ,nouser11 ,nouser12 ,nouser13 ,sms_length ,nouser14 ,sp_code ,nouser15 ,nouser16 ,access_code ,service_code ,nouser17 ,smg_code ,forw_smg_code ,agency_flag ,send_status ,sms_code ,nouser18 ,nouser19 ,nouser20 ,reserve1 ,reserve2 ,reserve3 ,reserve4 ,reserve5 ,redo_mark ,src_record ,record_serial ,redo_flag,ERR_BAKTIME,ERR_BAKMONTH,ERR_BAKDAY) select file_name ,file_sn ,thread_id ,error_code ,error_pos ,cdr_type ,nouser1 ,nouser2 ,file_id ,cdr_file_name ,nouser3 ,nouser4 ,nouser5 ,exception_file_id ,exception_cdr_id ,local_net ,nouser6 ,exception_type ,record_type ,org_number ,trim_number ,nouser8 ,start_time ,end_time ,nouser9 ,imsi ,imei ,nouser10 ,ifee ,mfee ,gfee ,nouser11 ,nouser12 ,nouser13 ,sms_length ,nouser14 ,sp_code ,nouser15 ,nouser16 ,access_code ,service_code ,nouser17 ,smg_code ,forw_smg_code ,agency_flag ,send_status ,sms_code ,nouser18 ,nouser19 ,nouser20 ,reserve1 ,reserve2 ,reserve3 ,reserve4 ,reserve5 ,redo_mark ,src_record ,record_serial ,redo_flag,SYSDATE,TO_CHAR(SYSDATE,'mm'),TO_CHAR(SYSDATE,'dd') FROM BILLING.ERR_FORMAT_SMSG where redo_flag = 1" % parity1,"insert into BILLING.ERR_FORMAT_VACP_BAK_%s (file_name ,file_sn ,thread_id ,error_code ,error_pos ,cdr_type ,nouse1 ,nouse2 ,file_id ,src_file_name ,cdr_id ,nouse3 ,nouse4 ,nouse5 ,nouse6 ,nouse7 ,nouse8 ,nouse9 ,org_number ,trm_number ,nouse10 ,start_time ,end_time ,duration ,up_vol ,down_vol ,ifee ,sequence ,nouse11 ,sp_id ,service_id ,cp_id ,content_id ,product_neti ,mobile_type ,nouse23 ,mobile_no ,vac_serv_type ,product_id ,sub_product ,sp_product ,nouse13 ,nouse14 ,nouse15 ,pay_flag ,fee_point ,user_pay_typ ,nouse24 ,imsi ,nouse25 ,nouse16 ,nouse17 ,nouse18 ,nouse19 ,nouse20 ,nouse21 ,record_type ,reserve1 ,reserve2 ,reserve3 ,reserve4 ,reserve5 ,nouse22 ,reserve6 ,redo_mark ,src_record ,record_serial ,redo_flag ,ERR_BAKTIME,ERR_BAKMONTH,ERR_BAKDAY) select file_name ,file_sn ,thread_id ,error_code ,error_pos ,cdr_type ,nouse1 ,nouse2 ,file_id ,src_file_name ,cdr_id ,nouse3 ,nouse4 ,nouse5 ,nouse6 ,nouse7 ,nouse8 ,nouse9 ,org_number ,trm_number ,nouse10 ,start_time ,end_time ,duration ,up_vol ,down_vol ,ifee ,sequence ,nouse11 ,sp_id ,service_id ,cp_id ,content_id ,product_neti ,mobile_type ,nouse23 ,mobile_no ,vac_serv_type ,product_id ,sub_product ,sp_product ,nouse13 ,nouse14 ,nouse15 ,pay_flag ,fee_point ,user_pay_typ ,nouse24 ,imsi ,nouse25 ,nouse16 ,nouse17 ,nouse18 ,nouse19 ,nouse20 ,nouse21 ,record_type ,reserve1 ,reserve2 ,reserve3 ,reserve4 ,reserve5 ,nouse22 ,reserve6 ,redo_mark ,src_record ,record_serial ,redo_flag ,SYSDATE,TO_CHAR(SYSDATE,'mm'),TO_CHAR(SYSDATE,'dd') FROM BILLING.ERR_FORMAT_VACP where redo_flag = 1" % parity1]
Truncate_sqllist=["alter table BILLING.ERR_FORMAT_STRM_BAK_%s truncate partition ERR_STRM_%s_%s update global indexes" % (parity2,parity2,day),"alter table BILLING.ERR_FORMAT_MBVC_BAK_%s truncate partition ERR_MBVC_%s_%s update global indexes" % (parity2,parity2,day),"alter table BILLING.ERR_FORMAT_SMSG_BAK_%s truncate partition ERR_SMSG_%s_%s update global indexes" % (parity2,parity2,day),"alter table BILLING.ERR_FORMAT_VACP_BAK_%s truncate partition ERR_VACP_%s_%s update global indexes" % (parity2,parity2,day)]
sqllist=zip(Parity_sqllist,Truncate_sqllist)
filename='./config/dbinfo.ini'
dblist=connectDb.getConnectInfo(filename)
dbconn=connectDb.connectDb(dblist[0])
cursor=connectDb.getCursor(dbconn)
for sql in sqllist:
insert_result=connectDb.executeSQL(cursor,sql[0])
if insert_result == True:
truncate_result=connectDb.executeSQL(cursor,sql[1])
connectDb.sqlcommit(dbconn)
except Exception,e:
parityerr='%s %s' % (datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),'execute parity falied')
with open(r'./log/error.log','a') as error_log:
print >> error_log,('%s %s' %(parityerr,e))
def exeProcedure(sql):
#try:
filename='./config/dbinfo.ini'
dblist=connectDb.getConnectInfo(filename)
dbconn=connectDb.connectDb(dblist[0])
cursor=connectDb.getCursor(dbconn)
connectDb.executeSQL(cursor,sql)
Res=connectDb.getSQLrst(cursor)
connectDb.closeCursor(cursor)
connectDb.closeConnect(dbconn)
print Res
return Res
def getSqlData(sql):
try:
filename='./config/billdbinfo.ini'
dblist=connectDb.getConnectInfo(filename)
dbconn=connectDb.connectDb(dblist[0])
cursor=connectDb.getCursor(dbconn)
connectDb.executeSQL(cursor,sql)
result=connectDb.getSQLrst(cursor)
return result
except Exception,e:
getDataerr='%s %s' % (datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),'execute getSqlData falied')
with open(r'./log/error.log','a') as error_log:
print >> error_log,('%s %s' %(getDataerr,e))
def executeCount(sql):
try:
filename='./config/dbinfo.ini'
dblist=connectDb.getConnectInfo(filename)
dbconn=connectDb.connectDb(dblist[0])
cursor=connectDb.getCursor(dbconn)
connectDb.executeSQL(cursor,sql)
result=connectDb.getSQLrst(cursor)
query_result=result[0][0]
#print query_result,type(query_result)
return query_result
except Exception,e:
count_err='%s %s' % (datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),'executeCount falied')
with open(r'./log/error.log','a') as error_log:
print >> error_log,('%s %s' %(count_err,e))
return query_err
def getSqlRes(sql):
try:
filename='./config/dbinfo.ini'
dblist=connectDb.getConnectInfo(filename)
dbconn=connectDb.connectDb(dblist[0])
cursor=connectDb.getCursor(dbconn)
connectDb.executeSQL(cursor,sql)
cols=[]
for col in cursor.description:
cols.append(col[0])
#print cols
result=connectDb.getSQLrst(cursor)
reslist=[]
for res in result:
reslist.append(list(res))
return (cols,reslist)
except Exception,e:
getReserr='%s %s' % (datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),'execute getSqlRes falied')
with open(r'./log/error.log','a') as error_log:
print >> error_log,('%s %s' %(getReserr,e))
def get_dbInfo(sql):
try:
filename='./config/dbinfo.ini'
dblist=connectDb.getConnectInfo(filename)
dbconn=connectDb.connectDb(dblist[0])
cursor=connectDb.getCursor(dbconn)
connectDb.executeSQL(cursor,sql)
result=connectDb.getSQLrst(cursor)
infodict={}
for res in result:
if infodict.has_key(res[0]):
infodict[res[0]].append(res[1:])
else:
infodict[res[0]]=[res[1:]]
#print infodict
return infodict
except Exception,e:
geterr='%s %s' % (datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),'execute get_dbInfo falied')
with open(r'./log/error.log','a') as error_log:
print >> error_log,('%s %s' %(geterr,e))
def executeCommitsql(sqllist):
try:
reslist=[]
filename='./config/dbinfo.ini'
dblist=connectDb.getConnectInfo(filename)
dbconn=connectDb.connectDb(dblist[0])
cursor=connectDb.getCursor(dbconn)
for sql in sqllist:
connectDb.executeSQL(cursor,sql)
rowsaffected = cursor.rowcount
reslist.append(rowsaffected)
connectDb.sqlcommit(dbconn)
update_result=sum(reslist)
#print reslist
#print update_result,type(update_result)
return update_result
except Exception,e:
update_err='%s %s' % (datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),'execute executeCommitsql falied')
with open(r'./log/error.log','a') as error_log:
print >> error_log,('%s %s' %(update_err,e))
return update_err
def dealBusiness(nb):
dblist = connectDb.getConnectInfo("dbConnect")
if type(dblist) is str:
return dblist
print str(nb) + "线程开始：" + datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
dbconn = connectDb.connectDb(dblist[0])
if type(dbconn) is str:
print dbconn
return dbconn
cursor = connectDb.getCursor(dbconn)
if type(cursor) is str:
connectDb.closeConnect(dbconn)
print cursor
return cursor
time.sleep(nb)
print str(nb) + "线程程序处理结束：" + datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
connectDb.sqlcommit(dbconn)
clsCor = connectDb.closeCursor(cursor)
clsCnt = connectDb.closeConnect(dbconn)
print "关闭连接：" + datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
if type(clsCor) is str:
print clsCor
if type(clsCnt) is str:
print clsCnt