python 記一次將資料庫查詢結果寫入到表格經曆

標籤：sql python xlwt 表格

python 記一次將資料庫查詢結果寫入到表格經曆

說明：

    運營那邊提了一些需求，在開發還沒有在頁面上實現此功能前，每天早上都要我們查詢語句做成表格，然後發給他們，感覺好煩啊，就用python寫了一個指令碼，實現此功能

第一次嘗試：

#!/usr/bin/env python#encoding=UTF-8import MySQLdbimport xlwt    #excel writehostIp = ‘10.10.94.157‘user = ‘xxxx‘passwd = ‘xxxx‘database = ‘xxxx‘def chongZhiWeiTouZi():          #儲值未投資使用者    sql = ‘‘‘SELECTctci.acc_no AS ‘帳號‘,ctci.cn_name AS ‘姓名‘,ctci.job_phone AS ‘手機‘,ctci.create_date AS ‘註冊時間‘,        tta.amount AS ‘儲值金額‘,        tta.create_time AS ‘儲值時間‘FROM        TPP_T_ACCOUNT_DEAL tta,CRM_T_CUSTOMER_INFO ctciWHERE        tta.customer_id = ctci.id AND         tta.type = 1AND tta.order_status IN (1, 9)AND tta.customer_id NOT IN (SELECT DISTINCT customer_id FROM J_P2P_ORDER WHERE order_type = 1 and product_id !=1)‘‘‘    columnName = [‘帳號‘,‘姓名‘,‘手機‘,‘註冊時間‘,‘儲值金額‘,‘儲值時間‘]  #定義所有的列名，共6列    style1= xlwt.XFStyle()                   #設定儲存格格式    style1.num_format_str= ‘yyyy/m/d h:mm:ss‘    wb=xlwt.Workbook(encoding=‘utf-8‘)       #建立一個excel活頁簿，編碼utf-8，表格中支援中文    sheet=wb.add_sheet(‘sheet 1‘)            #建立一個sheet    for i in range(len(columnName)):         #將列名插入表格，共6列        sheet.write(0,i,columnName[i])    db = MySQLdb.connect(hostIp,user,passwd,database,charset="utf8")         #串連資料庫，編碼utf-8    cursor = db.cursor()                     #建立一個指標對象    cursor.execute(sql)                      #執行sql語句    results = cursor.fetchall()    rows = len(results)                      #擷取行數    for i in range(rows):        for j in range(3):            sheet.write(i+1,j,results[i][j])        sheet.write(i+1,3,results[i][3],style1)         #設定時間列的儲存格格式        sheet.write(i+1,4,results[i][4])        sheet.write(i+1,5,results[i][5],style1)    wb.save(‘員工資訊表.xls‘)               #儲存表格，並命名為 員工資訊表.xls    cursor.close()    db.close()def yongHuDengLu():             #使用者登入資訊    sql = ‘‘‘SELECT        login_type AS ‘來源‘,        cn_name AS ‘登入名稱‘,        login_addr AS ‘登入IP‘,        mobile AS ‘手機號‘,        area AS ‘登入地區‘,        mome AS ‘手機電訊廠商‘,        login_time AS ‘登入時間‘FROM        SYS_T_LOGIN_LOGGER ORDER BY login_time DESC‘‘‘    columnName = [‘來源‘,‘登入名稱‘,‘登入IP‘,‘手機號‘,‘登入地區‘,‘手機電訊廠商‘,‘登入時間‘]  #定義所有的列名    style1= xlwt.XFStyle()                   #設定儲存格格式    style1.num_format_str= ‘yyyy/m/d h:mm:ss‘    wb=xlwt.Workbook(encoding=‘utf-8‘)       #建立一個excel活頁簿，編碼utf-8，表格中支援中文    sheet=wb.add_sheet(‘sheet 1‘)            #建立一個sheet    for i in range(len(columnName)):         #將列名插入表格        sheet.write(0,i,columnName[i])    db = MySQLdb.connect(hostIp,user,passwd,database,charset="utf8")    cursor = db.cursor()                     #建立一個指標對象    cursor.execute(‘use J_P2P‘)    cursor.execute(sql)                      #執行sql語句    results = cursor.fetchall()    rows = len(results)                      #擷取行數    for i in range(rows):        for j in range(6):            sheet.write(i+1,j,results[i][j])        sheet.write(i+1,6,results[i][6],style1)         #設定時間列的儲存格格式    wb.save(‘使用者登入資訊.xls‘)               #儲存表格    cursor.close()    db.close()chongZhiWeiTouZi()yongHuDengLu()

