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


Answer: 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](http://dev.mysql.com/doc/refman/5.0/en/load-data.html) for
more information.

