Python implements the method of exporting data from MySQL database tables to generate csv format files
- 2020-06-23 01:02:13
- OfStack
This article illustrates an Python implementation that exports data from MySQL database tables to generate csv format files. To share for your reference, specific as follows:
#!/usr/bin/env python
# -*- coding:utf-8 -*-
"""
Purpose: Generate daily summary reconciliation documents
Created: 2015/4/27
Modified:2015/5/1
@author: guoyJoe
"""
# The import module
import MySQLdb
import time
import datetime
import os
# The date of
today = datetime.date.today()
yestoday = today - datetime.timedelta(days=1)
# Check the date
checkAcc_date = yestoday.strftime('%Y%m%d')
# List of reconciliation documents
fileDir = "/u02/filesvrd/report"
#SQL statements
sqlStr1 = 'SELECT distinct pay_custid FROM dbpay.tb_pay_bill WHERE date_acct = %s'
# The total number of pen | Number of successful transactions | Successful transaction amount | Return of the number | Returns the amount of | Cancellation number | Cancellation amount
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:
# The connection MySQL The database
connDB= MySQLdb.connect("192.168.1.6","root","root","test" )
connDB.select_db('test')
curSql1 = connDB.cursor()
# Query merchants
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]
# Create a summary daily billing file name
fileName = '%s/JYMXSUM_%s_%s.csv' %(fileDir,custid,checkAcc_date)
# Determine if the file exists , Delete the file if it exists , Otherwise generate file!
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
# Open the cursor
curSql2= connDB.cursor()
# perform 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))
# To get the data
datesumpay = curSql2.fetchall()
# Open the file
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]
# Generate summary daily billing files
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
More about Python related content interested readers to view this site project: "common database operations Python skills summary", "Python mathematical operation skills summary", "Python data structure and algorithm tutorial", "Python function using techniques", "Python string skills summary", "Python introduction and advanced tutorial" and "Python file and directory skills summary"
I hope this article has been helpful in Python programming.