欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

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

程序员文章站 2022-03-10 16:10:16
...
#******************************接口调用部分*************************************
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