Forums

Can somebody tell me what is wrong with this code. I am able to connect via SSH on the commandline, but this does not want to work The error message i am getting is:

2019-11-24 13:35:32,304| ERROR   | Could not open connection to gateway
Traceback (most recent call last):
  File "sshpythonanywhere2.py", line 11, in <module>
    remote_bind_address=('Pipsy100.mysql.pythonanywhere-services.com', 3306)
  File "C:\anaconda\lib\site-packages\sshtunnel.py", line 1552, in __enter__
    self.start()
  File "C:\anaconda\lib\site-packages\sshtunnel.py", line 1294, in start
    reason='Could not establish session to SSH gateway')
  File "C:\anaconda\lib\site-packages\sshtunnel.py", line 1100, in _raise
    raise exception(reason)
sshtunnel.BaseSSHTunnelForwarderError: Could not establish session to SSH gateway





import mysql.connector
import sshtunnel


sshtunnel.SSH_TIMEOUT = 5.0
sshtunnel.TUNNEL_TIMEOUT = 5.0

with sshtunnel.SSHTunnelForwarder(
    ('ssh.pythonanywhere.com'),
    ssh_username='Pipsy100', ssh_password='XXXXXXX',
    remote_bind_address=('Pipsy100.mysql.pythonanywhere-services.com', 3306)
) as tunnel:
    connection = mysql.connector.connect(
        user='Pipsy100', password='XXXXXXX',
        host='127.0.0.1', port=tunnel.local_bind_port,
        database='Pipsy100$mysite',
    )


    mycursor = connection.cursor()
    mycursor.execute("CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))")
    mycursor.close()
    connection.close()

[edited by admin: formatting]

could you try if ssh-ing normally works instead of the using the ssh tunnel? If it doesn't then you would need to double check your password

Conrad, I am not an xpert on SSH. If i use the command-prompt and SSH I get an connection.(So password should be OK) but via MySQL Workbench(send printscreens to support@pythonanywhere.com) or this python code everything hangs. I have the strong feeling there is something wrong in the connection to MySQL. I am sure my passwords are right. I just can't get it figured out.

Thx.

let's try to consolidate all your queries into a central place- it seems we are having multiple conversations across different forum posts and emails.

Why is this code not running? It prints the portnumber but it does not seem to connect to the database.

import mysql.connector
import sshtunnel


sshtunnel.SSH_TIMEOUT = 5.0
sshtunnel.TUNNEL_TIMEOUT = 5.0

with sshtunnel.SSHTunnelForwarder(
    ('ssh.pythonanywhere.com'),
    ssh_username='Pipsy100', ssh_password='XXXXXX',
    remote_bind_address=('Pipsy100.mysql.pythonanywhere-services.com', 3306)
) as tunnel:
    print(tunnel.local_bind_port)

    connection = mysql.connector.connect(
    user='Pipsy100', password='XXXXXXX',
    host='127.0.0.1', port=tunnel.local_bind_port,
    database='Pipsy100$default',
    )

[edit by admin: formatting]

I don't see any code that actually uses the connection. What makes you think that the connection has not been made?

Glenn, here is the complete code. If I disable the mysql connection part, it runs till the end. If I enable it, it doesn't runs till the second print statement and the code hangs. Can it also be something on the mysql server side?

sshtunnel.SSH_TIMEOUT = 5.0
sshtunnel.TUNNEL_TIMEOUT = 5.0

with sshtunnel.SSHTunnelForwarder(
    ('ssh.pythonanywhere.com'),
    ssh_username='Pipsy100', ssh_password='XXXXXX',
    remote_bind_address=('Pipsy100.mysql.pythonanywhere-services.com', 3306)
) as tunnel:
    print(tunnel.local_bind_port)


    connection = mysql.connector.connect(
    user='Pipsy100', password='XXXXXX',
    host='127.0.0.1', port=tunnel.local_bind_port,
    database='Pipsy100$default')

    mycursor = connection.cursor()
    mycursor.execute("CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))")
    mycursor.close()
    connection.close()

    print(tunnel.local_bind_port)

tunnel.stop()

[edit by admin: formatting]

What happens if you remove the extra tunnel.stop() from the end? It's not needed if you use the sshtunnel.SSHTunnelForwarder as a context manager (that is, as part of a with statement).

Hello Giles,

that doesn't make any difference. I guess the code just hangs on building the connection with the database. can it be something on the database-side?

Gr en thx

Willem

Ok. Try this: Take the mysql connection code and try to run it in a PythonAnywhere console (replacing the host and port with the direct host and port) and make sure that works. If that works, then my suspicion would be that something on your machine is blocking the connection to the local port on your machine.

Giles, it is working indeed.

I can run this code in one of your consoles:

connection = mysql.connector.connect(user='Pipsy100', password='',host='Pipsy100.mysql.pythonanywhere-services.com', database='Pipsy100$default')

But it does not work when I run it via sshtunnel:

connection = mysql.connector.connect(user='Pipsy100', password='mysComsat01',host='127.0.0.1', port=tunnel.local_bind_port,database='Pipsy100$default')

The tunnel returns a portnumber though:

print(tunnel.local_bind_port)

All the code:

import mysql.connector import sshtunnel

sshtunnel.SSH_TIMEOUT = 5.0 sshtunnel.TUNNEL_TIMEOUT = 5.0

with sshtunnel.SSHTunnelForwarder( ('ssh.pythonanywhere.com'), ssh_username='Pipsy100', ssh_password='XXX', remote_bind_address=('Pipsy100.mysql.pythonanywhere-services.com', 3306) ) as tunnel: print(tunnel.local_bind_port)

connection = mysql.connector.connect(user='Pipsy100', password='mysComsat01',host='127.0.0.1', port=tunnel.local_bind_port,database='Pipsy100$default')
#connection = mysql.connector.connect(user='Pipsy100', password='',host='Pipsy100.mysql.pythonanywhere-services.com', database='Pipsy100$default')
mycursor = connection.cursor()
mycursor.execute("CREATE TABLE customers (name VARCHAR(255), address VARCHAR(255))")
mycursor.close()
connection.close()

edit by admin to remove password

This looks exactly like the same thing that NightShade is experiencing in this forum thread and I suspect the same thing is causing both. There were some other things that NightShade tried, so have a look at the thread and see whether yours is the same.

Also, posted your SSH password. I strongly suggest that you change it.

Glenn,

changing from mysql to pymysql in the python code solved the problem.

I do not know why but some users might experience the same problem... and can use this solution.

print("Starting to do stuff....", flush=True)
connection = pymysql.connect(user='Pipsy100', password='***',host='localhost',port=tunnel.local_bind_port,database='Pipsy100$default')
print("Finishing stuff....", flush=True)

That's interesting. Glad you found something that works.