一种快速导入Excel数据到mysql数据库的办法

基本需求

导入Excel表格中的数据到mysql数据库是一种常见需求。导入的方法有很多。今天介绍一种用python导入excel数据到mysql的方法。它的好处是执行效率高，导入数据量大。

实施步聚

1,通过python 将excel文件转换为sql插入语句组成的文本文件。为了实现这个功能用到了一个读取excel数据的python模块xlrd。
2.命令行上执行 mysql命令，运行第一步生成的sql脚本，将数据导入mysql数据库。

转换excel文件的python代码

# -*- coding:utf-8 -*- 
from xlrd import open_workbook  
import sys
import getopt
import time
reload(sys)
sys.setdefaultencoding( "utf-8" )  

#解析命令行参数，得到excel文件名和数据库表名
try:
	opts, args = getopt.getopt(sys.argv[1:],'f:s:d:',['src=','sheetIdx=','table='])
except getopt.GetoptError:
	print('error:','options invalid')
	sys.exit()

for k, v in opts:
	if k in ("-f", "--src"):
		srcFile = v
	elif k in ("-s", "--sheetIdx"):
		sheetIdx = int(v)
	elif k in ("-d", "--table"):
		tableName = v

startTime = time.time()

#创建表的sql
outsql =open(srcFile+'.sql','w')
outsql.write('DROP TABLE IF EXISTS '+tableName+';' + '\n')
outsql.write('CREATE TABLE IF NOT EXISTS '+tableName+'(`_tmp_id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,'+ '\n')

insertHeader = "insert into "+tableName+"("

#逐行逐列分析excel,生成insert语句并写入sql脚本文件
wb = open_workbook(srcFile)
sheetNum=0
rowCount="0"
for s in wb.sheets():  
    if sheetNum != sheetIdx:
    	continue
    print ('Sheet:',s.name ) 
    for row in range(s.nrows):
		if row < 1:
			fieldList=""
			fieldTypeList=""
			for col in range(s.ncols):
				cellValue = "%s"%s.cell(row,col).value
				fieldTypeList+= "`"+cellValue + '` varchar(255),'
				fieldList+= "`"+cellValue + '` ,'
			outsql.write(fieldTypeList + '\n')
			outsql.write('PRIMARY KEY (`_tmp_id`)) AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;'+ '\n')
			#关闭自动提交可以在执行大量语句时显著提高性能
			outsql.write('set autocommit =0;'+ '\n')

			insertHeader += fieldList[:-1]+") values("
		if row >= 1:
			valueList=""
			for col in range(s.ncols):
				if type(s.cell(row,col).value) == type(1.5):
					cellValue = "%d"%s.cell(row,col).value
				else:
					cellValue = "%s"%s.cell(row,col).value
				valueList+="'" + cellValue + "',"
			valueList = valueList[:-1]
			outsql.write(insertHeader + valueList + ');\n')
			rowCount = "%s"%row
    sheetNum+=1
    #break
outsql.write('commit;'+ '\n')
outsql.write('set autocommit =1;'+ '\n')
outsql.close()

outsql =open(srcFile+'.log','w')
#记录待导入的总行数
outsql.write(rowCount)
outsql.close()

endTime = time.time()
print('used time/seconds: ',endTime-startTime)

执行sql脚本的命令行命令

$cmdLine = 'mysql -h '.DATABASE_SERVER.' -u'.DATABASE_USER.' -p'.DATABASE_PWD.' '.DATABASE_NAME .' < '.$sqlFileName;

以上命令行是用php拼接而成的，其实用任意的后台语言，比如java,perl,python都可以实现命令行的拼接功能。

其中 DATABASE_SERVER 是指mysql数据库服务器的名字，一般用localhost或127.0.0.1就可以了;DATABASE_USER 指mysql数据库的用户名;DATABASE_PWD指用户密码；DATABASE_NAME指数据库名。$sqlFileName 代表第一个步骤中生成的sql脚本文件的名字。

经过实际测试，用这种方法导入包含有几万条数据的excel表格都很顺利。但如果用一些网上常见的组件往往在导入几百，几千条数据时还行，当数据量上万时就有可能会发生崩溃。所以这个方法虽然简单，但实际效果很不错。