文章目录

1. 1. 目标
2. 2. 准备
3. 3. 导出为CSV格式
4. 4. 导出为Excel格式

目标

有的时候，为了导出一份大的数据文件，或者RDS上没有开放MySQL数据库的访问用户的FILE权限的话，我们需要使用脚本的方式且分页导出数据。 下面介绍一下如何使用Python脚本来做MySQL数据的导出（CSV或Excel格式）。

准备

安装一些必要的依赖等，首先是安装Python的MySQL驱动，要注意的是这个目前已经没人维护了，如果你用的是Python3的话，需要使用MySQL的官方connector。在CentOS6.x下，执行yum install MySQL-python安装是的对应python2.6的版本，如果是用的python2.7的话，我们需要明确指定为MySQL-python27。

yum install python-devel mysql-devel
yum install MySQL-python27
pip install xlsxwriter  # 仅当导Excel格式时需要。

导出为CSV格式

import MySQLdb
import sys
import csv

user = 'root' # your username
passwd = '' # your password
host = '127.0.0.1' # your host
db = 'dbname' # database where your table is stored
table = 'talbe_name' # table you want to save
page_size = 10000
pages = 70
output_file = 'output.csv'

con = MySQLdb.connect(user=user, passwd=passwd, host=host, db=db)
cursor = con.cursor()
c = csv.writer(open(output_file,"wb"))

for i in range(0,pages):
    offset = i * page_size
    rows = (i+1) * page_size
    print rows # print current processing page
    query = "SELECT * FROM %s.%s limit %s,%s;" % (db, table, offset, rows)
    cursor.execute(query)
    result=cursor.fetchall()
    for row in result:
        c.writerow(row)

导出为Excel格式

import MySQLdb
import sys
from xlsxwriter.workbook import Workbook

user = 'root' # your username
passwd = '' # your password
host = '127.0.0.1' # your host
db = 'dbname' # database where your table is stored
table = 'talbe_name' # table you want to save
page_size = 10000
pages = 70
output_file = 'output.csv'

con = MySQLdb.connect(user=user, passwd=passwd, host=host, db=db)
cursor = con.cursor()
workbook = Workbook(output_file)
sheet = workbook.add_worksheet()

for i in range(0,pages):
    offset = i * page_size
    rows = (i+1) * page_size
    print rows # print current processing page
    query = "SELECT * FROM %s.%s limit %s,%s;" % (db, table, offset, rows)
    cursor.execute(query)
    result=cursor.fetchall()

    for r, row in enumerate(result):
        for c, col in enumerate(row):
            sheet.write(r, c, col)

workbook.close()