一、导入相关的包
import pymysql
import xlsxwriter
import time
二、创建excel并连接数据库
#创建excel表
now_time = time.strftime("%Y_%m_%d_%H")
persons_excel = xlsxwriter.Workbook(r"./report/"+ now_time + "persondata.xlsx")
sheet = persons_excel.add_worksheet("sheet")
#连接mysql
db = pymysql.connect("localhost","root","123456","test")
cursor = db.cursor()
sql = "select * from persons"
rows = cursor.execute(sql)
alldata = cursor.fetchall()#展示表中所有数据
row = len(alldata)#获取表的行数
三、代码部分
1、先获取表头,写入excel第1行
header = cursor.description
table_header = []
for i in header:
table_header.append(i[0])
sheet.write_row(0,0,table_header)#把表头写进去
2、写入数据的函数,按行写入
k = 0
def write_data(data):
global k
k = k + 1
sheet.write_row(k,0,data)
3、调用函数
for i in alldata:
write_data(i)
4、关闭excel,必须有关闭这一步,否则excel无法保存
persons_excel.close()
ps:初学相关技术,有不对之处欢迎纠正!