Python 2.7_ Preliminary connection MySQL query data export to exce_20161216

Because every day to the company need to perform the inspection operations first, to observe whether the data import integrity, today think of the ability to automatically connect to MySQL to execute SQL and export data, every day to the company to view the Excel file directly

Time tight, code and Excel format has not been adjusted, the first experiment, while groping to learn it. SQL code that sentence always want to be able to use a variable instead, it seems too lengthy

#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= ' Server IP address ', user= ' root ', passwd= ' server password ', db= ' local_db ', port=3306, Charse t= ' UTF8 ') cursor = conn.cursor () test_sql = ' ' SELECT date (order date) as Order date, ROUND (sum (amount)) as Total, ROUND (SUM (IF ( City = "Beijing", Amount, NULL)) as Beijing, ROUND (sum (if (city = "Chengdu", amount, null)) as Chengdu, ROUND (SUM (if (city = "Hangzhou", Amount, NULL)) as Hangzhou, ROUND (if (city City = "Jinan", amount, NULL)) as Jinan, ROUND (SUM (if (city = "Changchun", amount, NULL))) as Changchun, ROUND (SUM (if (city = "Qingdao", Amount, NULL)) as Qingdao, ROUND (SUM (if (city = "Nanjing", Amount, NULL)) as Nanjing, ROUND (SUM (if (city = "Zhengzhou", amount, NULL))) as Zhengzhou, ROUND (SUM (if (city = "Xi ' an", amount, NULL))) as Xi ' an from a003        _order WHERE amount >0 and date (order date) >=date_add (Current_date,interval-15 Day) and date (order date) <current_date GROUP by date (order date) "C"Ount=cursor.execute (Test_sql) conn.commit () Data=cursor.fetchall () Cursor.close () Conn.close () return Datad EF write_excel (): Data=getdata () workbook = Xlsxwriter. Workbook (newfile) worksheet = workbook.add_worksheet (' data ') Dic1 = {0: "Order Date", 1: "Total", 2: "Beijing", 3: "Chengdu", 4: "Hangzhou",        5: "Jinan", 6: "Changchun", 7: "Qingdao", 8: "Nanjing", 9: "Zhengzhou", 10: "Xi ' an"} for I in range (0, one): title = Dic1[i].decode (' Utf-8 ') Worksheet.write (0, I, title) for X in range (0, a): For Y in range (0, one): db = str (data[x][y]). Decode (' Utf-8 ') worksheet.write (x + 1, y, db) workbook.close () print write_excel ()

Performing an export to Excel is that the data has been processed tomorrow, and the generated files will be deleted today when executed

Python 2.7_ Preliminary connection MySQL query data export to exce_20161216