Python实现将MySQL数据库表中的数据导出生成csv格式文件的方法
本文实例讲述了Python实现将MySQL数据库表中的数据导出生成csv格式文件的方法。分享给大家供大家参考，具体如下：
#!/usr/bin/envpython
#-*-coding:utf-8-*-
"""
Purpose:生成日汇总对账文件
Created:2015/4/27
Modified:2015/5/1
@author:guoyJoe
"""
#导入模块
importMySQLdb
importtime
importdatetime
importos
#日期
today=datetime.date.today()
yestoday=today-datetime.timedelta(days=1)
#对账日期
checkAcc_date=yestoday.strftime('%Y%m%d')
#对账文件目录
fileDir="/u02/filesvrd/report"
#SQL语句
sqlStr1='SELECTdistinctpay_custidFROMdbpay.tb_pay_billWHEREdate_acct=%s'
#总笔数|成功交易笔数|成功交易金额|退货笔数|退货金额|撤销笔数|撤销金额
sqlStr2="""SELECTtotalNum,succeedNum,succeedAmt,returnNum,returnAmt,revokeNum,revokeAmt
FROM
(SELECTcount(order_id)AStotalNum
FROM(SELECTp.order_idasorder_id
FROMdbpay.tb_pay_billp,dbpay.tb_paybillserialq
WHEREp.oid_billno=q.oid_billno
ANDp.paycust_accttype=2
ANDp.Paycust_Type=1
ANDp.stat_billin(0,4)
ANDq.pay_stat=1
ANDq.col_stat=1
ANDp.pay_custid=%s
ANDq.date_acct=%s
UNIONALL
SELECTp.order_idasorder_id
FROMdbpay.tb_pay_billp,dbpay.tb_paybillserialq
WHEREp.oid_billno=q.oid_billno
ANDp.col_accttype=2
ANDp.col_type=1
ANDp.stat_billin(0,4)
ANDq.pay_stat=1
ANDq.col_stat=1
ANDp.col_custid=%s
ANDq.date_acct=%s
UNIONALL
SELECTR.ORDER_IDASORDER_ID
FROMDBPAY.TB_REFUND_BILLR,DBPAY.TB_PAYBILLSERIALQ
WHERER.oid_refundno=Q.OID_BILLNO
ANDR.ORI_COL_ACCTTYPE=2
ANDR.ORI_COL_TYPE=1
ANDR.STAT_BILL=2
ANDQ.PAY_STAT=1
ANDQ.COL_STAT=1
ANDR.ORI_COL_CUSTID=%s
ANDQ.DATE_ACCT=%s)astotal)A,
(SELECTcount(order_id)succeedNum,sum(amt_paybill)succeedAmt
FROM(SELECTp.order_idasorder_id,
q.amt_payserial/1000asamt_paybill
FROMdbpay.tb_pay_billp,dbpay.tb_paybillserialq
WHEREp.oid_billno=q.oid_billno
ANDp.paycust_accttype=2
ANDp.Paycust_Type=1
ANDp.stat_bill='0'
ANDq.pay_stat=1
ANDq.col_stat=1
ANDp.pay_custid=%s
ANDq.date_acct=%s
UNIONALL
SELECTp.order_idasorder_id,
q.amt_payserial/1000asamt_paybill
FROMdbpay.tb_pay_billp,dbpay.tb_paybillserialq
WHEREp.oid_billno=q.oid_billno
ANDp.col_accttype=2
ANDp.col_type=1
ANDp.stat_bill='0'
ANDq.pay_stat=1
ANDq.col_stat=1
ANDp.col_custid=%s
ANDq.date_acct=%s)assucceed)B,
(SELECTcount(order_id)returnNum,sum(amt_paybill)returnAmt
FROM(SELECTR.ORDER_IDASORDER_ID,
Q.AMT_PAYSERIAL/1000ASAMT_PAYBILL
FROMDBPAY.TB_REFUND_BILLR,DBPAY.TB_PAYBILLSERIALQ
WHERER.oid_refundno=Q.OID_BILLNO
ANDR.ORI_COL_ACCTTYPE=2
ANDR.ORI_COL_TYPE=1
ANDR.STAT_BILL=2
ANDQ.PAY_STAT=1
ANDQ.COL_STAT=1
ANDR.ORI_COL_CUSTID=%s
ANDQ.DATE_ACCT=%s)asretur)C,
(SELECTcount(order_id)revokeNum,sum(amt_paybill)revokeAmt
FROM(SELECTp.order_idasorder_id,
q.amt_payserial/1000asamt_paybill
FROMdbpay.tb_pay_billp,dbpay.tb_paybillserialq
WHEREp.oid_billno=q.oid_billno
ANDp.paycust_accttype=2
ANDp.Paycust_Type=1
ANDp.stat_bill='4'
ANDq.pay_stat=1
ANDq.col_stat=1
ANDp.pay_custid=%s
ANDq.date_acct=%s
UNIONALL
SELECTp.order_idasorder_id,
q.amt_payserial/1000asamt_paybill
FROMdbpay.tb_pay_billp,dbpay.tb_paybillserialq
WHEREp.oid_billno=q.oid_billno
ANDp.col_accttype=2
ANDp.col_type=1
ANDp.stat_bill='4'
ANDq.pay_stat=1
ANDq.col_stat=1
ANDp.col_custid=%s
ANDq.date_acct=%s)asrevok)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()
iflen(payCustID)<1:
print('Nofoundcheckbilldata,Pleasecheckthedatafor%s!'%checkAcc_date)
exit(1)
forrowinpayCustID:
custid=row[0]
#创建汇总日账单文件名称
fileName='%s/JYMXSUM_%s_%s.csv'%(fileDir,custid,checkAcc_date)
#判断文件是否存在,如果存在则删除文件,否则生成文件！
ifos.path.exists(fileName):
os.remove(fileName)
print'Thefilestartgenerating!%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')
forsumpayindatesumpay:
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'Thefilehasbeengenerated!%s'%time.strftime('%Y-%m-%d%H:%M:%S')
exceptMySQLdb.Error,err_msg:
print"MySQLerrormsg:",err_msg
更多关于Python相关内容感兴趣的读者可查看本站专题：《Python常见数据库操作技巧汇总》、《Python数学运算技巧总结》、《Python数据结构与算法教程》、《Python函数使用技巧总结》、《Python字符串操作技巧汇总》、《Python入门与进阶经典教程》及《Python文件与目录操作技巧汇总》
希望本文所述对大家Python程序设计有所帮助。