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

Source: Internet
Author: User
Tags python script

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.