python 访问数据库，将其数据写入excel

#!/usr/bin/env python
# -*- coding: utf-8 -*-
import MySQLdb as mdb
import MySQLdb.cursors


# 使用关键字参数
conn = mdb.connect(host='localhost', port=3306, user='root', passwd='*****', db='database_name', charset='utf8',cursorclass=MySQLdb.cursors.DictCursor)

# 如果使用事务引擎，可以设置自动提交事务，或者在每次操作完成后手动提交事务conn.commit()
conn.autocommit(1)    # conn.autocommit(True)

# 使用cursor()方法获取操作游标
cursor = conn.cursor()
# 因该模块底层其实是调用CAPI的，所以，需要先得到当前指向数据库的指针。

try:
    cursor.execute('SELECT * FROM product_response')
    re = cursor.fetchall()
    #re 是元组类型
    import numpy as np
    import xlwt
    book = xlwt.Workbook()
    # 创建表单
    sheet1 = book.add_sheet(u'sheet1', cell_overwrite_ok=True)
    # 按i行j列顺序依次存入表格

    sheet1.write(0, 0, 'id')
    sheet1.write(0, 1,'post_list_id')
    sheet1.write(0, 2, 'is_effect')
    sheet1.write(0, 3, 'product')
    sheet1.write(0, 4, 'fst_commit_time')
    sheet1.write(0, 5, 'expect_time')
    sheet1.write(0, 6, 'actual_time')
    sheet1.write(0, 7, 'version')
    sheet1.write(0, 8, 'last_update_time')
    sheet1.write(0, 9, 'submiter')
    sheet1.write(0, 10, 'submiter_display')
    sheet1.write(0, 11, 'submiter_property')
    sheet1.write(0, 12, 'comments')
    sheet1.write(0, 13, 'rule')
    for i in range(len(re)):
        sheet1.write(i + 1, 0, re[i]['id'])
        sheet1.write(i + 1, 1, re[i]['post_list_id'])
        sheet1.write(i + 1, 2, re[i]['is_effect'])
        sheet1.write(i + 1, 3, re[i]['product'])
        sheet1.write(i + 1, 4, str(re[i]['fst_commit_time']))
        sheet1.write(i + 1, 5, str(re[i]['expect_time']))
        sheet1.write(i + 1, 6, str(re[i]['actual_time']))
        sheet1.write(i + 1, 7, re[i]['version'])
        sheet1.write(i + 1, 8, str(re[i]['last_update_time']))
        sheet1.write(i + 1, 9, re[i]['submiter'])
        sheet1.write(i + 1, 10, re[i]['submiter_display'])
        sheet1.write(i + 1, 11, re[i]['submiter_property'])
        sheet1.write(i + 1, 12, re[i]['comments'])
        sheet1.write(i + 1, 13, re[i]['rule'])
            # 保存文件
    book.save('product.xls')
    conn.commit()
except:
    import traceback
    traceback.print_exc()
    # 发生错误时会滚
    conn.rollback()
finally:
    # 关闭游标连接
    cursor.close()
    # 关闭数据库连接
    conn.close()　

mysqldb 安装容易出现各种各样的问题，此方案验证有效

百度教程说安装

pip install mysqldb

这在我的电脑上安装失败：

  Could not find a version that satisfies the requirement mysqldb (from versions
: )
No matching distribution found for mysqldb

所以应该：

pip install MySQL-python

报错：

error: Microsoft Visual C++ 9.0 is required (Unable to find vcvarsall.bat).
Get it from http://aka.ms/vcpython27

缺少vc的一个库，我们根据提示到这里下载一下，然后安装，再次执行命令 ：pip install mysql-python

还是报错：

No such file or directory
    error: command '"C:\Users\年浩\AppData\Local\Programs\Common\Microsoft\Visua
l C++ for Python\9.0\VC\Bin\amd64\cl.exe"' failed with exit status 2

这是因为缺少驱动。

MySQL-python-1.2.3.win-amd64-py2.7.exe

给两个下载地址：http://download.csdn.net/detail/weibin0320/6663763

或者：http://www.jb51.net/softs/73369.html#download

安装后再次执行install 命令。ok,没有报错。我们测试一下。

import MySQLdb

没有报错，安装成功。。