David542 David542 - 2 months ago 12
MySQL Question

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?.

Answer

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.

Comments