python导出mysql_python 实现从mysql 导出为csv

# !/usr/bin/env python

# -*- coding: utf-8 -*-

import tornado.ioloop

import tornado.web

import mysql.connector

import time

import csv

import sys

reload(sys)

sys.setdefaultencoding('utf-8')

filename = "/home/py/static/item.csv"

class DownloadCSVHandler(tornado.web.RequestHandler):

def get(self):

setFileName()

exportItem()

self.set_header("Content-Type","text/csv,charset=UTF-8")

buf_size = 1000

with open(filename, 'rb') as f:

while True:

data = f.read(buf_size)

if not data:

break

self.write(data)

self.finish()

application = tornado.web.Application([

(r"/download/item.csv", DownloadCSVHandler)

def setFileName():

global filename

filename = "/home/py/static/item" + time.strftime('%Y%m%d%H%M') + ".csv"

def exportItem():

config={'host':'host',

'user':'root',

'password':'pwd',

'port':3306 ,

'database':'product',

'charset':'utf8'

}

result = []

try:

conn = mysql.connector.connect(**config)

cur = conn.cursor()

cur.execute('select id,unit,name,barcode,price from item;')

for res in cur:

result.append(res)

except mysql.connector.Error, e:

print "Mysql Error %d: %s" % (e.args[0], e.args[1])

finally:

cur.close()

conn.close()

if(len(result) >= 0):

global filename

with open(filename, 'wb') as csvfile:

spamwriter = csv.writer(csvfile)

#, delimiter=' ', quotechar=' ', quoting=csv.QUOTE_MINIMAL)

for res in result:

spamwriter.writerow(res)

if __name__ == "__main__":

application.listen(8866)

tornado.ioloop.IOLoop.instance().start()

其实还可以考虑，通过mysqldump命令，远程导出数据库，应该可以实现，后面再尝试。