您好,登錄后才能下訂單哦!
今天就跟大家聊聊有關(guān)怎么在Python中通過調(diào)用mysql存儲過程實(shí)現(xiàn)更新數(shù)據(jù)功能,可能很多人都不太了解,為了讓大家更加了解,小編給大家總結(jié)了以下內(nèi)容,希望大家根據(jù)這篇文章可以有所收獲。
1、更新訂單付息表(t_order_rapay)
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;
2、更新訂單表(t_order_info)
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;
3、python 調(diào)用mysql 中的存儲過程
# encoding: utf-8 import time import sys reload(sys) sys.setdefaultencoding('utf-8') time1 = time.time() import pandas as pd import pymysql ############################################從數(shù)據(jù)庫讀數(shù)據(jù)########################################### ###########線上###################### # 加上字符集參數(shù),防止中文亂碼 # conn=pymysql.connect( # host="##########", # database="######", # user="####", # password="##########", # port=#######, # charset='utf8' # ) # #############測試庫###################### # ## 加上字符集參數(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ù)存儲過程 # 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ù)存儲過程 # cursor.execute('call update_t_order_info(%s)', (each)) # conn.commit() # # # cursor.close() # print '調(diào)用存儲過程完畢................' # conn.close() # time2=time.time() # print u'總共耗時:' + str(time2 - time1) + 's'
看完上述內(nèi)容,你們對怎么在Python中通過調(diào)用mysql存儲過程實(shí)現(xiàn)更新數(shù)據(jù)功能有進(jìn)一步的了解嗎?如果還想了解更多知識或者相關(guān)內(nèi)容,請關(guān)注億速云行業(yè)資訊頻道,感謝大家的支持。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報,并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。