mysql导出数据写入文本转成excel文档

由于导出的数据量太大 预估可能需要百万行，用工具导几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)