Python/MySQL - LOAD DATA LOCAL INFILE

Question

I am using the mysql connector for Python and I'm trying to run the following SQL statement via Python (Windows) - It's a .csv file:

sql1 = ('SET GLOBAL local_infile = "ON";')
cursor.execute(sql1)

sql2 = ('LOAD DATA LOCAL INFILE "' + path[1:-1] + '" INTO TABLE mytable COLUMNS  TERMINATED BY "," LINES TERMINATED BY "\\r\\n" (COL0, COL1, COL2, COL3, COL4, COL5, COL6) SET COL7 = "'some_data'";')
cursor.execute(sql2)

but when I try to execute I receive the following exception:

1148 (42000): The used command is not allowed with this MySQL version

If I try to execute LOAD DATA LOCAL INFILE on mysql console, everything runs fine.

which command is the problem? sql1 or sql2? – mgilson Aug 29 '14 at 17:27 — mgilson, Aug 29 '14 at 17:27

score 4 · Accepted Answer · answered Aug 30 '14 at 06:44

4

Load Data Infile is disabled by default with Connector/Python while creating the connection set LOCAL_FILES client flag like this:

from mysql.connector.constants import ClientFlag
conn = mysql.connector.connect(...., client_flags=[ClientFlag.LOCAL_FILES])

answered Aug 30 '14 at 06:44

Peeyush

686
4
10

It did work. I tried this once, but I forgot to add the import. – heliosk Sep 01 '14 at 11:01

score 0 · Answer 2 · answered Aug 29 '14 at 19:59

0

There are a lot of security issues with LOAD DATA, so the server is really picky. Are you logging in to localhost, not the public IP of the server? Often one IP will be granted LOAD DATA, but the other won't.

See the fine manual

answered Aug 29 '14 at 19:59

johntellsall

14,394
4
46
40

score 0 · Answer 3 · answered Aug 29 '14 at 20:27

You could iterate through each line of the file, inserting each as a row. This would be easy since you already mentioned each column is delineated by , and each row is delineated by newlines.

For example, assuming your table mytable had 8 string columns, (COL0 to COL7):

input_file = open(path[1:-1], 'r')

#Loop through the lines of the input file, inserting each as a row in mytable
for line_of_input_file in input_file:
    values_from_file = line_of_input_file.split(',', 1) #get the columns from the line read from the file

    if(len(values_from_file) == 7): #ensure that 7 columns accounted for on this line of the file
        sql_insert_row = "INSERT INTO mytable VALUES (" + values_from_file[0] + "," + values_from_file[1] + "," + values_from_file[2] + "," + values_from_file[3] + "," + values_from_file[4] + "," + values_from_file[5] + "," + values_from_file[6] + "," + some_data + ");"
        cursor.execute(sql_insert_row)
input_file.close()

Doing the `INSERT` takes a long time. I have to insert thousands of registers. That's why I choose `LOAD DATA`. — heliosk, Sep 01 '14 at 11:03

score 0 · Answer 4 · answered Sep 27 '21 at 01:07

0

With the MySQLdb driver:

import MySQLdb
from MySQLdb.constants import CLIENT

then along with other arguments to MySQLdb.connect() , pass client_flag=CLIENT.LOCAL_FILES

Discovered by studying the source, and then trying it out.

answered Sep 27 '21 at 01:07

Sasha Pachev

5,162
3
20
20

Python/MySQL - LOAD DATA LOCAL INFILE

4 Answers4

Linked