python连接数据库后读取数据并将数据写入到excel表中的方法
"""
读取数据写入Excel文件
一个Excel文件 ---> 工作簿 ---> workbook
一个工作簿下面可以有一个或多个工作表 ---> sheet
"""
import MySQLdb
import xlwt
conn = MySQLdb.connect(host='47.104.31.108', port=3306,
user='root', password='Lhhv.618',
database='hrs', charset='utf8')
try:
with conn.cursor() as cursor:
cursor.execute(
'select eno, ename, job, sal, dname from tb_emp t1 '
'inner join tb_dept t2 on t1.dno=t2.dno'
)
# 创建工作簿对象
wb = xlwt.Workbook()
# 创建工作表对象
sheet = wb.add_sheet('员工信息表')
# 表头内容
titles = ('工号', '姓名', '职位', '月薪', '部门')
title_style = xlwt.XFStyle()
font = xlwt.Font()
font.name = '行楷-简'
font.height = 400
font.colour_index = 4
font.bold = True
title_style.font = font
aligment = xlwt.Alignment()
aligment.horz = xlwt.Alignment.HORZ_CENTER
aligment.vert = xlwt.Alignment.VERT_CENTER
title_style.alignment = aligment
# 添加表头内容到表中
for col_index, title in enumerate(titles):
#这里第四个参数title_style是样式设置,可以不加
sheet.write(0, col_index, title, title_style)
# row是元组,将数据库中内容写入表中
for row_index, row in enumerate(cursor.fetchall()):
for col_index, value in enumerate(row):
sheet.write(row_index + 1, col_index, value)
# 保存excel表,文件后缀为.xls
wb.save('hrs.xls')
except MySQLdb.MySQLError as err:
print(err)
conn.rollback()
finally:
conn.close()