python - 使用python加速将大型数据集从txt文件插入到mySQL

Question

背景: 我有 500 个格式化的 *.txt 文件需要插入到 mysql 数据库中。目前我有一个 python 脚本来逐行读取文件并插入到 mySQL 数据库中。

问题:文件很大(每个 txt 文件约 100M)，我测试了脚本，只将一个文件插入数据库花费的时间太长。

如何通过修改脚本来加快进程？

代码:

for file in os.listdir(INPUTFILEPATH):
    ## index += 1
    ## print "processing %s out of %s files " % (index, totalfiles)
    inputfilename = INPUTFILEPATH + "/" + file 
    open_file = open(inputfilename, 'r')
    contents = open_file.readlines()
    totalLines = len(contents)
    ## index2 = 0 
    for i in range(totalLines):
        ## index2 +=1
        ## print "processing %s out of %s lines " % (index2, totalLines)
        lineString = contents[i]
        lineString = lineString.rstrip('\n')
        values = lineString.split('\t')
        if ( len(re.findall(r'[0123456789_\'\.]',values[0])) > 0 ):  
            continue 
        message = """INSERT INTO %s(word,year,count,volume)VALUES('%s','%s','%s','%s')"""% ('1gram', values[0],values[1],values[2],values[3]) 
        cursor.execute(message)
        db.commit()

cursor.close()
db.close() 

Answer 1

要考虑的两个选项:

1) 最简单的方法是在一次插入中包含多行值。这比进行多次插入要快得多。

开始做 INSERT INTO tbl ( cols ) VALUES ( vals ) , 做类似 INSERT INTO tbl ( cols ) VALUES ( vals ), ( vals ), ( vals ) 的事情

您一次可以插入的行数取决于 mysql 服务器的最大数据包大小，但您可以安全地插入 100、1000 或 10000 行，这应该会使您的性能提高一个数量级或更多的。

参见 http://dev.mysql.com/doc/refman/5.5/en/insert-speed.html

2) LOAD DATA INFILE有点不同，需要更多的工作并有自己的要求，但速度非常非常快。