mysql - How to do LOAD DATA command from within python

How would I do the following?

import MySQLdb
conn = MySQLdb.connect (
                     host = settings.DATABASES['default']['HOST'],
                     port = 3306,
                     user = settings.DATABASES['default']['USER'],
                     passwd = settings.DATABASES['default']['PASSWORD'],
                     db = settings.DATABASES['default']['NAME'],
                     charset='utf8')
cursor = conn.cursor()    
cursor.execute('SELECT COUNT(*) FROM auth_user')
print cursor.fetchall() # this prints, so I know the connection is correct

cursor.execute('''
    LOAD DATA INFILE 'a_short.csv' INTO TABLE export
    FIELDS TERMINATED BY '|' ENCLOSED BY '"'
    LINES TERMINATED BY '\r\n'
    IGNORE 1 LINES;
    ''')

When I try the above I get an "Access Denied" error, however I think it is more related to trying to do the LOAD DATA command from inside the cursor. What would be the proper way to do this?

Update: This seems to be a limitation in privileges (no 'file' privilege) for the user's in Amazon RDS. Here's one way to get around this: how to 'load data infile' on amazon RDS?.

2 Answers

  1. Marcy- Reply

    2019-11-14

    In order for load file to work your database user account needs MySQL's file_priv. The database also needs to have read permissions on the file in question.

    In this query the database is instructed to look for a_short.csv on the database's filsystem, which will probably produce a path like: /var/mysql/a_short.csv. If this csv file is on the python side of the system, then python needs to open the file and populate the MySQL database.

  2. Mark- Reply

    2019-11-14

    This doesn't seem to be a cursor issue, but yet it seems that the user you are using does not having the required permissions to execute said command.

    Make sure you give the user the needed permissions.

Leave a Reply

Your email address will not be published. Required fields are marked *

You can use these HTML tags and attributes <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>