python連接資料庫，處理資料結果后生成excel檔案

# _*_coding:utf-8 _*_
import time
import xlwt
import os
import pymysql
import sys
import datetime
from datetime import datetime, timedelta

class writefile:
file = r"D:\Users\xx\Desktop"

#查詢資料庫結果
def datacommon(self,mounth,day,n,abj):
res = []
self.date1 = self.dates(mounth,day,n,abj)
for date in self.date1:

starttime = date + ' 00:00:00'
endtime = date + ' 23:59:59'

#連接資料庫
db = pymysql.connect(host="12.12.12.12", user="root", password="sqldb",port=3306, db="test")
# 使用cursor()方法獲取操作游標
cursor = db.cursor()
cursor.execute(f"SELECT project_name FROM `data`.`table` WHERE project_name like \"%test%\" AND create_time between '{starttime}' and '{endtime}'")
project_name = sorted(set(cursor.fetchall()))

for name in project_name:
#計算主線資料
cursor.execute(f"SELECT count(*) FROM `data`.`table` where branch_name like lower(trim(\"%-{name[0]}\")) AND status=0 AND create_time between '{starttime}' and '{endtime}'")
succ_NUM = cursor.fetchall()[0][0]
cursor.execute(f"SELECT count(*) FROM `data`.`table` where branch_name like lower(trim(\"%-{name[0]}\")) AND status=2 AND create_time between '{starttime}' and '{endtime}'")
fail_NUM = cursor.fetchall()[0][0]
if fail_NUM !=0 and succ_NUM !=0:
fail_RATE = "%.2f%%" % (fail_NUM / (succ_NUM + fail_NUM) * 100)
else:
fail_RATE = 0

#計算分支資料
cursor.execute(f"SELECT count(*) FROM `data`.`table` where branch_name like lower(trim(\"%-{name[0]}-%\")) AND status=0 AND create_time between '{starttime}' and '{endtime}'")
succ_num = cursor.fetchall()[0][0]
cursor.execute(f"SELECT count(*) FROM `data`.`table` where branch_name like lower(trim(\"%-{name[0]}-%\")) AND status=2 AND create_time between '{starttime}' and '{endtime}'")
fail_num = cursor.fetchall()[0][0]
if fail_num !=0 and succ_num !=0:
fail_rate = "%.2f%%" % (fail_num / (succ_num + fail_num) * 100)
else:
fail_rate = 0
res1 = [date,name[0], succ_num, fail_num, fail_rate, succ_NUM, fail_NUM, fail_RATE]
res.append(res1)
return res

# 關閉資料庫連接
db.close()

# 判斷檔案是否已存在
def if_file_exist(self):
if os.path.exists(self.file + "\資料展示.xls"):
os.remove(self.file + "\資料展示.xls")

# 生成excel
def write_excel(self, res):
book = xlwt.Workbook() # 新建一個excel
sheet = book.add_sheet('匯出資料') # 創建sheet
title = ['統計區間', '版本','個人資訊成功', '個人資訊失敗', '個人資訊有效攔截率', '主線資訊通過','主線資訊失敗','主線資訊有效攔截率', '問題分析'] # 寫表頭

# 回圈將title寫入excel表頭
i = 0
for header in title:
sheet.write(0, i, header)
i += 1

# 將資料寫入Excel
for row in range(1, len(res) + 1):
for col in range(0, len(res[row - 1])):
sheet.write(row, col, res[row - 1][col])
col += 1
row += 1
book.save(self.file + "\資料展示.xls")
print("匯出成功")

def dates(self,mounth,day,n=7,abj=0):
for i in range(1,n+1):
if abj != 0:
the_date = datetime.datetime(2021, mounth, day) # 指定當前日期 2018-11-10
the_date = the_date - datetime.timedelta(days=i)
date = the_date.strftime('%Y-%m-%d') # 將日期轉換為指定的顯示格式
else:
date = time.strftime('%Y-%m-%d', time.localtime(time.time() - 86400 * i))
yield date

def writefile(self):
n=7 #統計天數，默認為7天
mounth = 12 #統計日期
day = 12 #統計日期
abj= 0 #若值為0，則使用當前日期統計；若值為非0，則使用變數日期統計；默認為當前日期為準
self.if_file_exist()
res = self.datacommon(mounth,day,n,abj)
self.write_excel(res)

a=writefile()
a.writefile()