I want to export a mysql database to CSV on pythonanywhere. I opened a mysql console and even tried "select * from ". It shows me the correct output. But when I do "select * from into outfile 'db.csv';" it shows: "Access denied for user ''@'%' (using password: YES)".
1 Answer
As per the MySQL documentation:
The SELECT ... INTO OUTFILE statement is intended to enable dumping a table to a text file on the server host. To create the resulting file on some other host, SELECT ... INTO OUTFILE normally is unsuitable because there is no way to write a path to the file relative to the server host file system, unless the location of the file on the remote host can be accessed using a network-mapped path on the server host file system.
Alternatively, if the MySQL client software is installed on the remote host, you can use a client command such as mysql -e "SELECT ..." > file_name to generate the file on that host.
So, to put that another way -- you're connecting to a MySQL server running on a different computer to the one where you're running the MySQL console, and then running a command that is trying to write a file on that other computer, where you don't have privileges to write a file (and would not be able to access it if you did).
Instead, you should start a Bash console, then run a command like this:
mysql -u yourusername -p -h yourdatabasehost -e "select * from yourtable" 'yourdatabasename' > db.csv
...replacing yourusername, yourdatabasehost, yourtable and yourdatabasename appropriately. The single quotes around yourdatabasename are important because on PythonAnywhere, the database name will be something like yourusername$something, and the dollar sign will be interpreted by the shell as an environment variable if it is not quoted.
