0

I am trying to load a csv file into the database but no success. The issue is that it works when I load the query directly in the mysql shell but it doesn't work when I tried doing from the python code. The code I am using is shown below:

db = connect_to_database()
cursor = db.cursor()

q = "LOAD DATA LOCAL INFILE '/home/ubuntu/load_file.csv' INTO TABLE test_load FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'"
db.query(q)
cursor.execute(q)
db.commit()

The error it throws says:

File "/usr/local/lib/python2.7/dist-packages/MySQLdb/connections.py", line 281, in query
    _mysql.connection.query(self, query)
_mysql_exceptions.OperationalError: (1148, 'The used command is not allowed with this MySQL version')

When I copied the string into the sql shell, it loads successfully. I'm not sure why its throwing this error.

Samsquanch
  • 8,866
  • 12
  • 50
  • 89
Wayne Morris
  • 93
  • 8
  • 1
    "It doesn't work" could mean anything. Do you get an exception? If so, what's the full traceback? Or does something unexpected happen? If so, what happens, and what did you expect? – Sven Marnach Sep 06 '16 at 14:41
  • Do you know about [pandas](http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.to_sql.html#pandas-dataframe-to-sql) module? When I was using similar approach with yours I had to change permission somewhere - allow to upload from that folder. – quapka Sep 06 '16 at 14:41
  • idk anything about python. But working directly versus not in another environment is often an issue with escape sequences – Drew Sep 06 '16 at 14:42
  • I have updated the post with the error message. I will look into the permissions and see if that's what is causing the error. – Wayne Morris Sep 07 '16 at 15:50
  • Possible duplicate of [MySQL: Enable LOAD DATA LOCAL INFILE](http://stackoverflow.com/questions/10762239/mysql-enable-load-data-local-infile) – e4c5 Sep 08 '16 at 11:38
  • I modified the .cnf file and it still didn't work from running my python script. It only works through running it in the sql shell. – Wayne Morris Sep 08 '16 at 19:05

1 Answers1

0

So I managed to solve the problem. All it was missing was the local_infile parameter in the MySQLDB connect:

db=MySQLdb.connect(host=raw_host,user=raw_user,passwd=raw_passwd,db=raw_db, local_infile = 1)

When I added this parameter, it worked as expected. Thank you all for your efforts in resolving the problem.

Wayne Morris
  • 93
  • 8