0

I am trying to import data programatically using Python pyodbc to an Amazon RDS MySQL 8.0.13 Instance.

When I run the following code through SQLYog it works perfectly.

LOAD DATA LOCAL INFILE 'D:/path/to/my/files/myfile.csv' IGNORE INTO TABLE my_table
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n'

However when I run it on the same machine using either MySQL Workbench or through Python I get the error :

Error 1148. The used command is not allowed with this MySQL version

I am guessing it is something to do with either my connection string or ODBC Driver.

Can anyone tell me whether there are any known bugs with this command where it would work through one client (SQLYog) but not another (MySQL Workbench / Python).

Thanks

GMB
  • 216,147
  • 25
  • 84
  • 135
Mike de H
  • 599
  • 2
  • 6
  • 13

2 Answers2

0

OK - I found the answer.

It appears that the connection option local_infile=1 is only available for certain connections in Python.

I still can't find how I would enable local_infile=1 for MySQL Workbench or for pyodbc, however the module pymysql allows that option.

import pymysql
try:
    my_conn = pymysql.connect(
        host='myserver',
        port=3306,
        db='mydatabase',
        user='myuser',
        password='somethingsecret',
        local_infile=1
    )

    my_cursor = my_conn.cursor()
    my_cursor.execute("LOAD DATA LOCAL INFILE 'D:/path/to/my/files/myfile.csv' IGNORE INTO TABLE my_table FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\\r\\n")
    my_conn.commit()

except pymysql.Error as msg:
    exit(print(msg))
Mike de H
  • 599
  • 2
  • 6
  • 13
0

Ran into the same issue while running the command from MySQL Workbench.

By default, the option is set to 1 for any MySQL server running in an RDS instance.

It can also be set from MySQL Workbench using the following command:

SET GLOBAL local_infile = 1;

Anyways, glad you were able to do this programmatically in Python.

However, the data cannot be loaded from MySQL Workbench after setting this option due to a bug in its latest versions.

How to solve Error 1148 from MySql workbench and not cmd?

A workaround is to connect to the MySQL server from the command line passing the hostname, username and password.

Connect to server from command line

And then, executing your command to load the file from the MySQL client in the command line.

LOAD DATA LOCAL INFILE 'D:/path/to/my/files/myfile.csv' IGNORE INTO TABLE my_table
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n'
Abishek
  • 767
  • 5
  • 9