Python 查询数据库(mysql)导入excel

【openpyxl】+【pandas】生成.xlsx

import pandas as pd

from sqlalchemy import create_engine

# 数据库连接对象
engine = create_engine("mysql+pymysql://root:123456@localhost:3306/scoresys")
# sql语句
sql = "SELECT id '学号',name '姓名',chinese '语文',math '数学',english '英语',science '科学',sports '体育',art '艺术' FROM student"
df = pd.read_sql(sql, engine)
df.to_excel(r'E:\学生成绩表.xlsx', index=False)

【xlwt】生成.xlsx

import pymysql
import xlwt

# 数据库连接对象
conn = pymysql.connect(host='localhost', port=3306, user='root', password="123456", db="scoresys")
# 游标对象
cur = conn.cursor()
# sql语句
sql = "SELECT id '学号',name '姓名',chinese '语文',math '数学',english '英语',science '科学',sports '体育',art '艺术' FROM student"
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"E:\学生成绩表.xls")

【openpyxl】生成.xlsx

import pymysql
import openpyxl

conn = pymysql.connect(host='localhost', port=3306, user='root', password="123456", db="scoresys")
sql = "SELECT id '学号',name '姓名',chinese '语文',math '数学',english '英语',science '科学',sports '体育',art '艺术' FROM student"
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"E:\学生成绩表.xls")