重慶分公司
重慶分公司，新征程啟航
為企業(yè)提供網(wǎng)站建設、域名注冊、服務器等服務
Python如何將MySQL數(shù)據(jù)庫表中的數(shù)據(jù)導出生成csv格式文件-創(chuàng)新互聯(lián)
這篇文章將為大家詳細講解有關Python如何將MySQL數(shù)據(jù)庫表中的數(shù)據(jù)導出生成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 = 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,c
ustid,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,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如何將MySQL數(shù)據(jù)庫表中的數(shù)據(jù)導出生成csv格式文件”這篇文章就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，使各位可以學到更多知識，如果覺得文章不錯，請把它分享出去讓更多的人看到。
另外有需要云服務器可以了解下創(chuàng)新互聯(lián)scvps.cn，海內(nèi)外云服務器15元起步，三天無理由+7*72小時售后在線，公司持有idc許可證，提供“云服務器、裸金屬服務器、高防服務器、香港服務器、美國服務器、虛擬主機、免備案服務器”等云主機租用服務以及企業(yè)上云的綜合解決方案，具有“安全穩(wěn)定、簡單易用、服務可用性高、性價比高”等特點與優(yōu)勢，專為企業(yè)上云打造定制，能夠滿足用戶豐富、多元化的應用場景需求。
網(wǎng)頁題目：Python如何將MySQL數(shù)據(jù)庫表中的數(shù)據(jù)導出生成csv格式文件-創(chuàng)新互聯(lián)
地址分享：http://www.xueling.net.cn/article/dgjpeg.html