【python 存储过程】python利用mysql存储过程更新数据

一、需求分析
由于管理费率配置错误,生成订单的还本付息表和订单表的各种金额,管理费之间的计算都有错误,需要进行数据订正。为此,为了造个轮子,以后省很多功夫,全部用程序去修正,不接入人工。

二、带参数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'
已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页