Forums

I created a database with several tables in MySQL Workbench . I am running a very simple python script in Pythonanywhere that is attempting to retrieve one of the tables in the MySQL Workbench database via SSH.

I configured, and made a successful connection to, the Pythonanywhere MySQL database from MYSQL Workbench (per the Pythonanywhere instructions here: https://help.pythonanywhere.com/pages/AccessingMySQLFromOutsidePythonAnywhere/).

But when I try to retrieve the table that is in MySQL Workbench database from my python script that I am running in Pythonanywhere, I get a timed out error.

I exhausted all of the Pythonanywhere forum posts on the topic. I have a Hacker account and I am new to SSH. It appears to be connecting and I also tried to test the connection via a bash console in Pythonanywhere and it seems to have worked, but I don't know why it is timing out.

Any help would be greatly appreciated. Thank you

I've seen enough of our users struggle to get MySQL Workbench working that I think I'm going to give it a go and put some official guidance together. Would you mind giving it a day? I can find some time either today or tomorrow

Yes thank you @sboyd, I would appreciate that! And just as a side note, I tried connecting through MySQL Workbench (and I have a Flask app) but if there is an easier way to extract the data in my MySQL database and bring it into my Pythonanywhere script, I would gladly do that, but if you do arrive at a solution for MySQL Workbench, that would probably be best since I (and apparently others) are already working on that type of connection.

Thanks again!

Hey @sandatest! I've given MySQL Workbench a go and have added the connection config I used to the help page - https://help.pythonanywhere.com/pages/AccessingMySQLFromOutsidePythonAnywhere/. That should get your local machine connected to your database.

On getting a Flask webapp connected to the database you could use the "From Python code" part of the help page above. Or something a bit more complete like a sqlalchemy connection to mysql, see https://www.digitalocean.com/community/tutorials/how-to-use-flask-sqlalchemy-to-interact-with-databases-in-a-flask-application

Thank you so much @sboyd for taking the time to do this. Unfortunately, when I went into MySQL Workbench and followed your instructions, it looked like I needed to change the SSH Hostname from "ssh.pythonanywhere.com" to "<myusername>.pythonanywhere.com:22". I also changed the remote management option to the second radio button which is to control it from a Windows machine. After I made those changes, I am now completely locked out from entering the connection via MySQL Workbench. FYI, I am on a Windows machine, and I am not sure if that would make a difference here since you are using a Mac. I even tried resetting my Workbench password (which I had successfully done previously for my localhost connection) and that did not work either. It seems that either or both of the changes I made completely locked me out of MySQL Workbench. It is most likely something on my end I am doing wrong, but I cannot resolve it. If you have, or anyone else has, any further guidance I would greatly appreciate it. Thanks again.

UPDATE: I was able to re-access my MySQL Workbench connection from the Home screen in MySQL Workbench by using the menus at the top of the screen: Database > Connect to Database --AND-- Database > Manage Connections. This may seem rudimentary, but I am somewhat new to MySQL.

And in both of those sub-menus, changing the SSH Hostname back to what it originally was, which is: "ssh.pythonanywhere.com"

And also, I had to re-select the "Do not use remote management" in the Remote Management tab.

Now I can access the Pythonanywhere connection in MySQL Workbench, but that does not solve the access matter when I try to access the MySQL database from Pythonanywhere. And also, when I clicked "Test Connection" the connection worked again (it did not work when I made any of the changes, it only worked when I reverted back to the original ssh connection instructions).

So basically, it's back to the original question. As before, If you have, or anyone else has, any further guidance I would greatly appreciate it.

You're quite right, I think that Sam made a mistake in his screenshot -- it should be ssh.pythonanywhere.com (or for other users who might be reading this and have accounts on our EU system, ssh.eu.pythonanywhere.com). I've fixed that, and I'll ask him to double-check the recommendation for using the remote management stuff.

Regarding your code running inside PythonAnywhere, you don't need to use any of the stuff on that help page for that. Just for complete clarity (and I think you probably already know this, but just in case): MySQL Workbench is just the application that runs on your computer that allows you to manage remote databases. Your actual data is stored on a MySQL server inside PythonAnywhere.

So, in order to read the data that you have put into the server and edited using MySQL Workbench, you just need to connect to the MySQL server using the hostname from the "Databases" page. There's no need to set up any SSH tunneling or anything like that.

Now, regarding timeouts -- keeping a connection open uses up server resources, so if a connection is left unused for a particular amount of time (5 minutes on our servers), it is closed on the server side and you need to create a new one. My guess is that you're running into that.

There are two solutions. The quick and easy one is to always open a new connection immediately before you want to read data from it, and then to close it immediately afterwards. In a Flask app, you'd do that by opening the connection at the start of each view function that hits the DB, and then closing it after you've done your queries, ideally in the "finally" clause of a "try" block.

The alternative, which requires a little more setup but is easier and more maintainable in the long run, is to use a connection manager; Flask-SQLAlchemy is the best one for Flask. If you check out our tutorial on MySQL-backed Flask websites you'll see some useful sample code. That tutorial also includes a number of other best practices which may be of interest.