分析：

    因為有多個sql查詢語句，製作成多個表格，所以寫了多個函數，將sql語句分別寫到每個函數裡，而且表格的列名也是需要手動輸入的，感覺好麻煩，而且好傻瓜

第二次嘗試：

#!/usr/bin/env python#encoding=UTF-8import MySQLdbimport xlwt    #excel writehostIp = ‘10.10.94.157‘user = ‘xxx‘passwd = ‘xxxx‘database = ‘xxxxx‘f = open(‘select.sql‘,‘r‘)reSql = f.read().split(‘;‘)            #將所有的sql語句賦值給reSql; select.sql檔案裡的sql語句要以‘;‘結尾f.close()def chongZhiWeiTouZi():          #儲值未投資使用者    style1= xlwt.XFStyle()                   #設定儲存格格式    style1.num_format_str= ‘yyyy/m/d h:mm:ss‘    wb=xlwt.Workbook(encoding=‘utf-8‘)       #建立一個excel活頁簿，編碼utf-8，表格中支援中文    sheet=wb.add_sheet(‘sheet 1‘)            #建立一個sheet    db = MySQLdb.connect(hostIp,user,passwd,database,charset="utf8")         #串連資料庫，編碼utf-8    cursor = db.cursor(cursorclass = MySQLdb.cursors.DictCursor)                     #建立一個指標對象    cursor.execute(‘use J_P2P‘)    cursor.execute(reSql[0])                      #執行sql語句    results = cursor.fetchall()    columnName = []    for i in results[0].keys():        columnName.append(i)    columnLen = len(columnName)    for i in range(columnLen):         #將列名插入表格，共6列        sheet.write(0,i,columnName[i])    rows = len(results)                      #擷取行數    for i in range(rows):        for j in range(columnLen):            sheet.write(i+1,j,results[i][columnName[j]])    wb.save(‘員工資訊表.xls‘)               #儲存表格，並命名為 員工資訊表.xls    cursor.close()    db.close()def yongHuDengLu():          #使用者登入資訊    style1= xlwt.XFStyle()                   #設定儲存格格式    style1.num_format_str= ‘yyyy/m/d h:mm:ss‘    wb=xlwt.Workbook(encoding=‘utf-8‘)       #建立一個excel活頁簿，編碼utf-8，表格中支援中文    sheet=wb.add_sheet(‘sheet 1‘)            #建立一個sheet    db = MySQLdb.connect(hostIp,user,passwd,database,charset="utf8")         #串連資料庫，編碼utf-8    cursor = db.cursor(cursorclass = MySQLdb.cursors.DictCursor)                     #建立一個指標對象    cursor.execute(reSql[1])                      #執行sql語句    results = cursor.fetchall()    columnName = []    for i in results[0].keys():        columnName.append(i)    columnLen = len(columnName)    for i in range(columnLen):         #將列名插入表格，共6列        sheet.write(0,i,columnName[i])    rows = len(results)                      #擷取行數    for i in range(rows):        for j in range(columnLen):            sheet.write(i+1,j,results[i][columnName[j]])    wb.save(‘使用者登入資訊.xls‘)               #儲存表格，並命名為 員工資訊表.xls    cursor.close()    db.close()chongZhiWeiTouZi()yongHuDengLu()

分析：

    這次把需要查詢的sql語句全部寫到一個檔案裡，注意：要以‘；’結尾

vim select.sqlSELECTctci.acc_no AS ‘帳號‘,ctci.cn_name AS ‘姓名‘,ctci.job_phone AS ‘手機‘,ctci.create_date AS ‘註冊時間‘,        tta.amount AS ‘儲值金額‘,        tta.create_time AS ‘儲值時間‘FROM        TPP_T_ACCOUNT_DEAL tta,CRM_T_CUSTOMER_INFO ctciWHERE        tta.customer_id = ctci.id AND        tta.type = 1AND tta.order_status IN (1, 9)AND tta.customer_id NOT IN (SELECT DISTINCT customer_id FROM J_P2P_ORDER WHERE order_type = 1 and product_id !=1);select a.acc_no as ‘使用者名稱‘,a.cn_name as ‘姓名‘,a.mobile as ‘銀行預留手機‘,a.job_phone as ‘登入手機‘,b.order_amount as ‘投資金額‘,c.name as ‘產品‘ ,b.lucky_numbers as ‘幸運號‘from ACTIVITY_LUCKY_NUMBER b left join CRM_T_CUSTOMER_INFO a on b.customer_id = a.idleft join J_P2P_PRODUCT c on b.product_id = c.idwhere  b.period=‘4‘;

    而且列名會自己從查詢結果裡擷取

    不過，感覺還是很亂，很複雜，而且用了多個函數，而且最終的表格格式還不好看，如下：

