快捷導(dǎo)航

Python通過調(diào)用mysql存儲過程實現(xiàn)更新數(shù)據(jù)功能示例

更新時間：2018年04月03日 11:40:01 作者：開心果汁

這篇文章主要介紹了Python通過調(diào)用mysql存儲過程實現(xiàn)更新數(shù)據(jù)功能,結(jié)合實例形式分析了Python調(diào)用mysql存儲過程實現(xiàn)更新數(shù)據(jù)的具體步驟與相關(guān)操作技巧,需要的朋友可以參考下

本文實例講述了Python通過調(diào)用mysql存儲過程實現(xiàn)更新數(shù)據(jù)功能。分享給大家供大家參考，具體如下：

一、需求分析

由于管理費率配置錯誤，生成訂單的還本付息表和訂單表的各種金額，管理費之間的計算都有錯誤，需要進行數(shù)據(jù)訂正。為此，為了造個輪子，以后省很多功夫，全部用程序去修正，不接入人工。

二、帶參數(shù)mysql 存儲過程創(chuàng)建

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)建游標
#   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)建游標
#   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'

更多關(guān)于Python相關(guān)內(nèi)容感興趣的讀者可查看本站專題：《Python+MySQL數(shù)據(jù)庫程序設(shè)計入門教程》、《Python常見數(shù)據(jù)庫操作技巧匯總》、《Python數(shù)學運算技巧總結(jié)》、《Python數(shù)據(jù)結(jié)構(gòu)與算法教程》、《Python函數(shù)使用技巧總結(jié)》、《Python字符串操作技巧匯總》、《Python入門與進階經(jīng)典教程》及《Python文件與目錄操作技巧匯總》

希望本文所述對大家Python程序設(shè)計有所幫助。

您可能感興趣的文章: