#!/usr/bin/env python#-*- coding: utf-8 -*-
'''@Time : 2020/1/1 18:08
@Author : Jason.Jia
@contact: jiajunp@163.com
@Version : 1.0
@file :mysql_write_excel.py
@desc :
从mysql读取数据,写入excel中'''
importpymysql,xlwtdefexport_excel(table_name):
conn= pymysql.connect(user='root',host='127.0.0.1',port=3306,passwd='root',db='python',charset='utf8')
cur=conn.cursor()
sql= 'select * from %s;' %table_name#读取数据
cur.execute(sql)
fileds= [filed[0] for filed incur.description]
all_date= cur.fetchall() #所有数据
for result inall_date:print(result)#写excel
book= xlwt.Workbook() #创建一个book
sheet= book.add_sheet('result') #创建一个sheet表
for col,filed inenumerate(fileds):
sheet.write(0,col,filed)#从第一行开始写
row= 1
for data inall_date:for col,filed inenumerate(data):
sheet.write(row,col,filed)
row+= 1book.save('%s.xls' %table_name)if __name__ == '__main__':
export_excel('stocks')