after connect to remote database script doesn't exit

Question

I connect to remote database. I'm using sshtunnel for this. I have no problem to connect to DB and get access to data.

My problem is that, my script doesn't exit after everything. I create connection, download data, print data, stop ssh connection, print 'exit'. This script has cease working at line server.stop() and doesn't print 'stop'. I need to interrupt it to stop working this code.

This is code:

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


server = SSHTunnelForwarder(
    ('host', 22),
    ssh_password='password',
    ssh_username='username',
    remote_bind_address=('127.0.0.1', 3306)
)
server.start()

engine = create_engine(
    'mysql+mysqldb://db_user:[email protected]:{}/temps'.format(server.local_bind_port))

query = 'SELECT * FROM temp'

df = pd.read_sql(query, engine)
print(df.head())
print(df.tail())

server.stop()
print('stop')

This script doesn't print 'stop'.

Question: Why this code can not stop working?

EDIT:

I added

trace_logger = create_logger(loglevel="TRACE")

After this I notice something interesting. Code with data transfer hasn't contain one line: Transport is closed. I checked my code without sending sql query and script has correctly finished.

logs with data transfer

2018-10-07 18:41:43,274| WAR | MainThrea/0967@sshtunnel | Could not read SSH configuration file: ~/.ssh/config
2018-10-07 18:41:43,275| INF | MainThrea/0993@sshtunnel | 0 keys loaded from agent
2018-10-07 18:41:43,275| INF | MainThrea/1042@sshtunnel | 0 keys loaded from host directory
2018-10-07 18:41:43,275| INF | MainThrea/0914@sshtunnel | Connecting to gateway: 192.168.0.102:22 as user ‘xxx’
2018-10-07 18:41:43,275| DEB | MainThrea/0917@sshtunnel | Concurrent connections allowed: True
2018-10-07 18:41:43,275| DEB | MainThrea/1369@sshtunnel | Trying to log in with password: xxx
2018-10-07 18:41:43,600| INF | Srv-56620/1389@sshtunnel | Opening tunnel: 0.0.0.0:56620 <> 127.0.0.1:3306
….. # data transfer
2018-10-07 18:41:43,945| INF | MainThrea/1328@sshtunnel | Closing all open connections...
<Logger sshtunnel.SSHTunnelForwarder (TRACE)>
2018-10-07 18:41:43,945| DEB | MainThrea/1332@sshtunnel | Listening tunnels: 0.0.0.0:56620
2018-10-07 18:41:43,945| INF | MainThrea/1408@sshtunnel | Shutting down tunnel ('0.0.0.0', 56620)
2018-10-07 18:41:44,048| INF | Srv-56620/1395@sshtunnel | Tunnel: 0.0.0.0:56620 <> 127.0.0.1:3306 released

logs without data transfer

2018-10-07 18:37:54,016| WAR | MainThrea/0967@sshtunnel | Could not read SSH configuration file: ~/.ssh/config
2018-10-07 18:37:54,017| INF | MainThrea/0993@sshtunnel | 0 keys loaded from agent
2018-10-07 18:37:54,017| INF | MainThrea/1042@sshtunnel | 0 keys loaded from host directory
2018-10-07 18:37:54,017| INF | MainThrea/0914@sshtunnel | Connecting to gateway: 192.168.0.102:22 as user ‘xxx'
2018-10-07 18:37:54,017| DEB | MainThrea/0917@sshtunnel | Concurrent connections allowed: True
2018-10-07 18:37:54,017| DEB | MainThrea/1369@sshtunnel | Trying to log in with password: xxx
2018-10-07 18:37:54,342| INF | Srv-56560/1389@sshtunnel | Opening tunnel: 0.0.0.0:56560 <> 127.0.0.1:3306
2018-10-07 18:37:54,363| INF | MainThrea/1328@sshtunnel | Closing all open connections...
<Logger sshtunnel.SSHTunnelForwarder (TRACE)>
2018-10-07 18:37:54,363| DEB | MainThrea/1332@sshtunnel | Listening tunnels: 0.0.0.0:56560
2018-10-07 18:37:54,363| INF | MainThrea/1408@sshtunnel | Shutting down tunnel ('0.0.0.0', 56560)
2018-10-07 18:37:54,448| INF | Srv-56560/1395@sshtunnel | Tunnel: 0.0.0.0:56560 <> 127.0.0.1:3306 released
2018-10-07 18:37:54,448| DEB | MainThrea/1422@sshtunnel | Transport is closed

Answer 1

After log inspection it turns out that sqlalchemy open connections was the problem.

We created trace_logger = sshtunnel.create_logger(loglevel="TRACE") and passed it to SSHTunnelForwarder

To anyone for future reference:

Adding engine.dispose() after df.read_sql will close all hanging connections to database allowing the ssh tunnel to be closed.

Relevant documentation from sqlalchemy

Answer 2

Just want to add to this in case anyone has the same issue where engine.dispose() doesn't work. I'm on windows python 3.7. Took me hours to find the solution

issuing server.daemon_forward_servers = True before server.start() fixed the problem for me.

More references here: https://github.com/pahaz/sshtunnel/issues/138