sql查询结果写到excel中

import os
import xlwt
import pymysql
import datetime
from do_config import cfg


class MysqlToExcel:
    def __init__(self,file_name,sql):


        self.host = cfg.get_values('db','host')
        self.user = cfg.get_values('db','user')
        self.passwd = cfg.get_values('db','password')
        self.db_name = cfg.get_values('db','database')
        self.port = cfg.get_int('db','port')
        self.file_name = file_name
        self.sql=sql

    def get_query_results(self):
        # 导出项目基础信息

        # sql="""select distinct(b.project_num),b.project_name,b.project_type,b.pm_id  from gs_task_staff a,gs_projectinfo b where a.project_id=b.project_id and b.project_num like '6%' """
        # sql="""select a.project_id,b.project_num,b.project_name,a.pts_id,a.task_id,a.staff_id,b.pm_id  from gs_task_staff a,gs_projectinfo b where a.project_id=b.project_id and b.project_num like '6%' order by project_id asc;"""
        # sql="""select a.project_id,b.project_num,b.project_name,a.pts_id,a.task_id,a.staff_id,c.staff_name,b.pm_id  from gs_task_staff a,gs_projectinfo b ,gs_staff c where a.project_id=b.project_id and b.project_num like '6%' and a.staff_id=c.user_id order by project_id asc;"""
        # sql="""select project_id,project_num,project_name,pm_id from gs_projectinfo where project_num like '6%'"""
        conn = pymysql.connect(
            host=self.host,
            user=self.user,
            passwd=self.passwd,
            port=self.port,
            database=self.db_name,
            charset='utf8',
            cursorclass=pymysql.cursors.DictCursor
        )
        cur = conn.cursor()  # 创建游标
        cur.execute(self.sql)  # 执行sql命令
        result = cur.fetchall()
        # 获取执行的返回结果

        # print(result)
        cur.close()
        conn.close()  # 关闭mysql 连接
        return result

    def get_maxlength(self,value, col):
        """
        获取value最大占位长度，用于确定导出的xlsx文件的列宽
        col : 表头，也参与比较，解决有时候表头过长的问题
        """
        # 长度列表
        len_list = []
        # 表头长度
        width = 256 * (len(col) + 1)
        len_list.append(width)

        # 数据长度
        if len(value) >= 10:
            width = 256 * (len(value) + 1)
            len_list.append(width)

        return max(len_list)

    def generate_table(self):
            """
            生成excel表格
            :return:
            """
            # 删除已存在的文件
            if os.path.exists(self.file_name):
                os.remove(self.file_name)

            result = self.get_query_results()
            # print(result)
            if not result:
                print("查询结果为空")
                return False

            # 创建excel对象
            f = xlwt.Workbook()
            sheet1 = f.add_sheet('Sheet1', cell_overwrite_ok=True)

            # 第一行结果
            row0 = result[0]
            # 列字段
            column_names = list(result[0].keys())

            # 写第一行，也就是列所在的行
            for i in range(0, len(row0)):
                sheet1.write(0, i, column_names[i])

            # 写入多行
            # 行坐标，从第2行开始，也是1
            for row_id in range(1, len(result) + 1):
                # 列坐标
                for col_id in range(len(column_names)):
                    # 写入的值
                    value = result[row_id - 1][column_names[col_id]]
                    # 判断为日期时
                    if isinstance(value, datetime.datetime):
                        value = result[row_id - 1][column_names[col_id]].strftime('%Y-%m-%d %H:%M:%S')

                    # 写入表格
                    sheet1.write(row_id, col_id, value)

            # 保存文件
            f.save(self.file_name)

            # 判断文件是否存在
            if not os.path.exists(self.file_name):
                print("生成excel失败")
                return False

            print("生成excel成功")
            return True

if __name__ == '__main__':
    file_name='staff_user_basic.xlsx'
    # sql="""select project_id,project_num,project_name,pm_id from gs_projectinfo where project_num like '6%'"""
    sql="""select staff_id,staff_name,staff_job_num,user_id,parent_id,staff_phone,staff_desc from gs_staff;"""
    excel=MysqlToExcel(file_name,sql)
    excel.generate_table()