Reprint Please specify source: http://blog.csdn.net/guoyjoe/article/details/45841221
#!/usr/bin/env python
#-*-Coding:utf-8-*-
"""
Purpose: Generate Daily Summary reconciliation file
Created:2015/4/27
Modified:2015/5/1
@author: Guoyjoe
"""
#导入模块
Import MySQLdb
Import time
Import datetime
Import OS
#日期
Today = Datetime.date.today ()
Yestoday = Today-datetime.timedelta (Days=1)
#对账日期
Checkacc_date = Yestoday.strftime ('%y%m%d ')
#对账文件目录
Filedir = "/u02/filesvrd/report"
#SQL语句
SQLSTR1 = ' SELECT distinct pay_custid from Dbpay.tb_pay_bill WHERE date_acct =%s '
#总笔数 | Number of Successful trades | Transaction amount | return Count | return amount | Undo Number | Undo Amount
Sqlstr2= "" "Select Totalnum,succeednum,succeedamt,returnnum,returnamt,revokenum,revokeamt
From
(SELECT count (order_id) as Totalnum
From (SELECT p.order_id as order_id
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 P.pay_custid =%s
and Q.date_acct =%s
UNION All
SELECT p.order_id as order_id
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 P.col_custid =%s
and Q.date_acct =%s
UNION All
SELECT r.order_id as order_id
From Dbpay. Tb_refund_bill R, Dbpay. Tb_paybillserial Q
WHERE R.oid_refundno = 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 R.ori_col_custid =%s
and Q.date_acct =%s) as total) A,
(SELECT count (order_id) succeednum, sum (amt_paybill) Succeedamt
From (SELECT p.order_id as order_id,
q.amt_payserial/1000 as Amt_paybill
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 = ' 0 '
and Q.pay_stat = 1
and Q.col_stat = 1
and P.pay_custid =%s
and Q.date_acct =%s
UNION All
SELECT p.order_id as order_id,
q.amt_payserial/1000 as Amt_paybill
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 = ' 0 '
and Q.pay_stat = 1
and Q.col_stat = 1
and P.col_custid =%s
and Q.date_acct =%s) as succeed) B,
(SELECT count (order_id) returnnum, sum (amt_paybill) Returnamt
From (SELECT r.order_id as order_id,
q.amt_payserial/1000 as Amt_paybill
From Dbpay. Tb_refund_bill R, Dbpay. Tb_paybillserial Q
WHERE R.oid_refundno = 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 R.ori_col_custid =%s
and Q.date_acct =%s) as retur) C,
(SELECT count (order_id) revokenum,sum (Amt_paybill) Revokeamt
From (SELECT p.order_id as order_id,
q.amt_payserial/1000 as Amt_paybill
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 = ' 4 '
and Q.pay_stat = 1
and Q.col_stat = 1
and P.pay_custid =%s
and Q.date_acct =%s
UNION All
SELECT p.order_id as order_id,
q.amt_payserial/1000 as Amt_paybill
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 = ' 4 '
and Q.pay_stat = 1
and Q.col_stat = 1
and P.col_custid =%s
and Q.date_acct =%s) as Revok) D "" "
Try
#连接MySQL数据库
conndb= MySQLdb.connect ("rdsq99sy52dvv160hz76w.mysql.rds.aliyuncs.com", "Sysdba", "UubeeDBmydb1", "Dbchkbill")
conndb.select_db (' Dbchkbill ')
CURSQL1 = Conndb.cursor ()
#查询商户
Cursql1.execute (Sqlstr1,checkacc_date)
Paycustid = Cursql1.fetchall ()
If Len (Paycustid) < 1:
Print (' No found Checkbill data,please check the data for%s! ' %checkacc_date)
Exit (1)
For row in Paycustid:
CustID = row[0]
#创建汇总日账单文件名称
FileName = '%s/jymxsum_%s_%s.csv '% (filedir,custid,checkacc_date)
#判断文件是否存在, delete the file if it exists, otherwise generate the file!
If Os.path.exists (fileName):
Os.remove (FileName)
print ' The file start generating! %s '%time.strftime ('%y-%m-%d%h:%m:%s ')
print '%s '%filename
#打开游标
Cursql2= Conndb.cursor ()
#执行SQL
Checkacc_date = Yestoday.strftime ('%y%m%d ')
Cursql2.execute (SQLSTR2, (custid,checkacc_date,custid,checkacc_date,custid,checkacc_date,custid,checkacc_date,c
Ustid,checkacc_date,custid,checkacc_date,custid,checkacc_date,custid,checkacc_date))
#获取数据
Datesumpay = Cursql2.fetchall ()
#打开文件
outfile = open (FileName, ' W ')
For Sumpay in Datesumpay:
Totalnum = sumpay[0]
Succeednum = sumpay[1]
succeedamt= Sumpay[2]
Returnnum = sumpay[3]
Returnamt = Sumpay[4]
Revokenum = sumpay[5]
Revokeamt = Sumpay[6]
#生成汇总日账单文件
Outfile.write ('%s|%s|%s|%s|%s|%s|%s\n '% (Totalnum,succeednum,succeedamt,returnnum,returnamt,revokenum,revo
Keamt))
Outfile.flush ()
Cursql2.close ()
Cursql1.close ()
Conndb.close ()
print ' The file has been generated! %s '%time.strftime ('%y-%m-%d%h:%m:%s ')
Except mysqldb.error,err_msg:
Print "MySQL error msg:", err_msg
Python Script---Export data from MySQL database tables to generate CSV format files
