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?
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.
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
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
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.