用Python读取数据库写进EXCEL

数据库可以select出数据，但是进行数据操作就不行了，EXCEL是一款很强大的软件，简单的数据统计或者数据分析都可以进行，所以下面说一下从mysql中读取数据，写进EXCEL。

Python : 3.3

packages : mysql.connector openpyxl

EXCEL : 2007

MySQL : 5.6

import mysql.connector as mc
from openpyxl.writer.excel import ExcelWriter
from openpyxl.workbook import Workbook
# it's different between workbook and worksheet

conn = mc.connect(user='root', password='password', host='127.0.0.1', database='callcenter')
cur = conn.cursor()

# query language
sql = "select * from trafficin where enter_time>'2014-12-31' and ((queue!=0040 and queue is not null) or status='系统未接听-黑名单')"
data = cur.execute(sql)

# use fetchall method to get a list, some tuples in it
data_list = cur.fetchall()
cur.close()
conn.close()


wb = Workbook()
ws = wb.worksheets[0]
ws.title = 'data'

ew = ExcelWriter(workbook = wb)

file_name = r'12.31_Traffic.xlsx'
rows = len(data_list)
cols = len(data_list[0])

# same as read data
for rx in range(rows):
    for cx in range(cols):
        ws.cell(row=rx+1, column=cx+1).value = data_list[rx][cx]

# use ExcelWriter method to write file and save it
ew.save(filename=file_name)

遇到故障查看一下源码或者help() ， dir()  获得帮助，即是看不大懂，依然会有帮助。