Using mysql load data infile with django

To import large amounts of CSV data from a file into a MySQL database you can use the LOAD DATA [LOCAL] INFILE command. There are some caveats using this command having to do with security. In order for LOAD DATA to work you will have to enable the command on both the server and client.

To do this for the server, you can edit your server's my.cnf file and add local-infile statement to the [mysqld] or [safemysql] section, like so:

[mysqld]
local-infile    = 1

This must also be configured on the client side, for example by adding it to your .my.cnf file in your homedirectory:

[mysql]
host=localhost
user=[user]
password=[password]
local-infile=1

Now the catch is to do a LOAD DATA command from Django (or a generic python script using the mysqldb api directly). You will have to add the local infile as an option for the MySQL-python client. Like so:

DATABASE_ENGINE = 'mysql'
DATABASE_NAME = '[db name]'
DATABASE_USER = '[db user]'
DATABASE_PASSWORD = '[db password]'
DATABASE_HOST = 'localhost'
DATABASE_PORT = ''
# Add extra options for the database backend
DATABASE_OPTIONS = {
    'local_infile': 1,
}

From within your django project you can now do:

from django.db import connection
cursor = connection.cursor()
nr_records_inserted = cursor.execute("LOAD DATA LOCAL INFILE '/tmp/import.csv' INTO TABLE import CHARACTER SET latin1 FIELDS TERMINATED BY ';' ENCLOSED BY '"' LINES TERMINATED BY '\n' (id, name);")