用Python将mysql数据导出成excel

1、导出示例
/Users/nisj/PycharmProjects/BiDataProc/oldPythonBak/mysqlData2excel.py

# -*- coding=utf-8 -*-import xlwtimport MySQLdbimport warningsimport datetimeimport timeimport sysreload(sys)sys.setdefaultencoding('utf8')warnings.filterwarnings("ignore")db_config = {    'host': 'MysqlHostOuterIp',    'user': 'MysqlUser',    'passwd': 'MysqlPass',    'port': 50512,    'db': 'Tv_event'}def getDB():    try:        conn = MySQLdb.connect(host=db_config['host'], user=db_config['user'], passwd=db_config['passwd'],                               port=db_config['port'])        conn.autocommit(True)        curr = conn.cursor()        curr.execute("SET NAMES utf8");        curr.execute("USE %s" % db_config['db']);        return conn, curr    except MySQLdb.Error, e:        print "Mysql Error %d: %s" % (e.args[0], e.args[1])        return None, Noneconn, curr = getDB()today = datetime.date.today()yesterday = today - datetime.timedelta(days=1)tomorrow = today + datetime.timedelta(days=1)sql_text = "SELECT uid,name,phone_num,qq,area,created_time FROM match_apply where match_id = 83 order by created_time desc;"curr.execute(sql_text)datas = curr.fetchall()fields = curr.descriptionworkbook = xlwt.Workbook()sheet = workbook.add_sheet('tableSheet_message', cell_overwrite_ok=True)# 写上字段信息for field in range(0, len(fields)):    sheet.write(0, field, fields[field][0])# 获取并写入数据段信息row = 1col = 0for row in range(1, len(datas)+1):    for col in range(0, len(fields)):        sheet.write(row, col, u'%s' % datas[row-1][col])workbook.save(r'/Users/nisj/Desktop/mysqlDataDownload.xls')curr.close()conn.close()print time.strftime('%Y-%m-%d %H:%M:%S',time.localtime(time.time())),"mysqlData to excel Finished!"

2、将导出函数各部分进行封装
/Users/nisj/PycharmProjects/BiDataProc/oldPythonBak/mysqlData2excel(Encapsulation).py

# -*- coding=utf-8 -*-import xlwtimport MySQLdbimport warningsimport datetimeimport sysreload(sys)sys.setdefaultencoding('utf8')warnings.filterwarnings("ignore")mysqlDb_config = {    'host': 'MysqlHostOuterIp',    'user': 'MysqlUser',    'passwd': 'MysqlPass',    'port': 50512,    'db': 'Tv_event'}today = datetime.date.today()yesterday = today - datetime.timedelta(days=1)tomorrow = today + datetime.timedelta(days=1)def getDB(dbConfigName):    dbConfig = eval(dbConfigName)    try:        conn = MySQLdb.connect(host=dbConfig['host'], user=dbConfig['user'], passwd=dbConfig['passwd'],                               port=dbConfig['port'])        conn.autocommit(True)        curr = conn.cursor()        curr.execute("SET NAMES utf8");        curr.execute("USE %s" % dbConfig['db']);        return conn, curr    except MySQLdb.Error, e:        print "Mysql Error %d: %s" % (e.args[0], e.args[1])        return None, Nonedef mysqlData2excel(dbConfigName, selectSql, exportPath, exportName):    # 边框的定义    borders = xlwt.Borders()    borders.left = 1    borders.right = 1    borders.top = 1    borders.bottom = 1    borders.bottom_colour = 0x3A    # Initialize a style for frist row    style_fristRow = xlwt.XFStyle()    font = xlwt.Font()    font.name = 'Times New Roman'    font.bold = True    font.colour_index = 1    style_fristRow.font = font    badBG = xlwt.Pattern()    badBG.pattern = badBG.SOLID_PATTERN    badBG.pattern_fore_colour = 6    style_fristRow.pattern = badBG    style_fristRow.borders = borders    # Initialize a style for data row    style_dataRow = xlwt.XFStyle()    font = xlwt.Font()    font.name = u'隶变-简 常规体'    font.bold = False    style_dataRow.font = font    style_dataRow.borders = borders    conn, curr = getDB(dbConfigName)    curr.execute(selectSql)    datas = curr.fetchall()    fields = curr.description    workbook = xlwt.Workbook()    sheet = workbook.add_sheet('tableSheet_message', cell_overwrite_ok=True)    # 写上字段信息    for field in range(0, len(fields)):        sheet.write(0, field, fields[field][0], style_fristRow)    # 获取并写入数据段信息    row = 1    col = 0    for row in range(1, len(datas)+1):        for col in range(0, len(fields)):            sheet.write(row, col, u'%s' % datas[row-1][col], style_dataRow)    workbook.save(r'{exportPath}/{exportName}.xls'.format(exportPath=exportPath, exportName=exportName))    curr.close()    conn.close()# Batch TestdbConfigName = 'mysqlDb_config'selectSql = "SELECT uid,name,phone_num,qq,area,created_time FROM match_apply where match_id = 83 order by created_time desc;"exportPath = '/Users/nisj/Desktop/'exportName = 'mysqlDataDownload'mysqlData2excel(dbConfigName, selectSql, exportPath, exportName)