How to do LOAD DATA command from within python
<p>How would I do the following?</p>

<pre><code>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;
    ''')
</code></pre>

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

<p><strong>Update</strong>: 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: <a href="http://stackoverflow.com/questions/1641160/how-to-load-data-infile-on-amazon-rds">how to &#39;load data infile&#39; on amazon RDS?</a>. </p>

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

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

python mysql