MySQL LOAD DATA LOCAL INFILE Python

Question

I am running Ubuntu 12.04 and MySQL 5.5 Alright so here is the problem:

Using the MySQLDB module for Python, the SQL command:

cursor.execute("LOAD DATA LOCAL INFILE 'example.csv' INTO TABLE 'example_mysql_table' TERMINATED BY ',';")

Doesn't work. I get ERROR 1148: The used command is not allowed with this MySQL version

I have looked around for a solution for this for a while now, and so far it seems other people having the same problem have fixed it by adding "local-infile = 1" to 'my.cnf' underneath [mysqld] and [mysql]. This has not worked for me, and I am not sure why.

Relevant Link:

MySQL: Enable LOAD DATA LOCAL INFILE

Did you restart MySQL after modifying my.cnf? What happens when you run it with the MySQL console directly? — Nathan Villaescusa
– Nathan Villaescusa, Commented Oct 15, 2012 at 6:12
I did restart MySQL after modifying and it didn't change anything. The command does work when running it directly on the MySQL console, so I am assuming it is an issue with MySQLdb — user1746082
– user1746082, Commented Oct 16, 2012 at 1:08

Johann Bosman · Accepted Answer · 2012-10-31 09:13:07Z

69

After spending hours on trying all kinds of settings in the config files and restarting mysql dozens of times I came up with this which seems to solve the problem (could not find this in any documentation anywhere)

MySQLdb.connect(server, username, password, database, local_infile = 1)

answered Oct 31, 2012 at 9:13

Johann Bosman

7134 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

sadmicrowave Over a year ago

great solution, pure genius! and keeps me from editing my.conf

Hatem Jaber Over a year ago

coming a little late on this thread, I was curious if this is the recommended way and also if you can pass other arguments when creating the connection similar to this.

zhihong Over a year ago

Thanks, this is how it should be to add the local_infile option, but not to edit my.conf.

AustEcon Over a year ago

Thanks. It's a shame though that the more optimized c binding _mysql.connect doesn't have the same option... Any luck with that?

Devart · Accepted Answer · 2012-10-17 08:00:19Z

4

As I see, there is no file option local-infile. But you can change this value dynamically in a script.

To view this option run this query -

SELECT @@global.local_infile;

To set variable use this statement -

SET @@global.local_infile = 1;

Tested in MySQL command line console, everything is OK:

SET @@GLOBAL.local_infile = 1;
LOAD DATA LOCAL INFILE 'test.csv' INTO TABLE table1;
Query OK, 3 rows affected (0.06 sec)

SET @@GLOBAL.local_infile = 0;
LOAD DATA LOCAL INFILE 'test.csv' INTO TABLE table1;
ERROR 1148 (42000): The used command is not allowed with this MySQL version

edited Oct 17, 2012 at 8:00

answered Oct 15, 2012 at 6:16

Devart

120k23 gold badges171 silver badges189 bronze badges

5 Comments

user1746082 Over a year ago

I ran those queries in the MySQL console, and they worked, but when I added a line in my python code to set the local_infile variable to 1, ala: cursor.execute("SELECT @@global.local_infile = 1;") nothing changed, still got the 1148 error.

Devart Over a year ago

Were there any errors? Or maybe someone, or something reset this flag again? Anyway, if it works in console it should work everywhere.

Devart Over a year ago

Stop... You shouldn't run SELECT. Use SET statement!

user1746082 Over a year ago

Sorry for the typo, I did use the SET statement and not SELECT. It still does not work..

Devart Over a year ago

I have tried to reproduce the problem. Everything works. Have a look at answer.

stoicalpirate · Accepted Answer · 2018-09-18 04:30:58Z

I know this is a really old thread, but I thought I'd just add a little footnote regarding Flask.

I was unable to upload any csv files to MySQL via LOAD DATA INFILE, so I attempted to use LOAD DATA LOCAL INFILE. I got the same 'ERROR 1148: The used command is not allowed with this MySQL version' mentioned above.

My solution to this was to open up flaskext > mysql.py and modify the 'connect' function. I added:

if self.app.config['MYSQL_LOCAL_INFILE']:
    self.connect_args['local_infile'] = self.app.config['MYSQL_LOCAL_INFILE']

I then added:

app.config['MYSQL_LOCAL_INFILE'] = True

to my flask module. This effectively allows for the local_infile option of pymysql to be set to 'True' when using flaskext.mysql

Collectives™ on Stack Overflow

MySQL LOAD DATA LOCAL INFILE Python

3 Answers 3

4 Comments

5 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

5 Comments

Comments

Linked

Related