python3实现查询mysql生成excel并下载

#******************************接口调用部分*************************************
def post(self, *args):
  sql = "select * from yongyao_lv"
  head = ["ICD10","名称","年份","地区","产品名","新增人数","现有人数","增长率"]
  line = ["code_category","category_name","y","addr","n_item_name","num_yongyao","num_huanbin","yongyaobi"]
  output = export_excel(sql,head,line)
  nowtime = datetime.datetime.now().strftime('%Y%m%d%H%M%S')
  self.set_header("Content-Type", "application/octet-stream")
  self.set_header("Content-Disposition", "attachment; filename=yongyaolv_%s.xls" % nowtime)
  self.write(output.getvalue())

#**********************表格处理部分**************************


def export_excel(sql,head,line):
    list_obj = connect_datebase(sql)
    """导出excel表"""
    if list_obj:
        # 创建工作簿
        ws = xlwt.Workbook(encoding='utf-8')
        w = ws.add_sheet('sheet1')
        for h in range(0, len(head)):
            w.write(0, h, str(head[h]))
        excel_row = 1
        for obj in list_obj:
            for j in range(0, len(line)):
                line_name=line[j]
                w.write(excel_row, j, obj.get(line_name))
            excel_row += 1
        output = BytesIO()
        ws.save(output)
        output.seek(0)
        return output

#***********************数据库查询部分*************************

def connect_datebase(sql):
    try:
        conn = mysqldb.connect(host="127.0.0.1", port=6379, user="root",
                               passwd="123456",
                               db="data", charset="utf8")
        cursor = conn.cursor()
        n = cursor.execute(sql)
        rows = []
        col = []
        cols = cursor.description
        for c in cols:
            col.append(c[0])
        for em in cursor.fetchall():
            r = {}
            for i in range(0, len(col)):
                key = str(col[i])
                value = str(em[i])
                r[key] = value
            # row.append(r)
            rows.append(r)
    except Exception as e:
        print(e)
    return rows