建站
咨詢
售后
建站咨詢
新聞中心
這里有您想知道的互聯(lián)網(wǎng)營銷解決方案
怎么在Python中通過調(diào)用mysql存儲過程實(shí)現(xiàn)更新數(shù)據(jù)功能-創(chuàng)新互聯(lián)
今天就跟大家聊聊有關(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'總共耗時(shí)：' + str(time2 - time1) + 's'看完上述內(nèi)容，你們對怎么在Python中通過調(diào)用mysql存儲過程實(shí)現(xiàn)更新數(shù)據(jù)功能有進(jìn)一步的了解嗎？如果還想了解更多知識或者相關(guān)內(nèi)容，請關(guān)注創(chuàng)新互聯(lián)成都網(wǎng)站設(shè)計(jì)公司行業(yè)資訊頻道，感謝大家的支持。
另外有需要云服務(wù)器可以了解下創(chuàng)新互聯(lián)scvps.cn，海內(nèi)外云服務(wù)器15元起步，三天無理由+7*72小時(shí)售后在線，公司持有idc許可證，提供“云服務(wù)器、裸金屬服務(wù)器、高防服務(wù)器、香港服務(wù)器、美國服務(wù)器、虛擬主機(jī)、免備案服務(wù)器”等云主機(jī)租用服務(wù)以及企業(yè)上云的綜合解決方案，具有“安全穩(wěn)定、簡單易用、服務(wù)可用性高、性價(jià)比高”等特點(diǎn)與優(yōu)勢，專為企業(yè)上云打造定制，能夠滿足用戶豐富、多元化的應(yīng)用場景需求。
分享標(biāo)題：怎么在Python中通過調(diào)用mysql存儲過程實(shí)現(xiàn)更新數(shù)據(jù)功能-創(chuàng)新互聯(lián)
瀏覽地址：http://fisionsoft.com.cn/article/djched.html
