本文實例講述了Python實現(xiàn)將MySQL數(shù)據(jù)庫表中的數(shù)據(jù)導(dǎo)出生成csv格式文件的方法。分享給大家供大家參考，具體如下：

#!/usr/bin/env python# -*- coding:utf-8 -*-""" Purpose: 生成日匯總對賬文件 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)#對賬日期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'#總筆數(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ù)庫  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#打開游標      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()#打開文件      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