怎么在Python中通过调用mysql存储过程实现更新数据功能-创新互联
今天就跟大家聊聊有关怎么在Python中通过调用mysql存储过程实现更新数据功能,可能很多人都不太了解,为了让大家更加了解,小编给大家总结了以下内容,希望大家根据这篇文章可以有所收获。
成都网站制作、做网站、外贸营销网站建设的关注点不是能为您做些什么网站,而是怎么做网站,有没有做好网站,给创新互联公司一个展示的机会来证明自己,这并不会花费您太多时间,或许会给您带来新的灵感和惊喜。面向用户友好,注重用户体验,一切以用户为中心。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 调用mysql 中的存储过程
# encoding: utf-8 import time import sys reload(sys) sys.setdefaultencoding('utf-8') time1 = time.time() import pandas as pd import pymysql ############################################从数据库读数据########################################### ###########线上###################### # 加上字符集参数,防止中文乱码 # conn=pymysql.connect( # host="##########", # database="######", # user="####", # password="##########", # port=#######, # charset='utf8' # ) # #############测试库###################### # ## 加上字符集参数,防止中文乱码 # 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 模块导入mysql数据 # data=pd.read_sql(sqlcmd2,conn) # print data # # # ###################更新order_rapay表 # for each in data["order_no"]: # print each # # 创建游标 # cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # #有参数存储过程 # cursor.execute('call update_t_order_rapay(%s)',(each)) # conn.commit() # # print "%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%" # #####################更新order_info表################### # for each in data["order_no"]: # print each # # # 创建游标 # cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # #有参数存储过程 # cursor.execute('call update_t_order_info(%s)', (each)) # conn.commit() # # # cursor.close() # print '调用存储过程完毕................' # conn.close() # time2=time.time() # print u'总共耗时:' + str(time2 - time1) + 's'
看完上述内容,你们对怎么在Python中通过调用mysql存储过程实现更新数据功能有进一步的了解吗?如果还想了解更多知识或者相关内容,请关注创新互联成都网站设计公司行业资讯频道,感谢大家的支持。
另外有需要云服务器可以了解下创新互联scvps.cn,海内外云服务器15元起步,三天无理由+7*72小时售后在线,公司持有idc许可证,提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案,具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势,专为企业上云打造定制,能够满足用户丰富、多元化的应用场景需求。
当前文章:怎么在Python中通过调用mysql存储过程实现更新数据功能-创新互联
URL网址:http://myzitong.com/article/djched.html