mysql跑批_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