How to do LOAD DATA command from within python

Question

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

I don't really think its a cursor error, your user probably just doesn't have the needed permissions — hiperbolt
– hiperbolt, Commented Sep 28, 2016 at 18:31
Should I make it a response? I will, please check it as right <3 — hiperbolt
– hiperbolt, Commented Sep 28, 2016 at 18:41

rook · Accepted Answer · 2016-09-28 18:43:28Z

2

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.

edited Sep 28, 2016 at 18:43

answered Sep 28, 2016 at 18:34

rook

67.1k38 gold badges171 silver badges248 bronze badges

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

Comments

hiperbolt · Accepted Answer · 2016-09-28 18:42:03Z

1

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.

answered Sep 28, 2016 at 18:42

hiperbolt

1942 gold badges3 silver badges11 bronze badges

Collectives™ on Stack Overflow

How to do LOAD DATA command from within python

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related