MYSQL海量数据导出到EXCEL

本文主要是介绍MYSQL海量数据导出到EXCEL，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

import pymysql
import openpyxl
import time
def export_to_excel(worksheet, cursor, table):
    """
    将MySQL一个数据表导出到excel文件的一个表的函数
    :param    worksheet:  准备写入的excel表
    :param    cursor:     源数据的数据库游标
    :param    table       源数据的数据表
    :return:  Nove.
    """
    # 首先向excel表中写入数据表的字段
    column_count = cursor.execute("desc %s"%table)
    for i in range(column_count):
       temptuple = cursor.fetchone()
       #print(temptuple[0])
       worksheet.cell(1, i+1, temptuple[0])

    # 向构建好字段的excel表写入所有的数据记录
    row_count = cursor.execute("select * from %s"%table)
    for i in range(row_count):
        temptuple = cursor.fetchone()
        for j in range(column_count):
            worksheet.cell(i + 1, j+1, temptuple[j])


#workbook = xlwt.Workbook()
workbook = openpyxl.Workbook()
worksheet = workbook.create_sheet("sheet1",0)
localtime = time.localtime(time.time())#获取当前时间
time = time.strftime("%Y%m%d%H%M%S",time.localtime(time.time()))
connect = pymysql.connect(
             user = '',
            password = '',
            db = '',
            host = '',
            port = 3306,
            charset = 'utf8'
            )
cursor = connect.cursor()

export_to_excel(worksheet, cursor, 'xinxi')

cursor.close()
connect.close()

workbook.save(time+".xls")

这篇关于MYSQL海量数据导出到EXCEL的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！