Forums

Hey all, so I know that there are multiple posts on the subject of getting locally saved csv files to import into my server on pythonanywhere, but I have spent hours at this point making attempts to implement any of the advice I found online about it.

My use case is to create a table of city names. I have a csv file, cityNames.csv, with this information saved on my desktop. I have a database, hhuffman3817$cities, with a table, cityTable, with columns that match the column names given on the csv file.

In Bash, I start a MySQL session like so (below), and successfully enter my password and begin the session.

$ mysql -h hhuffman3817.mysql.pythonanywhere-services.com -u hhuffman3817 'hhuffman3817$cities' -p --local-infile=1

I then try to import this csv file like so:

mysql> LOAD DATA LOCAL INFILE 'C:\\Users\\<My Username>\\Desktop\\cityNames.csv'
-> INTO TABLE cityTable
-> FIELDS TERMINATED BY ','
-> LINES TERMINATED BY '\n';

and receive this error: ERROR 2 (HY000): File 'C:\Users\<My Username>\Desktop\cityNames.csv' not found (OS errno 2 - No such file or directory)

Things I've tried:

-Removing the 'LOCAL' from the command. Results in the error below.

ERROR 1045 (28000): Access denied for user 'hhuffman3817'@'%' (using password: YES)

-Specifying the table as hhuffman3817$cities.cityNames. Results in this error.

ERROR 2 (HY000): File 'C:\Users\<My Username>\Desktop\cityNames.csv' not found (OS errno 2 - No such file 
or directory)

-Moving this csv file to other parts of my directory. Results in ERROR 2 (HY000).

-Changing the delimiter from ',' to ';'. You guessed it: ERROR 2 (HY000).

It's been incredibly frustrating trying to make this work, and you have no idea how grateful I'd be to you if you know anything that can help me. I'm totally stumped. Thanks.

The command "LOAD DATA LOCAL INFILE" loads the file from the machine where the command is run; for a MySQL session started from Bash on PythonAnywhere, that will be the server on our cluster where you ran the command, not your own local machine.

So you need to upload the file to PythonAnywhere using the "Files" page first; let's say that you just upload it to your home directory, which is /home/hhuffman3817. Then you would run this command in your MySQL session:

mysql> LOAD DATA LOCAL INFILE '/home/hhuffman3817/cityNames.csv'
-> INTO TABLE cityTable
-> FIELDS TERMINATED BY ','
-> LINES TERMINATED BY '\n';