本文實(shí)例講述了Python實(shí)現(xiàn)將MySQL數(shù)據(jù)庫(kù)表中的數(shù)據(jù)導(dǎo)出生成csv格式文件的方法。分享給大家供大家參考，具體如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
|
#!/usr/bin/env python# -*- coding:utf-8 -*-""" Purpose: 生成日匯總對(duì)賬文件 Created: 2015/4/27 Modified:2015/5/1 @author: guoyJoe"""#導(dǎo)入模塊import MySQLdbimport timeimport datetimeimport os#日期today = datetime.date.today()yestoday = today - datetime.timedelta(days=1)#對(duì)賬日期checkAcc_date = yestoday.strftime('%Y%m%d')#對(duì)賬文件目錄fileDir = "/u02/filesvrd/report"#SQL語(yǔ)句sqlStr1 = 'SELECT distinct pay_custid FROM dbpay.tb_pay_bill WHERE date_acct = %s'#總筆數(shù)|成功交易筆數(shù)|成功交易金額|退貨筆數(shù)|退貨金額|撤銷筆數(shù)|撤銷金額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數(shù)據(jù)庫(kù) connDB= MySQLdb.connect("192.168.1.6","root","root","test" ) connDB.select_db('test') 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]#創(chuàng)建匯總?cè)召~單文件名稱 fileName = '%s/JYMXSUM_%s_%s.csv' %(fileDir,custid,checkAcc_date)#判斷文件是否存在, 如果存在則刪除文件,否則生成文件！ 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#打開(kāi)游標(biāo) curSql2= connDB.cursor()#執(zhí)行SQL checkAcc_date = yestoday.strftime('%Y%m%d') curSql2.execute(sqlStr2,(custid,checkAcc_date,custid,checkAcc_date,custid,checkAcc_date,custid,checkAcc_date,custid,checkAcc_date,custid,checkAcc_date,custid,checkAcc_date,custid,checkAcc_date))#獲取數(shù)據(jù) datesumpay = curSql2.fetchall()#打開(kāi)文件 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]#生成匯總?cè)召~單文件 outfile.write('%s|%s|%s|%s|%s|%s|%s\n' %(totalNum,succeedNum,succeedAmt,returnNum,returnAmt,revokeNum,revokeAmt)) 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 |
希望本文所述對(duì)大家Python程序設(shè)計(jì)有所幫助。
原文鏈接：http://blog.csdn.net/guoyjoe/article/details/45841221
