Forums

SqlAlchemy + SSh : sqlalchemy.exc.OperationalError

Hello and thanks for your help,

I am trying to connect remotely to my DB (i have a paid account) using SQLalchemy and SSH. There are lots of solution that I have tried but cannot move over this error :

sqlalchemy.exc.OperationalError: (_mysql_exceptions.OperationalError) (2003, "Can't connect to MySQL server on '127.0.0.1' (10061)") (Background on this error at: http://sqlalche.me/e/e3q8)

Below my code :

import pandas as pd
import APIdata
from sqlalchemy import create_engine
import sshtunnel

sshtunnel.SSH_TIMEOUT = 5.0
sshtunnel.TUNNEL_TIMEOUT = 5.0
host = '127.0.0.1'
user = 'myuser'
passwd = 'mycode'
db = 'myuser$dbname'

with sshtunnel.SSHTunnelForwarder(
    ('ssh.pythonanywhere.com'),
    ssh_password="pythonanywhere_passwd",
    ssh_username="pythonanywhere_username",
    remote_bind_address=('myuser.mysql.pythonanywhere-services.com', 3306)
) as server:
    server.start()
    print('Server connected via SSH')
    port = str(server.local_bind_port)
    conn_addr = 'mysql://' + user + ':' + passwd + '@' + host + ':' + port + '/' + db
    print("[SQL] Trying to connect to " + conn_addr)
    engine =create_engine(conn_addr, pool_recycle=280)
    print('[SQL] Connexion established on ' + conn_addr)

dataDF = pd.read_sql('SHOW TABLES from myuser$dbname', engine)
print(dataDF)

Any advice would be nice !

Thanks for your help !

I think the problem is that you're trying to access the database from outside the with block -- this means that the SSH tunnel has already been shut down when you do the pd.read_sql. If you indent the last two lines in your script, it should work.

I also don't think you need the server.start() in there, so if you still get errors after indenting the last two lines, try removing that and see if it helps.

Thanks it solved the issue :-)

Excellent -- thanks for confirming!