Connecting to MySQL via Python and I have contradictory results

Question

I am trying to connect to a MySQL database hosted on Python Anywhere. In the below example connection.get_server_info() returns a result however connection.is_connected() returns False and I am unsure why.

Here is my code:

import mysql.connector
import sshtunnel

sshtunnel.SSH_TIMEOUT = 5.0
sshtunnel.TUNNEL_TIMEOUT = 5.0

with sshtunnel.SSHTunnelForwarder(
    ('ssh.pythonanywhere.com'),
    ssh_username='USERNAME', ssh_password='PASSWORD',
    remote_bind_address=('USERNAME.mysql.pythonanywhere-services.com', 3306)
) as tunnel:
    connection = mysql.connector.connect(
        user='USERNAME', password='DB_PASSWORD',
        host='127.0.0.1', port=tunnel.local_bind_port,
        database='USERNAME$default',
    )
db_info = connection.get_server_info()
if connection.is_connected():      
    print('Connected to MySQL DB...version on ', db_info)
else:
    print('Failed to connect.')
print(db_info)
connection.close()

I have a paid account on Python Anywhere so SSH tunneling should be possible

Answer 1

It's because you're trying to access the SSH tunnel after it has been closed; the tunnel is closed when you exit the with block, so anything that uses the connection needs to be indented so that it is contained within it. Your code above look like this:

import mysql.connector
import sshtunnel

sshtunnel.SSH_TIMEOUT = 5.0
sshtunnel.TUNNEL_TIMEOUT = 5.0

with sshtunnel.SSHTunnelForwarder(
    ('ssh.pythonanywhere.com'),
    ssh_username='USERNAME', ssh_password='PASSWORD',
    remote_bind_address=('USERNAME.mysql.pythonanywhere-services.com', 3306)
) as tunnel:
    connection = mysql.connector.connect(
        user='USERNAME', password='DB_PASSWORD',
        host='127.0.0.1', port=tunnel.local_bind_port,
        database='USERNAME$default',
    )
    db_info = connection.get_server_info()
    if connection.is_connected():      
        print('Connected to MySQL DB...version on ', db_info)
    else:
        print('Failed to connect.')
    print(db_info)
    connection.close()