Forums

SSHTunnelForwarder and MySQLdb for external pythonanywhere db access

Hi!,

I'm trying to connect from external PC to a pythonanywhere database with SSHTunnelForwarder and MySQLdb libraries. I found on the web this code that I'm trying to adapt but it doesn't work. How can I make it? 

from sshtunnel import SSHTunnelForwarder
import MySQLdb

with SSHTunnelForwarder(
    ssh_host = 'ssh.pythonanywhere.com',
    local_bind_address = ('localhost',3306),
    remote_bind_address=('mysql.server', 3306),
    ssh_password= <pythonanywhere_psswd>,
    ssh_username= <user>
    ) as server:

    conn = MySQLdb.connect(host='127.0.0.1',
                           port=server.local_bind_port,
                           user= <user>,
                           passwd= <dbpasswd>,
                           db= <user>$<dbasename>)
deleted-user-1218232 | 9 posts | March 28, 2017, 8:50 a.m. | permalink

And? What happens?

Staff glenn | 9718 posts | PythonAnywhere staff | March 28, 2017, 9:44 a.m. | permalink

Happens this:

(gedit:2903): Gtk-CRITICAL **: gtk_widget_get_preferred_width_for_height: assertion 'height >= 0' failed

(gedit:2903): Gtk-WARNING **: gtk_widget_size_allocate(): attempt to allocate widget with width 10 and height -27
$ python2 ssh.py
Traceback (most recent call last):
  File "ssh.py", line 38, in <module>
    ssh_username= 'username'
  File "/usr/local/lib/python2.7/dist-packages/sshtunnel.py", line 1482, in __enter__
    self.start()
  File "/usr/local/lib/python2.7/dist-packages/sshtunnel.py", line 1221, in start
    self._create_tunnels()
  File "/usr/local/lib/python2.7/dist-packages/sshtunnel.py", line 1082, in _create_tunnels
    msg = 'Problem setting SSH Forwarder up: {0}'.format(e.args[0])
IndexError: tuple index out of range

[edit by admin: formatting]

deleted-user-1218232 | 9 posts | March 28, 2017, 3:52 p.m. | permalink

Sorry, no idea. It looks to me like there is something wrong with the call to SSHTunnelForwarder. Perhaps you can look at the sshtunnel docs to work out what is wrong.

Staff glenn | 9718 posts | PythonAnywhere staff | March 28, 2017, 3:59 p.m. | permalink

Manually I can do this:

ssh -L 3306:mysql.server:3306 username@ssh.pythonanywhere.com

and then, to connect to mysql

mysql -u username -h username.mysql.pythonanywhere-services.com -p

It works, but I need to do this stuff under a python script

deleted-user-1218232 | 9 posts | March 28, 2017, 4:48 p.m. | permalink

Which version of the sshtunnel library are you using? (You can check using "pip2 show sshtunnel")

Staff giles | 12096 posts | PythonAnywhere staff | March 29, 2017, 11:36 a.m. | permalink

Im using sshtunnel 0.1.2 version

deleted-user-1218232 | 9 posts | April 1, 2017, 7:40 a.m. | permalink

Check the docs for the version that you're using to find out why it doesn't like the arguments you're providing.

Staff glenn | 9718 posts | PythonAnywhere staff | April 1, 2017, 9:37 a.m. | permalink

More specifically: as far as I can see from the sshtunnel code, you're not passing in the correct keyword parameters. ssh_host should be ssh_address_or_host.

Staff giles | 12096 posts | PythonAnywhere staff | April 4, 2017, 11:50 a.m. | permalink

I've also tried this code, I'm not sure if the params are ok:

with SSHTunnelForwarder(
     ssh_address_or_host = ('ssh.pythonanywhere.com',3306),
     local_bind_address = ('localhost',3306),
     remote_bind_address=('mysql.server',3306),
     ssh_password= <pythonanywhere_psswd>,
     ssh_username= <username>
     etc.....(rest of the code)

this is the error I get:

Traceback (most recent call last):
  File "ssh.py", line 39, in <module>
    ssh_username= <username>
  File "/home/anaconda3/lib/python3.5/site-packages/sshtunnel.py", line 1482, in __enter__
    self.start()
  File "/home/anaconda3/lib/python3.5/site-packages/sshtunnel.py", line 1224, in start
    reason='Could not establish session to SSH gateway')
  File "/home/anaconda3/lib/python3.5/site-packages/sshtunnel.py", line 1036, in _raise
    raise exception(reason)
sshtunnel.BaseSSHTunnelForwarderError: Could not establish session to SSH gateway
deleted-user-1218232 | 9 posts | April 8, 2017, 7:27 p.m. | permalink

I think the first parameter is wrong: 

ssh_address_or_host = ('ssh.pythonanywhere.com',3306),

should be

ssh_address_or_host = ('ssh.pythonanywhere.com', 22),
Staff giles | 12096 posts | PythonAnywhere staff | April 9, 2017, 3:34 p.m. | permalink

