Using SSHTunnel and Connecting to MySQL DB

[KowshikBokka]

I tried different configuration but end up with timed out waiting for connection everytime.

Please let me know that best way to achieve this

This is my code

with SSHTunnelForwarder( (ssh_host, ssh_port), ssh_username=ssh_user, ssh_pkey=mypkey, remote_bind_address=(sql_hostname, sql_port)) as tunnel: start_time = time.time() df = cx.read_sql(url,mysql_query) print("--- %s seconds ---" % (time.time() - start_time))

[wangxiaoying]

Hi @KowshikBokka ,

I'm not sure whysshtunnel package does not work and I will look into the reason. But I tried to establish a ssh tunnel manually (without using sshtunnel library) and it works:

1. Run command: ssh username@remote_host -L local_port:remote_host:remote_port in one terminal
2. Run code in another terminal:

cx.read_sql("mysql://db_user:[email protected]:local_port/db", query)

Maybe it can be a quick workaround. Thanks for the report.

[KowshikBokka]

Hello @wangxiaoying

Thanks for the response. It does work in the terminal however, I do want to schedule the python script on cloud function so I am still stuck on this as I can't do manual workaround every time.

[wangxiaoying]

Hi @KowshikBokka , for now to me it seems like the problem is caused by the Python GIL since the sshtunnel library is spawning a thread for a TCPServer, which will be blocked by ConnectorX when trying to receive request using poll. I try to use subprocess to start the ssh tunnel on a process instead of a thread and it works:

import subprocess
import connectorx as cx

p = subprocess.Popen(['ssh', '-N', '-l', 'username', '-L', 'local_port:remote_host:remote_port', 'remote_host'])
df = cx.read_sql("mysql://db_user:[email protected]:local_port/db", query)
p.kill()

Hopefully it can help in your current situation. We will take a deeper look at whether it is indeed the GIL cause the problem and whether we can not holding it during the entire process.