python mysql直接导出excel文件

import pymysql
import xlwt

# 数据库连接对象
conn = pymysql.connect(host='124.71.72.144', port=3306, user='root', password="gClUjjCh2tozZY23o", db="pay",charset='utf8mb4')
# 游标对象
cur = conn.cursor()
# sql语句
sql = 'select account,count(account) as num FROM syt_channel WHERE account in(13821555397,17851213119,13821505398) AND add_time BETWEEN 1672540205 AND 1673663405 GROUP BY account'
cur.execute(sql)
# 获取表格的字段信息
fields = cur.description
# 获取所有数据
rows = cur.fetchall()

# 移动指针到某一行.如果mode='relative',则表示从当前所在行移动value条,如果mode='absolute',则表示从结果集的第一 行移动value条.
cur.scroll(0, mode='absolute')
cur.close()
conn.close()

# 二、初始化excel文件
workbook = xlwt.Workbook(encoding='utf-8', style_compression=0)
style = xlwt.XFStyle()  # 初始化样式
font = xlwt.Font()  # 创建字体样式
font.name = "微软雅黑"
style.font = font  # 把字体添加到样式中
sheet = workbook.add_sheet("用户信息", cell_overwrite_ok=True)

# 三、把数据写入excel
'''创建excel的列名'''
# title = "学号,姓名,语文，数学,英语,科学,体育,艺术"
# title = title.split(",")
# '''#使用循环写入数据'''
# for i in range(len(title)):
#     ws.write(0, i, title[i], style)


# #开始写入数据
# 将表的字段名写入excel
for field in range(len(fields)):
    sheet.write(0, field, fields[field][0], style)

# 结果写入excle
for row in range(1, len(rows) + 1):
    for col in range(len(fields)):
        sheet.write(row, col, rows[row - 1][col])
workbook.save(r"D:\学生成绩表.xls")

 

import pymysql
import openpyxl

conn = pymysql.connect(host='124.71.72.128', port=3306, user='root', password="gClUjjChtozZY2i4o", db="pay")
sql = "select account,count(account) as num FROM syt_channel WHERE account in(13821555397,17851213119,13821505398) AND add_time BETWEEN 1672540205 AND 1673663405 GROUP BY account"
cur = conn.cursor()
cur.execute(sql)
result = cur.fetchall()
cur.close()
conn.close()
# 写 Excel
book = openpyxl.Workbook()
sheet = book.active
# 获取表头信息
h1 = [filed[0] for filed in cur.description]
sheet.append(h1)
for i in result:
    sheet.append(i)
book.save(r"D:\学生成绩表555.xls")