基于xlsxwriter模块，MySQL导出xlsx文件，自动格式化会存在科学计数法的数字
1.大数据自动分割文件(xlsx一张表只能存100W数据)2.自动格式化会存在可续计数法的数字
注意：该方法如果导出大数据的情况下回占用大量的内存，如果是安装的32位python，占用的内存超过2G会报异常，跟换64位python即可解决
同时欢迎大家指教，xlsxwriter模块模块是我测过很多模块中导出速度最快的一个模块了，如果有发现更快的方法，一定要告诉我{:1_893:}
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 = 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 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 in (7, 11, 12):
ws.set_column(col_, col_, 20, DateTime_bold)# yyyy-mm-dd hh:mm:ss
elif Field_Name_Attr == 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")
学习了学习了
自己研究研究 学习学习，谢谢分享 收藏了，学习下，感谢分享
页:
[1]