Problem bulk loading a csv file in Mysql using Python

Question

I am loading a file called 'rec.csv' to my Mysql DB. I use the code below:

import MySQLdb,os

path='testData'
absPath = os.path.abspath(path)
print absPath

conn = MySQLdb.connect(host='localhost',
                          user='root',
                          passwd='',
                          db='iens')

db_cursor = conn.cursor()

query = "LOAD DATA INFILE '"+ absPath + "/rec.csv" +"' INTO TABLE iens.recensies FIELDS TERMINATED BY ' ' LINES TERMINATED BY '\n' "

db_cursor.execute(query)
connection.commit()

For some reason it can't find the file! I can see the file exists, and the when print the path it is printing it correctly. but in the end it generates this error:

File "Load_Data.py", line 18, in <module>
    db_cursor.execute(query)
  File "/usr/lib/pymodules/python2.6/MySQLdb/cursors.py", line 166, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib/pymodules/python2.6/MySQLdb/connections.py", line 35, in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.InternalError: (29, "File '/home/hossein/Documents/Parabots/DataBase/testData/rec.csv' not found (Errcode: 13)")
hossein@hossein-laptop:~/Documents/Parabots/DataBase$ python Load_Data.py
/home/hossein/Documents/Parabots/DataBase/testData
Traceback (most recent call last):
  File "Load_Data.py", line 18, in <module>
    db_cursor.execute(query)
  File "/usr/lib/pymodules/python2.6/MySQLdb/cursors.py", line 166, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib/pymodules/python2.6/MySQLdb/connections.py", line 35, in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.InternalError: (29, "File '/home/hossein/Documents/Parabots/DataBase/testData/rec.csv' not found (Errcode: 13)")

Can someone tell me, what I am doing wrong? Thanks

Faisal Feroz · Accepted Answer · 2011-02-09 15:53:00Z

3

Make a slight change in your load query.

LOAD DATA LOCAL INFILE

i.e., add LOCAL in the query.

As per MySQL docs: If LOCAL is specified, the file is read by the client program on the client host and sent to the server. The file can be given as a full path name to specify its exact location. If given as a relative path name, the name is interpreted relative to the directory in which the client program was started.

Check this link for more information.

answered Feb 9, 2011 at 15:53

Faisal Feroz

12.8k4 gold badges42 silver badges51 bronze badges

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

Collectives™ on Stack Overflow

Problem bulk loading a csv file in Mysql using Python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related