将数据库数据写入到xls文件

# -*- coding: utf-8 -*-
"""
Created on Thu Feb 28 14:26:07 2019

@author: Admin
"""

import pymysql
import xlwt
import datetime

host="127.0.0.1"
port=3306
passwd=""
db="db"
user="root"

def Write_file(table):
    conn = pymysql.connect(host=host,port=port,passwd=passwd,db=db,user=user)
    cur = conn.cursor()

    sql = "select * from " + table
    cur.execute(sql)

#    获取数据库数据
    data = cur.fetchall()

#    获取数据库字段名和属性
    fileds = cur.description
    cur.close()
    conn.close()
    row_3 = []  #字段名
    for i in data:
        row_3.append(list(i))

    ff = []
    for filed in fileds:
        ff.append(filed[0])

    #实例化一个对象
    workbook = xlwt.Workbook(encoding='utf-8')

    #创建Excel中的一个sheet，并命名且为可重写状态。
    sheet = workbook.add_sheet('count',cell_overwrite_ok=True)

    #将数据库查询的字段名写入第一行
    for field in range(0,len(ff)):
        sheet.write(0,field,ff[field])
        
    #将数据库的数据写入xls文件
    for row in range(1,len(row_3)+1):
        for col in range(0,len(row_3[row-1])):
            sheet.write(row,col,u'%s'%row_3[row-1][col])

    #将Excel文件保存下来
    workbook.save(table+'.xls')
 
    
table = "herfs_table"   #数据库表名
Write_file(table)