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>)

And? What happens?

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]

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.

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

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

Im using sshtunnel 0.1.2 version

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

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.

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

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),

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.

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.

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

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

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.

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.