环境 : win7 64 位+ python 3.4
需要俩个库
# 创建兼容微软 Excel 97/2000/XP/2003的电子表格(.XLS)文件
xlwt
# mysql 接口
MySQL-python/MySQLdb
安装(解压文件,进入下载目录)
python setup.py install
若没有安装Visual Studio,会出现以下错误:
error: Microsoft Visual C++ 10.0 is required (Unable to find vcvarsall.bat)
不打算安装的话 下载以下两个文件(http://www.lfd.uci.edu/~gohlke/pythonlibs/)
mysqlclient-1.3.9-cp34-cp34m-win_amd64#python 3.4 ,win 64位
xlwt-1.1.2-py2.py3-none-any.whl
安装
python -m pip install "xlwt-1.1.2-py2.py3-none-any.whl"
python -m pip install "mysqlclient-1.3.9-cp34-cp34m-win_amd64"
导出mysql数据到excel脚本:
# coding:utf8
import sys
import xlwt
import MySQLdb
import datetime
host = 'localhost'
user = 'root'
pwd = 'password'
db = 'db'
sql = 'select * from tables'
sheet_name = 'city'
out_path = 'C:/Users/huangzc/Desktop/'+datetime.datetime.now().strftime('%Y%m%d')+'.xls'
conn = MySQLdb.connect(host,user,pwd,db,charset='utf8')
cursor = conn.cursor()
count = cursor.execute(sql)
print(count)
cursor.scroll(0,mode='absolute')
results = cursor.fetchall()
fields = cursor.description
workbook = xlwt.Workbook()
sheet = workbook.add_sheet(sheet_name,cell_overwrite_ok=True)
for field in range(0,len(fields)):
sheet.write(0,field,fields[field][0])
row = 1
col = 0
for row in range(1,len(results)+1):
for col in range(0,len(fields)):
sheet.write(row,col,u'%s'%results[row-1][col])
workbook.save(out_path)
参考:Python实现数据库一键导出为Excel表格