Python脚本---在MySQL数据库中跑批加载多个表的数据

转载请注明出处：http://blog.csdn.net/guoyjoe/article/details/45841117

#!/usr/bin/env python

# -*- coding:utf-8 -*-


"""
  Purpose: 生成日明细账单数据
  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 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:
#连接MySQL数据库
    cOnnDB= MySQLdb.connect("192.168.1.6","root","root","test" )
    connDB.select_db('test‘)


#删除昨晚账单的数据
    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