Python Script---Load data from multiple tables in a MySQL database

Reprint Please specify 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 MySQLdb
Import time
Import datetime
Import OS




#日期
Today = Datetime.date.today ()
Yestoday = Today-datetime.timedelta (Days=1)


#账单日期
Date_acct = Yestoday.strftime ('%y%m%d ')


#SQL语句
Sqldel = ' DELETE from Dbchkbill.tb_day_chkbill WHERE date_acct =%s '

Sqlins = "" "
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 where 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 is 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
#连接MySQL数据库
CONNDB = MySQLdb.connect ("rdsq99sy52dvv160hz76w.mysql.rds.aliyuncs.com", "Sysdba", "UubeeDBmydb1", "Dbchkbill")
conndb.select_db (' Dbchkbill ')


#删除昨晚账单的数据
Curdel = Conndb.cursor ()
Curdel.execute (SQLDEL,DATE_ACCT)
Conndb.commit ()
Curdel.close ()


#插入昨天账单的数据
Curins = Conndb.cursor ()
Curins.execute (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 '))


#异常
Except mysqldb.error,err_msg:
Print "MySQL error msg:", err_msg

Python Script---Load data from multiple tables in a MySQL database