Forums

Hi! Anybody knows how to connect the data from Pythonanywhere MySQL database to a visualization tool as Power Bi, Qlik or Tableau?

Regards

This is one example on the tableau forums of how to use ssh tunneling to connect to a PythonAnywhere MySQL database.

I'm trying to do this with Google's Looker Studio, which doesn't support SSH. It does support SSL. Any idea?

There is no other way to access Pythonanywhere MySQL database from outside, only SSH.

Thanks. Looker Studio can't do this, but PowerBI can via PuTTY on windows.

I'm following the directions here https://help.pythonanywhere.com/pages/AccessingMySQLFromOutsidePythonAnywhere/ and https://www.a2hosting.com/kb/developer-corner/mysql/remote-mysql-connections/

But despite trying ~20 combinations of options in the "Tunnels" and "Sessions" options I can't get it to connect. Any advice?

For example

In "Session"

• Host Name: PeteLambert.mysql.pythonanywhere-services.com
• Port: 3306

In "Connection" -> "SSH" -> "Tunnels":

• Source Port: 3306
• Destination Port: PeteLambert.mysql.pythonanywhere-services.com:3306
• Click "Add"

The PuTTY terminal opens with no text, then after ~30s it closes with the error "Network Error: Connection timed out".

In Connection part for the Tunnels setting you should rather use ssh.pythonanywhere.com, than your database CNAME.

Thank you. Can you give me the correct settings for all the fields?

Your suggestion is different than the PythonAnywhere walkthrough, and each time I try I have to reset PuTTY from scratch which is very time consuming.

For example the following didn't work:

In "Session"

• Host Name: PeteLambert.mysql.pythonanywhere-services.com
• Port: 3306

In "Connection" -> "SSH" -> "Tunnels":

• Source Port: 3306
• Destination Port: ssh.pythonanywhere.com:3306
• Click "Add"

When I use ssh.pythonanywhere.com in both places I can log in, but I end up in the file system not the database - I'm trying to connect to the database.

Yes. That's what SSH does. In your last example, you should not use port 3306 for the Destination port, SSH is on port 22. It's also not entirely clear what the SSH tunnel section means by "source" and "destination". You may have those backwards.

Thanks Glenn,

Okay with this I can get the SSH tunnel going.

I'm now trying to log into the database from Bash. I'm using the code: mysql -h 127.0.0.1 -u PeteLambert -p and my database password

I get: ERROR 2003 (HY000): Can't connect to MySQL server on '127.0.0.1:3306' (111)

Am I logging in wrong? Or is this about ports (I left port as "22" under Session, but set it as "3306" in Connection as per the walkthrough)

Figured it out.

For anyone that needs to do it in future, it's different than in the various online walkthroughs.

Once you're in bash in the ssh tunnel the command is:

mysql -h [username].mysql.pythonanywhere-services.com -u [username] -p '[username$[database-name]'

Glad you got that working! BTW the command you use is the first one we mention on Using MySQL | PythonAnywhere help.

Thanks! I see that, and may have gotten it from there.

The "PuTTY" section of Accessing your MySQL database from outside PythonAnywhere is quite incorrect/vague though.

Could you let us know how could we improve on that section so it's more correct/unambiguous?

Okay in the end the tutorial was pretty correct. The one thing it wasn't explicit on it you use two different pythonanywhere endpoints in the two places.

Here are the settings that worked for me:

Setup PuTTY Port Forwarding:

(I've saved these settings as default)

In "Session"

• Host Name: ssh.pythonanywhere.com
• Port: 22

In "Connection" -> "SSH" -> "Tunnels":

• Source Port: 3306
• Destination Port: [Username].mysql.pythonanywhere-services.com:3306
• Click "Add"
• Click "Open"
• Log in with PyAny Account and Pass

Connect PowerBI:

• "Get data from another source" → MySQL Database
• Server: 127.0.0.1:3306
• Database: [Username]$[database_name]
• It saved password, but originally I think I clicked "Credentials" or something, then gave my username and database password, which is different

Thanks for sharing!