python调用数据库存储过程_Python通过调用mysql存储过程实现更新数据功能示例

本文实例讲述了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'

希望本文所述对大家Python程序设计有所帮助。