650) this.width=650;" src="http://s5.51cto.com/wyfs02/M02/79/42/wKioL1aM2JKBuUfTAAAmuoX_Fs8171.png" title="QQ20160106170337.png" alt="wKioL1aM2JKBuUfTAAAmuoX_Fs8171.png" />

第三次嘗試：

    這次沒有再用MySQLdb模組

#!/usr/bin/env python#encoding=UTF-8import osimport xlwt    #excel writehostIp = ‘10.10.94.157‘user = ‘xxxx‘passwd = ‘xxxx‘db = ‘xxxx‘f = open(‘select.sql‘,‘r‘)reSql = f.read().split(‘;‘)            #將所有的sql語句賦值給reSql; select.sql檔案裡的sql語句要以‘;‘結尾f.close()def createTable(selectSql,tableName):     results = os.popen(‘mysql -h‘+hostIp+‘ -u‘+user+‘ -p‘+passwd+‘ -D‘+db+‘ -e "‘+selectSql+‘"‘).read().strip().split(‘\n‘)    columnName = results[0].split(‘\t‘)    wb=xlwt.Workbook(encoding=‘utf-8‘)       #建立一個excel活頁簿，編碼utf-8，表格中支援中文    sheet=wb.add_sheet(‘sheet 1‘)            #建立一個sheet    rows = len(results)                      #擷取行數    columns = len(columnName)    for i in range(rows):        for j in range(columns):            sheet.write(i,j,results[i].split(‘\t‘)[j])    wb.save(tableName)               #儲存表格    createTable(reSql[0],‘儲值未投資使用者.xls‘)createTable(reSql[1],‘活動投資名單.xls‘)

分析：

    這次感覺還不錯，唯寫了一個函數

    所有的sql語句同樣寫到select.sql檔案裡，且以‘；’結尾

    這次表格樣式如下：

650) this.width=650;" src="http://s3.51cto.com/wyfs02/M01/79/44/wKiom1aM3LTyDtrAAAAktjWkuIg052.png" title="QQ20160106172059.png" alt="wKiom1aM3LTyDtrAAAAktjWkuIg052.png" />

    表格格式感覺還是不好看

最終最佳化：

    這次最佳化了表格的輸出格式

    最佳化了select.sql檔案：裡面可以寫以#開頭的注釋

vim select.sql#儲值未投資使用者#SELECTctci.acc_no AS ‘帳號‘,ctci.cn_name AS ‘姓名‘,ctci.job_phone AS ‘手機‘,ctci.create_date AS ‘註冊時間‘,        tta.amount AS ‘儲值金額‘,        tta.create_time AS ‘儲值時間‘FROM        TPP_T_ACCOUNT_DEAL tta,CRM_T_CUSTOMER_INFO ctciWHERE        tta.customer_id = ctci.id AND        tta.type = 1AND tta.order_status IN (1, 9)AND tta.customer_id NOT IN (SELECT DISTINCT customer_id FROM J_P2P_ORDER WHERE order_type = 1 and product_id !=1);#活動投資名單select a.acc_no as ‘使用者名稱‘,a.cn_name as ‘姓名‘,a.mobile as ‘銀行預留手機‘,a.job_phone as ‘登入手機‘,b.order_amount as ‘投資金額‘,c.name as ‘產品‘ ,b.lucky_numbers as ‘幸運號‘from ACTIVITY_LUCKY_NUMBER b left join CRM_T_CUSTOMER_INFO a on b.customer_id = a.idleft join J_P2P_PRODUCT c on b.product_id = c.idwhere  b.period=‘4‘;

本文出自 “見” 部落格，請務必保留此出處http://732233048.blog.51cto.com/9323668/1732136

python 記一次將資料庫查詢結果寫入到表格經曆