drop procedure if exists update_t_order_rapay;delimiter $$create procedure update_t_order_rapay(IN orderNo varchar(64))begin  declare t_order_no varchar(64);  set t_order_no=orderNo;  UPDATE t_order_repay  SET total_amount=principal+interest+round(manage_amount*0.0808/0.052,3)+breach_amount,    left_amount=principal+interest+round(manage_amount*0.0808/0.052,3)+breach_amount,  left_repay_manager=round(manage_amount*0.0808/0.052,3),  manage_amount=round(manage_amount*0.0808/0.052,3)  WHERE order_no=t_order_no;end $$delimiter;

drop procedure if exists update_t_order_info;delimiter $$create procedure update_t_order_info(IN orderNo varchar(64))begin  declare t_order_no varchar(64);  set t_order_no=orderNo;  SELECT left_amount into @m1 from t_order_repay WHERE order_no=t_order_no ORDER BY plan_time LIMIT 1;  UPDATE t_order_info  set manage_cost_rate=0.0808,  manage_cost=round(manage_cost*0.0808/0.052,3),  left_amount=borrow_amount+interest_amount+manage_cost,  next_amount_need=@m1  WHERE order_no=t_order_no;end $$delimiter;

# encoding: utf-8import timeimport sysreload(sys)sys.setdefaultencoding('utf-8')time1 = time.time()import pandas as pdimport pymysql############################################從數(shù)據(jù)庫讀數(shù)據(jù)######################################################線上####################### 加上字符集參數(shù)，防止中文亂碼# conn=pymysql.connect(#  host="##########",#  database="######",#  user="####",#  password="##########",#  port=#######,#  charset='utf8'# )# #############測(cè)試庫####################### ## 加上字符集參數(shù)，防止中文亂碼# conn=pymysql.connect(#  host="172.16.34.32",#  database="#########",#  user="admin",#  password="##############",#  port=#########,#  charset='utf8'# )#sql語句(安徽）# sqlcmd="""# SELECT order_no from t_order_info WHERE offline_org_no in(# 0032,0035,0036### ) and substr(create_time,1,10)>="2017-10-31"## and `status` in(105,106,107,108)## and manage_cost_rate=0.052### """#################sql語句（江蘇）# sqlcmd2="""# SELECT order_no from t_order_info WHERE offline_org_no in(# 0002,0005,0006,0007,0008,0009,0010,0011,0012,0013,0014,0017,0018,0019,0025,0026,0027,0028,0030,0031,0033,0034# ) and substr(create_time,1,10)>="2017-10-31"# and `status` in(105,106,107,108)# and manage_cost_rate=0.052## """#利用pandas 模塊導(dǎo)入mysql數(shù)據(jù)# data=pd.read_sql(sqlcmd2,conn)# print data### ###################更新order_rapay表# for each in data["order_no"]:#   print each#   # 創(chuàng)建游標(biāo)#   cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)#   #有參數(shù)存儲(chǔ)過程#   cursor.execute('call update_t_order_rapay(%s)',(each))#   conn.commit()## print "%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%"# #####################更新order_info表#################### for each in data["order_no"]:#   print each##   # 創(chuàng)建游標(biāo)#   cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)#   #有參數(shù)存儲(chǔ)過程#   cursor.execute('call update_t_order_info(%s)', (each))#   conn.commit()### cursor.close()# print '調(diào)用存儲(chǔ)過程完畢................'# conn.close()# time2=time.time()# print u'總共耗時(shí)：' + str(time2 - time1) + 's'