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.

Answer 1

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])

Answer 2

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

Answer 3

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()

Answer 4

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.