python导出mysql数据_Python 导出mysql数据到excel

环境  ： 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)

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)