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