Python script-run the batch load of data from multiple tables in the MySQL database, pythonmysql
Reprinted please indicate the source: http://blog.csdn.net/guoyjoe/article/details/45841117
#! /Usr/bin/env python
#-*-Coding: UTF-8 -*-
"""
Purpose: generate daily ledger data
Created: 2015/4/21
Modified: 2015/4/24
@ Author: guoyJoe
"""
# Import module
Import MySQLdb
Import time
Import datetime
Import OS
# Date
Today = datetime. date. today ()
Yestoday = today-datetime. timedelta (days = 1)
# Bill date
Date_acct = yestoday. strftime ('% Y % m % D ')
# SQL statements
SqlDel = 'delete FROM dbchkbill. tb_day_chkbill WHERE date_acct = % s'
Sqloud = """
Insert into dbchkbill. tb_day_chkbill (order_id, traderno, order_dtsend, oid_biz, oid_billno, date_acct, amt_paybill, flag,
Stat_bill, pay_prod, pay_type, order_info, dt_billupd)
SELECT p. order_id as order_id, q. pay_custid as traderno, p. order_dtsend as order_dtsend,
Q. oid_biz as oid_biz, CAST (q. oid_billno as char) as oid_billno, q. date_acct as date_acct,
Q. amt_payserial as amt_paybill, '1' as flag, p. stat_bill as stat_bill, p. oid_chnl as pay_prod,
Q. pay_type as pay_type, p. order_info as order_info, q. dt_col as dt_billupd
FROM dbpay. tb_pay_bill p, dbpay. tb_paybillserial q
WHERE p. oid_billno = q. oid_billno and p. paycust_accttype = '2'
AND p. Paycust_Type = '1'
AND p. stat_bill in ('0', '4 ')
AND q. pay_stat = '1'
AND q. col_stat = '1'
And q. date_acct = % s
UNION ALL
SELECT distinct p. order_id as order_id, p. col_custid as traderno,
P. order_dtsend as order_dtsend, q. oid_biz as oid_biz, CAST (q. oid_billno as char) as oid_billno,
Q. date_acct as date_acct, q. amt_payserial as amt_paybill ,(
Case when q. amt_payserial> 0 then '0' else '1' end) as flag, p. stat_bill as stat_bill,
P. oid_chnl as pay_prod, q. pay_type as pay_type, p. order_info as order_info, q. dt_col as dt_billupd
FROM dbpay. tb_pay_bill p, dbpay. tb_paybillserial q
WHERE p. oid_billno = q. oid_billno
AND p. col_accttype = 2
AND p. col_type = 1
AND p. stat_bill in (0, 4)
AND q. pay_stat = 1
AND q. col_stat = 1
AND q. DATE_ACCT = % s
UNION ALL
Select r. ORDER_ID AS ORDER_ID, R. ORI_COL_CUSTID as traderno, R. ORDER_DT AS ORDER_DTSEND,
Q. OID_BIZ AS OID_BIZ, R. ORI_ORDER_ID AS OID_BILLNO, q. date_acct as date_acct,
Q. AMT_PAYSERIAL AS AMT_PAYBILL,
(Case when q. Colcust_Type = '1' THEN '0' when q. Paycust_Type = '1' then'1' END) as flag,
IF (R. STAT_BILL, '2', '5') AS STAT_BILL, r. oid_chnl as pay_prod, q. pay_type as pay_type, r. memo as order_info,
Q. dt_col as dt_billupd from dbpay. TB_REFUND_BILL R, DBPAY. TB_PAYBILLSERIAL Q
Where r. oid_refundno = trim (Q. OID_BILLNO)
And r. ORI_COL_ACCTTYPE = 2
And r. ORI_COL_TYPE = 1
And r. STAT_BILL = 2
And q. PAY_STAT = 1
And q. COL_STAT = 1
And q. DATE_ACCT = % s
"""
Try:
# Connecting to the MySQL database
ConnDB = MySQLdb. connect ("192.168.1.6", "root", "root", "test ")
ConnDB. select_db ('test ')
# Delete the bill data from last night
CurDel = connDB. cursor ()
CurDel.exe cute (sqlDel, date_acct)
ConnDB. commit ()
CurDel. close ()
# Insert the data of yesterday's bill
CurIns = connDB. cursor ()
CurIns.exe cute (sqlIns, (date_acct, date_acct, date_acct ))
ConnDB. commit ()
CurIns. close ()
ConnDB. close ()
Print ('insert the billing data successfully! % S' % time. strftime ('% Y-% m-% d % H: % M: % s '))
# Exception
Failed t MySQLdb. Error, err_msg:
Print "MySQL error msg:", err_msg
