python从数据库取数据保存为excel_python读取数据库表数据并写入excel

#!/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')