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

最新推荐文章于 2021-08-06 17:16:10 发布

原创最新推荐文章于 2021-08-06 17:16:10 发布 · 200 阅读

0 ·

CC 4.0 BY-SA版权

Python 专栏收录该内容

8 篇文章

订阅专栏

该博客讲述了如何通过SQL查询从数据库中导出大量数据到CSV文件，然后利用Python的openpyxl库将这些数据写入Excel工作簿。作者首先执行SQL语句选择指定时间范围内的数据，并将结果保存为CSV。接着，通过Python读取CSV文件，逐行处理数据并移除双引号，最后将内容写入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)