CODEWITHSUNDEEP
pythonmysqlpython-3.3
blade_runner
blade_runner

Reputation: 145

Fast MySQL Import

Writing a script to convert raw data for MySQL import I worked with a temporary textfile so far which I later imported manually using the LOAD DATA INFILE... command.

Now I included the import command into the python script:

    db = mysql.connector.connect(user='root', password='root',
                          host='localhost',
                          database='myDB')
    cursor = db.cursor() 
    query = """
    LOAD DATA INFILE 'temp.txt' INTO TABLE myDB.values
    FIELDS TERMINATED BY ',' LINES TERMINATED BY ';';
    """
    cursor.execute(query)
    cursor.close()
    db.commit()
    db.close()

This works but temp.txt has to be in the database directory which isn't suitable for my needs.

Next approch is dumping the file and commiting directly:

    db = mysql.connector.connect(user='root', password='root',
                          host='localhost',
                          database='myDB')
    sql = "INSERT INTO values(`timestamp`,`id`,`value`,`status`) VALUES(%s,%s,%s,%s)"
    cursor=db.cursor()

    for line in lines:
        mode, year, julian, time, *values = line.split(",")
        del values[5]
        date = datetime.strptime(year+julian, "%Y%j").strftime("%Y-%m-%d")
        time = datetime.strptime(time.rjust(4, "0"), "%H%M" ).strftime("%H:%M:%S")
        timestamp = "%s %s" % (date, time)
        for i, value in enumerate(values[:20], 1):
            args = (timestamp,str(i+28),value, mode)
            cursor.execute(sql,args)
    db.commit()

Works as well but takes around four times as long which is too much. (The same for construct was used in the first version to generate temp.txt)

My conclusion is that I need a file and the LOAD DATA INFILE command to be faster. To be free where the textfile is placed the LOCAL option seems useful. But with MySQL Connector (1.1.7) there is the known error: mysql.connector.errors.ProgrammingError: 1148 (42000): The used command is not allowed with this MySQL version

So far I've seen that using MySQLdb instead of MySQL Connector can be a workaround. Activity on MySQLdb however seems low and Python 3.3 support will probably never come.

Is LOAD DATA LOCAL INFILE the way to go and if so is there a working connector for python 3.3 available?

EDIT: After development the database will run on a server, script on a client.

Upvotes: 1

Views: 1455

Answers (2)

Peeyush
Peeyush

Reputation: 726

To use LOAD DATA INFILE with every accessible file you have to set the LOCAL_FILES client flag while creating the connection

import mysql.connector
from mysql.connector.constants import ClientFlag
db = mysql.connector.connect(client_flags=[ClientFlag.LOCAL_FILES], <other arguments>)

Upvotes: 0

Chris Lear
Chris Lear

Reputation: 6752

I may have missed something important, but can't you just specify the full filename in the first chunk of code?

LOAD DATA INFILE '/full/path/to/temp.txt'

Note the path must be a path on the server.

Upvotes: 2

Related Questions