从数据库写入数据到excel

从数据库写入数据到excel

"""
example04 - 将二维表的数据导出到Excel/CSV文件

Author: Hao
Date: 2022/9/2
"""
import csv

import openpyxl
import pymysql

from pymysql.cursors import Cursor

file = open('emp.csv', 'w', encoding='utf-8')
writer = csv.writer(file)
writer.writerow(('eno', 'ename', 'job', 'mname', 'sal', 'comm', 'dname'))
# 创建Excel工作簿对象
wb = openpyxl.Workbook()
# 获取默认的工作表
sheet = wb.active
sheet.title = '员工信息'
# 设置表头
sheet.append(('编号', '姓名', '职位', '主管姓名', '月薪', '补贴', '部门名称'))
conn = pymysql.connect(host='localhost', port=3306,
                       user='guest', password='Guest.618',
                       database='hrs', charset='utf8mb4')
try:
    with conn.cursor() as cursor:  # type: Cursor
        cursor.execute(
            'select '
            '   t1.eno, t1.ename, t1.job, t2.ename, t1.sal, t1.comm, dname '
            'from '
            '   tb_emp as t1 left join tb_emp as t2 on t1.mgr = t2.eno '
            '   inner join tb_dept as t3 on t1.dno = t3.dno'
        )
        while row := cursor.fetchone():
            writer.writerow(row)
            sheet.append(row)
except pymysql.MySQLError as err:
    print(err)
finally:
    conn.close()
    file.close()
    wb.save('hr.xlsx')