Python实现将MySQL数据库表中的数据导出⽣成csv格式
⽂件的⽅法
本⽂实例讲述了Python实现将MySQL数据库表中的数据导出⽣成csv格式⽂件的⽅法。分享给⼤家供⼤家参考，具体如下：#!/usr/bin/env python
# -*- coding:utf-8 -*-
"""
Purpose: ⽣成⽇汇总对账⽂件
Created: 2015/4/27
Modified:2015/5/1
@author: guoyJoe
"""
#导⼊模块
import MySQLdb
import time
import datetime
import os
#⽇期
today = day()
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 (der_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
mysql数据库的方法AND p.stat_bill in (0, 4)
AND q.pay_stat = 1
l_stat = 1
AND p.pay_custid = %s
AND q.date_acct = %s
UNION ALL
der_id as order_id
FROM dbpay.tb_pay_bill p, dbpay.tb_paybillserial q
WHERE p.oid_billno = q.oid_billno
l_accttype = 2
l_type = 1
AND p.stat_bill in (0, 4)
AND q.pay_stat = 1
l_stat = 1
l_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 (der_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
l_stat = 1
AND p.pay_custid = %s
AND q.date_acct = %s
UNION ALL
der_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
l_accttype = 2
l_type = 1
AND p.stat_bill = '0'
AND q.pay_stat = 1
l_stat = 1
l_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 (der_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
l_stat = 1
AND p.pay_custid = %s
AND q.date_acct = %s
UNION ALL
der_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
l_accttype = 2
l_type = 1
AND p.stat_bill = '4'
AND q.pay_stat = 1
l_stat = 1
l_custid = %s
AND q.date_acct = %s) as revok) D"""
try:
#连接MySQL数据库
connDB= t("192.168.1.6","root","root","test" )
connDB.select_db('test')
curSql1 = connDB.cursor()
#查询商户
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 ists(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')
#获取数据
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相关内容感兴趣的读者可查看本站专题：《》、《》、《》、《》、《》、《》及《》希望本⽂所述对⼤家Python程序设计有所帮助。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。