One other problem that you might be having is a timeout -- the SSHTunnelForwarder has quite short timeouts by default.

We've put some sample code (including setting the timeout to something that seems to work well) on the help page.

Staff giles | 12096 posts | PythonAnywhere staff | May 12, 2017, 6:48 p.m. | permalink

Hello, I'm having this problem too. My code is taken from the example on the help page:

sshtunnel.SSH_TIMEOUT = 5.0
sshtunnel.TUNNEL_TIMEOUT = 5.0

with sshtunnel.SSHTunnelForwarder(
('ssh.pythonanywhere.com'),
ssh_username='my_username', ssh_password='my_pa_password',
remote_bind_address=('xxxx.mysql.pythonanywhere-services.com', 3306)
) as tunnel:
    cnx = mysql.connector.connect(
        user='my_username', password='my_db_password',
        host='127.0.0.1', port=tunnel.local_bind_port,
        database='my_username$my_db_name',
    )

The error message is:

cursor = cnx.cursor()
File "/usr/lib/python2.7/dist-packages/mysql/connector/connection.py", line 1383, in cursor
raise errors.OperationalError("MySQL Connection not available.")
mysql.connector.errors.OperationalError: MySQL Connection not available.

I can ssh into my shell and then MySQL OK from the command line.

deleted-user-1811176 | 8 posts | Nov. 22, 2017, 12:17 p.m. | permalink

that all looks correct to me, so it's pretty baffling. does manual ssh tunnelling work?

Staff harry | 2710 posts | PythonAnywhere staff | Nov. 22, 2017, 1:08 p.m. | permalink

Can you try to do the ssh tunnelling manually and see what happens? ie.

  1. ssh -vD 3306 ssh.pythonanywhere.com
  2. run the mysql command locally (not from the ssh session) to try to connect to it
  3. if that works, run your python script (without the ssh tunnelling stuff) to try to connect to it
Staff conrad | 4232 posts | PythonAnywhere staff | Nov. 22, 2017, 2:08 p.m. | permalink

Thank you Harry and Conrad. I've run your ssh command Conrad and it defaults to my forename (richard@ssh.pythonanywhere...) whereas my username on PythonAnywhere is different. Might that be a problem?

I can ssh into the shell no problem using this command as above:

ssh -L 3306:mysql.server:3306 username@ssh.pythonanywhere.com

Thanks again.

deleted-user-1811176 | 8 posts | Nov. 22, 2017, 6:10 p.m. | permalink

By default ssh uses your local username - so you should specify it. Like this:

ssh -vD 3306 sidestrand@ssh.pythonanywhere.com

The "working" command seems suspicious - mysql.server has not been a valid hostname on PythonAnywhere for some time.

Staff glenn | 9718 posts | PythonAnywhere staff | Nov. 23, 2017, 4:36 p.m. | permalink

Hello I'm having the same problem. The code in the help page didn't work

deleted-user-4256749 | 1 post | July 28, 2018, 8:08 p.m. | permalink

It looks like you have a free account -- as the help page says, SSH tunnels to MySQL only works from paid accounts.

Staff giles | 12096 posts | PythonAnywhere staff | July 29, 2018, 1:55 p.m. | permalink

I am able to use sshtunnel to connect to my PA MySQLDB using the sample code as given in the examples.. However if I enclose the code block in try except the cursor.execute fails. even the connection.cursor command seems to take forever. Why so. I am trying to handle connection failure in my except block. Sample code which is enclosed in try except block

import mysql.connector
import sshtunnel

sshtunnel.TUNNEL_TIMEOUT = 5.0

with sshtunnel.SSHTunnelForwarder(
('ssh.pythonanywhere.com'),
ssh_username='my_username', ssh_password='my_pa_password',
remote_bind_address=('xxxx.mysql.pythonanywhere-services.com', 3306)
) as tunnel:
    cnx = mysql.connector.connect(
        user='my_username', password='my_db_password',
        host='127.0.0.1', port=tunnel.local_bind_port,
        database='my_username$my_db_name',
    )

[edit by admin: formatting]

deleted-user-6583563 | 9 posts | March 18, 2020, 12:23 a.m. | permalink

Could you post the full code that's failing? You mention that it fails on a cursor.execute and a connection.cursor, but they're not in the example that you posted.

One common source of confusion is that all of your DB code must be inside the with block -- if you're trying to use cnx from outside that block, then it will fail because the SSH tunnel is shut down as soon as you exit that block.

Staff giles | 12096 posts | PythonAnywhere staff | March 18, 2020, 11:44 a.m. | permalink

Hello! I have the same problems. I took the code from example. My account is not free and I can do it manually. I have such error: mysql.connector.errors.OperationalError: MySQL Connection not available.

deleted-user-7746647 | 1 post | July 9, 2020, 9:33 p.m. | permalink

Could you post the exact code that you're using (with the passwords replaced with XXXX)?

Staff giles | 12096 posts | PythonAnywhere staff | July 10, 2020, 10:09 a.m. | permalink