由于导出的数据量太大 预估可能需要百万行,用工具导几w行已是瓶颈,所以用sql查出数据 写入文本,再用Python写入excel。
select path ,size,createtime
into outfile 'alldata.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' from pusher_2_days where createtime > '2016-01-18 13:00:00' and createtime<='2020-01-18 17:30:00';
把csv文档down到本机 写程序处理 需要先安装openpyxl包
import os
import openpyxl
def read_txt(filename,i):
with open(filename,'r',encoding = 'utf-8') as f:
for line in f.readlines():
i=i+1
write_excel(line,i)
def write_excel(line,row):
#outwb = openpyxl.Workbook()
#outws = outwb.create_sheet(index=0)
#print(line.split(','))
j = 0
for col in line.split(','):
replace_col = col.replace('"','')
#print(col)
j=j+1
outws.cell(row=row, column = j).value = replace_col
if __name__ == '__main__':
filelist = ['']
#filelist = ['201911.csv',]
csvfiledir = r'd:\work\2020'
for fname in filelist:
i=0
csvfile = os.path.join(csvfiledir,fname)
print(csvfile)
outwb = openpyxl.Workbook()
outws = outwb.create_sheet(index=0)
read_txt(csvfile,i)
savefile = os.path.splitext(fname)[0]+'.xlsx'
print(savefile)
saveexcel = os.path.join(csvfiledir,savefile)
outwb.save(saveexcel)