Python 2.7_初試串連Mysql查詢資料匯出到exce_20161216

標籤：cursor   沒有   cal   blog   class   ksh   from   匯出到excel   杭州   

由於每天到公司都需要先執行一遍檢測操作,觀察資料是否匯入完整,今天想到能否自動連接Mysql執行SQL並匯出資料,每天到公司直接查看excel檔案即可

時間緊,代碼以及excel格式還沒有調,初次實驗,邊摸索邊學習吧。sql代碼那句總想能不能用個變數代替,顯得太冗長了

#coding:utf-8import MySQLdbimport xlsxwriterimport datetimeimport ostoday = datetime.date.today()oldfile=‘demo‘+str(today.day-2)+‘.xlsx‘newfile=‘demo‘+str(today.day-1)+‘.xlsx‘if os.path.isfile(oldfile) :    os.remove(oldfile)else:    passdef getdata():    conn = MySQLdb.connect(host=‘伺服器IP地址‘, user=‘root‘, passwd=‘伺服器密碼‘, db=‘local_db‘, port=3306, charset=‘utf8‘)    cursor = conn.cursor()    test_sql = ‘‘‘        SELECT DATE(訂單日期) AS 訂單日期,ROUND(SUM(金額)) AS 總計 ,ROUND(SUM(IF(城市="北京",金額,NULL))) AS 北京,ROUND(SUM(IF(城市="成都",金額,NULL))) AS 成都       ,ROUND(SUM(IF(城市="杭州",金額,NULL))) AS 杭州,ROUND(SUM(IF(城市="濟南",金額,NULL))) AS 濟南,ROUND(SUM(IF(城市="長春",金額,NULL))) AS 長春       ,ROUND(SUM(IF(城市="青島",金額,NULL))) AS 青島,ROUND(SUM(IF(城市="南京",金額,NULL))) AS 南京,ROUND(SUM(IF(城市="鄭州",金額,NULL))) AS 鄭州       ,ROUND(SUM(IF(城市="西安",金額,NULL))) AS 西安        FROM a003_order        WHERE 金額>0 AND DATE(訂單日期)>=DATE_ADD(CURRENT_DATE,INTERVAL -15 DAY) AND DATE(訂單日期)<CURRENT_DATE        GROUP BY DATE(訂單日期)    ‘‘‘    count=cursor.execute(test_sql)    conn.commit()    data=cursor.fetchall()    cursor.close()    conn.close()    return datadef write_excel():    data=getdata()    workbook = xlsxwriter.Workbook(newfile)    worksheet = workbook.add_worksheet(‘data‘)    dic1 = {0: "訂單日期", 1: "總計", 2: "北京", 3: "成都", 4: "杭州", 5: "濟南", 6: "長春", 7: "青島", 8: "南京", 9: "鄭州", 10: "西安"}    for i in range(0, 11):        title = dic1[i].decode(‘utf-8‘)        worksheet.write(0, i, title)    for x in range(0, 15):        for y in range(0, 11):            db = str(data[x][y]).decode(‘utf-8‘)            worksheet.write(x + 1, y, db)    workbook.close()print write_excel()

執行匯出到excel是 資料已處理 明天執行時候會先刪除今天執行產生的檔案

 

Python 2.7_初試串連Mysql查詢資料匯出到exce_20161216