基于xlsxwriter模块，MySQL导出xlsx文件，自动格式化会存在科学计数法的数字

禁之零零

1.大数据自动分割文件(xlsx一张表只能存100W数据)
2.自动格式化会存在可续计数法的数字
注意：该方法如果导出大数据的情况下回占用大量的内存，如果是安装的32位python，占用的内存超过2G会报异常，跟换64位python即可解决
同时欢迎大家指教，xlsxwriter模块模块是我测过很多模块中导出速度最快的一个模块了，如果有发现更快的方法，一定要告诉我

[Python] 纯文本查看 复制代码

import pymysql
import datetime
import xlsxwriter


def Get_Conn_Config(Data_Key):
    "获取数据库配置信息"
    Data_Config = {
        "host": "",
        "user": "",
        "password": "",
        "database": "",
        'port': 3306,
        'charset': 'utf8'
    }

    if Data_Key == "1":
        Data_Config["host"] = "127.0.0.1"
        Data_Config["user"] = "root"
        Data_Config["password"] = "password"
        Data_Config["database"] = "database"
    elif Data_Key == "2":
        Data_Config["host"] = "127.0.0.2"
        Data_Config["user"] = "root"
        Data_Config["password"] = "password"
        Data_Config["database"] = "yiqixiu"
    return Data_Config


def Export_MySQL_To_Excel(Export_File, SQL, MySQL_Conn_Config, DataBase=None, Out_Name=None):
    "导出数据库"
    try:
        db = pymysql.connect(**MySQL_Conn_Config)  # 获取mysql句柄
    except Exception as e:
        print("MySQL Connection Error!" + e)
        return
    cursor = db.cursor()  # 获取游标
    if Out_Name is None:
        file_path = input("请输入文件名(勿输入后缀名):")
    else:
        file_path = Out_Name
    print("查询中...")
    Start_time = datetime.datetime.now()
    try:
        if DataBase is not None: cursor.execute("use `%s`" % DataBase)
        cursor.execute(SQL)  # 执行SQL语句
    except Exception as e:
        print(e)
        return
    End_Time = datetime.datetime.now()
    print('耗时:%ds' % ((End_Time - Start_time).seconds))
    Row_Count = cursor.rowcount  # 取总行数
    Field_Name_Attr = cursor.description  # 取字段名
    Field_Name = [list[0] for list in Field_Name_Attr]
    print(Field_Name)  # 取字段名二维数组的第一列
    # print(Field_Name_Attr)

    Start_time = datetime.datetime.now()  # 取任务开始时间

    Separate_Count = 1000000  # 100W条数据自动分隔文件
    Is_Separate = False
    if Row_Count > Separate_Count:  # 总行数大于分隔行数，则启动分隔模式
        Is_Separate = True

    Separate = Row_Count // Separate_Count  # 分隔次数
    if Row_Count % Separate_Count != 0:  # 具有余数则分隔次数再加1次
        Separate += 1

    print("结果:%d" % Row_Count)
    print("分段:%d" % Separate)
    Separate += 1  # range函数从1开始的额外计算1次
    for Separate_Number in (range(1, Separate)):
        File_Name = Export_File + "\\" + file_path

        if Is_Separate == False:
            File_Name2 = File_Name + '.xlsx'
            wb = xlsxwriter.Workbook(File_Name2)
        else:
            File_Name2 = File_Name + "_" + str(Separate_Number) + '.xlsx'
            wb = xlsxwriter.Workbook(File_Name2)

        ws = wb.add_worksheet()
        # 标题样式：粗体 背景色 边框 字体颜色
        Title_Style = wb.add_format({'bold': True, 'fg_color': '#336666', 'border': 1, 'color': '#FFFFFF'})
        ws.write_row(0, 0, Field_Name, Title_Style)  # 写入标题

        tem_i = 0
        for Row_Number in range(Row_Count):
            data = cursor.fetchone()  # 读取一行数据
            if not data: break  # 如果没数据则跳出循环
            data2 = []
            col_ = 0
            for var in (data):
                # 格式化会科学计数法的数字型 type:5=double 8=bigint
                if Field_Name_Attr[col_][1] in (5, 8):
                    if var is not None:
                        if var > 9999999999:
                            # 当数字大于一定值才会科学计数法显示，将其格式化
                            data2.append(str(var))
                        else:
                            data2.append(var)
                else:
                    data2.append(var)
                col_ += 1
            ws.write_row(Row_Number + 1, 0, data2)  # 写入一行数据至Excel

            # 奢华的计算进度条
            Complete_Number = Row_Number + 1 + (Separate_Number - 1) * Separate_Count
            percentage = round(Complete_Number / Row_Count * 100)
            if Complete_Number >= Row_Count / 100 * tem_i:
                End_Time = datetime.datetime.now()
                print(
                    '\r任务:' + str(Separate_Number) + '/' + str(Separate - 1) + '[' + '■' * (percentage // 5) + '□' * (
                            20 - percentage // 5) + ']' + str(percentage) + '%,' + "%dS" % (
                        (End_Time - Start_time).seconds), end='')
                tem_i += 1
            # print("任务:%d/%d" % (Separate_Number, Separate - 1))

            if (Row_Number + 1) % Separate_Count == 0:  # 导出指定行数则进行分隔
                break

        DateTime_bold = wb.add_format({'num_format': 'yyyy-mm-dd hh:mm:ss'})
        Date_bold = wb.add_format({'num_format': 'yyyy-mm-dd'})
        Text_bold = wb.add_format({'num_format': '@'})

        # 格式化时间型的数据列 Type:7=Timestamp 10=Date 11=Time 12=DateTime
        for col_ in range(len(Field_Name_Attr)):
            if Field_Name_Attr[col_][1] in (7, 11, 12):
                ws.set_column(col_, col_, 20, DateTime_bold)  # yyyy-mm-dd hh:mm:ss
            elif Field_Name_Attr[col_][1] == 10:
                ws.set_column(col_, col_, 20, Date_bold)  # yyyy-mm-dd

        print()
        End_Time = datetime.datetime.now()
        print("保存中...%s,%ds" % (File_Name2, (End_Time - Start_time).seconds))
        wb.close()

    End_Time = datetime.datetime.now()
    print('耗时:%ds' % ((End_Time - Start_time).seconds))
    cursor.close()  # 关闭游标
    db.close()  # 断开MySQL链接


if __name__ == '__main__':
	#将user表导出
	Export_MySQL_To_Excel("D:\\", "select * from user", Get_Conn_Config("1"), "mysql")

佚丶名

学习了学习了
自己研究研究

coolsnake

学习学习，谢谢分享

刘郝聪明

收藏了，学习下，感谢分享