Speed Up Inserting Large Datasets From Txt File To Mysql Using Python

background: I have 500 formatted *.txt files that I need to insert into a mysql database. Currently I have a python script to read the files line by line and insert into mySQL data

Solution 1:

Two options to consider:

1) the easiest is to include multiple rows of values on one insert. This is way, way faster than doing multiple indserts.

Insetad of doing INSERT INTO tbl ( cols ) VALUES ( vals ), do something like INSERT INTO tbl ( cols ) VALUES ( vals ), ( vals ), ( vals )

The amount of rows you can insert at once depends on the maximum packet size of the mysql server, but you can probably do 100, 1000, maybe 10000 rows safely and it should give you a performance increase of an order of magnitude or more.

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

2) LOAD DATA INFILE is a bit different, requires more work and has its own requirements, but is very very fast.

Solution 2:

You are storing too much text in ram. You should do it using buffers like this:

withopen(inputfilename, 'r') as f:
    for lineString in f:
        ... do your thing

Solution 3:

Try this :

Process the txt files and generate INSERT commands for each line. After the files are done processing and you have .sql file for all the txt's, import them into the db.