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

[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")