Python exports data from MySQL database tables to generate csv files,

Python exports data from MySQL database tables to generate csv files,

This example describes how to export data from MySQL database tables to generate csv files using Python. We will share this with you for your reference. The details are as follows:

#! /Usr/bin/env python #-*-coding: UTF-8-*-"Purpose: generate the daily summary reconciliation file Created: 2015/4/27 Modified: 2015/5/1 @ author: guoyJoe "" # import module import MySQLdbimport timeimport datetimeimport OS # date today = datetime. date. today () yestoday = today-datetime. timedelta (days = 1) # reconciliation date checkAcc_date = yestoday. strftime ('% Y % m % D') # reconciliation file directory fileDir = "/u02/filesvrd/report" # SQL statement sqlStr1 = 'select distinct pay_custid FROM dbpay. tb_pay_ B Ill WHERE date_acct = % s' # Total number of transactions | Number of successful transactions | amount of successful transactions | Number of returned items | amount of returned items | number of canceled items | withdrawal 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_R EFUND_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_T Ype = 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: # connect to MySQL database connDB = MySQLdb. connect ("192.168.1.6", "root", "root", "test") connDB. select_db ('test') curSql1 = connDB. cursor () # query merchant 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 Bill file name fileName =' % s/JYMXSUM_%s_%s.csv '% (fileDir, custid, checkAcc_date) # determine whether a file exists. If yes, delete the file; otherwise, generate the 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 () # run SQL checkAcc_date = yestoday. strftime ('% Y % m % D') hour (sqlStr2, (custid, hour, custid, hour, custid, checkAcc_date, custid, checkAcc_date, custid, checkAcc_date, custid, checkAcc_date) # obtain data datesumpay = curSql2.fetchall () # open the file outfile = open (fileNam E, '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 the summary daily Bill file outfile. write ('% s | % s \ n' % (totalNum, succeedNum, succeedAmt, returnNum, returnAmt, revokeNum, revokeAmt) outfile. flush () curSql2.close () curSql1.close () connDB. close () print 'The file has been g Enerated! % S' % time. strftime ('% Y-% m-% d % H: % M: % s') cannot be MySQLdb. error, err_msg: print "MySQL error msg:", err_msg