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()