本文实例讲述了Python实现将MySQL数据库表中的数据导出生成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: 生成日汇总对账文件 Created: 2015/4/27 Modified:2015/5/1 @author: guoyJoe"""#导入模块import MySQLdbimport timeimport datetimeimport 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'#总笔数|成功交易笔数|成功交易金额|退货笔数|退货金额|撤销笔数|撤销金额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("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]#创建汇总日账单文件名称 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#打开游标 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,custid,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,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 |
希望本文所述对大家Python程序设计有所帮助。
原文链接：http://blog.csdn.net/guoyjoe/article/details/45841221
