How Python calls MySQL to update data

This time to bring you a Python call MySQL update data method, Python call MySQL update data of the attention to what, the following is the actual case, take a look.

This example describes Python's ability to update data by invoking a MySQL stored procedure. Share to everyone for your reference, as follows:

First, demand analysis

Due to the mis-configuration of the management rate, the various amounts of the repayment form and the order form of the order are generated, and the calculation between the management fee is wrong and the data need to be revised. Therefore, in order to build a wheel, after a lot of Kung fu, all with the program to correct, not access to labor.

Ii. MySQL stored procedure creation with parameters

1. Update Order Coupon form (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 (+);  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. Update order form (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 (+);  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 calls the stored procedure in MySQL

# encoding:utf-8import Timeimport sysreload (SYS) sys.setdefaultencoding (' utf-8 ') time1 = time.time () Import pandas as Pdimport pymysql########################################### #从数据库读数据 ############################################  ######### #线上 ####################### plus character set parameters to prevent Chinese garbled # conn=pymysql.connect (# host= "##########", # database= "######", # User= "# # # # # # # password=" ########## ", # port=#######,# charset= ' UTF8 ' #) # ############ #测试库 ####################### # # Add character set parameter, prevent Chinese garbled # conn=pymysql.connect (# host= "172.16.34.32", # database= "#########", # user= "admin", # password= "###### ######## ", # port=#########,# charset= ' UTF8 ' #) #sql语句 (Anhui) # sqlcmd=" "" # Select Order_no from T_order_info WHERE offline_or G_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语句 (Jiangsu) # 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 module import MySQL data # data=pd.read_sql (sqlcmd2,conn) # Print data### ################## #更新order_rapay表 # for each in data["Order_no"]:# print each# # create cursor # cursor = CONN.CU   Rsor (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## # create cursor # cursor = CONN.C Ursor (cursor=pymysql.cursors.dictcursor) # #有参数存储过程 # cursor.execute (' Call Update_t_order_info (%s) ', (each)) # conn.co Mmit () # # # # Cursor.close () # print ' Call the stored procedure complete ... ' # conn.close () # time2=time.time () # print U ' total time: ' + str (time2 -time1) + ' s '

Believe that you have read the case of this article you have mastered the method, more exciting please pay attention to the PHP Chinese network other related articles!

Recommended reading:

The object of diff is the virtual Dom

Event Emitter Listener Events