Problem closing a ssh tunnel in python 3 but not in python 2

Question

Good morning.

I'm trying to read a SQL query with pandas through a SSH tunnel. It worked fine in python 2.7, but now, with python 3.7, it seems like the process paused when trying to close the tunnel. My code is the following:

def conect(lista, names):

    # Block of code where I set used variables below.

    while not success and retries < max_retries:

        try:
            print('Trying to connect ({n})...'.format(n = retries + 1))
            sshtunnel.DEFAULT_LOGLEVEL = logging.DEBUG

            with SSHTunnelForwarder((REMOTE_SERVER, 22),
                                ssh_username = user_name,
                                ssh_pkey     = ssh_pkey,
                                ssh_private_key_password= password,
                                remote_bind_address=(str_number, PUERTO),
                                local_bind_address=('', PUERTO)) as tunnel:

                engine = sqlalchemy.create_engine("postgresql+psycopg2://{user}:{passw}@{host}:{port}/{db}".format(
                        user  = user_name,
                        passw = long_pass,
                        host  = tunnel.local_bind_host,
                        port  = tunnel.local_bind_port,
                        db    = db))

                conn     = engine.connect()
                dic_df   = {name: pd.DataFrame(conn.execute(query).fetchall(), columns = conn.execute(query).keys()) for (query, name) in zip(lista, names)}


            return dic_df

        except Exception as e:
            print('Error...')
            print(e)
        retries += 1

The logs I got with debug mode are:

Python 2

2019-04-03 16:12:02,563| WAR | MainThrea/0967@sshtunnel | Could not read SSH configuration file: ~/.ssh/config

2019-04-03 16:12:02,564| INF | MainThrea/0993@sshtunnel | 0 keys loaded from agent

2019-04-03 16:12:02,564| INF | MainThrea/1042@sshtunnel | 0 keys loaded from host directory

2019-04-03 16:12:02,674| DEB | MainThrea/1229@sshtunnel | Private key file (/Users/agarzon/Desktop/id_rsa, ) successfully loaded

2019-04-03 16:12:02,675| INF | MainThrea/0914@sshtunnel | Connecting to gateway: REMOTE SERVER:22 as user user_name

2019-04-03 16:12:02,675| DEB | MainThrea/0917@sshtunnel | Concurrent connections allowed: True

2019-04-03 16:12:02,675| DEB | MainThrea/1355@sshtunnel | Trying to log in with key: 240aa5925ca5e09b3c905a48202bcfe2

2019-04-03 16:12:02,690| WAR | Thread-1/0368@ec | /Library/Python/2.7/site-packages/paramiko/kex_ecdh_nist.py:39: CryptographyDeprecationWarning: encode_point has been deprecated on EllipticCurvePublicNumbers and will be removed in a future version. Please use EllipticCurvePublicKey.public_bytes to obtain both compressed and uncompressed point encoding. m.add_string(self.Q_C.public_numbers().encode_point())

2019-04-03 16:12:02,728| WAR | Thread-1/0387@ec | /Library/Python/2.7/site-packages/paramiko/kex_ecdh_nist.py:96: CryptographyDeprecationWarning: Support for unsafe construction of public numbers from encoded data will be removed in a future version. Please use EllipticCurvePublicKey.from_encoded_point self.curve, Q_S_bytes

2019-04-03 16:12:02,730| WAR | Thread-1/0368@ec | /Library/Python/2.7/site-packages/paramiko/kex_ecdh_nist.py:111: CryptographyDeprecationWarning: encode_point has been deprecated on EllipticCurvePublicNumbers and will be removed in a future version. Please use EllipticCurvePublicKey.public_bytes to obtain both compressed and uncompressed point encoding. hm.add_string(self.Q_C.public_numbers().encode_point())

2019-04-03 16:12:02,825| INF | Srv-5432/1389@sshtunnel | Opening tunnel: 0.0.0.0:PUERTO <> str_number:PUERTO

2019-04-03 16:12:03,495| INF | MainThrea/1408@sshtunnel | Shutting down tunnel ('0.0.0.0', PUERTO)

2019-04-03 16:12:03,588| INF | Srv-5432/1395@sshtunnel | Tunnel: 0.0.0.0:PUERTO <> str_number:PUERTO released

2019-04-03 16:12:03,597| DEB | MainThrea/1422@sshtunnel | Transport is closed

which works fine...

Python 3

2019-04-03 16:16:07,326| WAR | MainThrea/0967@sshtunnel | Could not read SSH configuration file: ~/.ssh/config

2019-04-03 16:16:07,327| INF | MainThrea/0993@sshtunnel | 0 keys loaded from agent

2019-04-03 16:16:07,327| INF | MainThrea/1042@sshtunnel | 0 keys loaded from host directory

2019-04-03 16:16:07,414| DEB | MainThrea/1229@sshtunnel | Private key file (/Users/agarzon/Desktop/id_rsa, ) successfully loaded

2019-04-03 16:16:07,414| INF | MainThrea/0914@sshtunnel | Connecting to gateway: REMOTE SERVER:22 as user user_name

2019-04-03 16:16:07,414| DEB | MainThrea/0917@sshtunnel | Concurrent connections allowed: True

2019-04-03 16:16:07,415| DEB | MainThrea/1355@sshtunnel | Trying to log in with key: b'240aa5925ca5e09b3c905a48202bcfe2'

2019-04-03 16:16:07,431| WAR | Thread-1/0110@warnings | /usr/local/lib/python3.7/site-packages/paramiko/kex_ecdh_nist.py:39: CryptographyDeprecationWarning: encode_point has been deprecated on EllipticCurvePublicNumbers and will be removed in a future version. Please use EllipticCurvePublicKey.public_bytes to obtain both compressed and uncompressed point encoding. m.add_string(self.Q_C.public_numbers().encode_point())

2019-04-03 16:16:07,474| WAR | Thread-1/0110@warnings | /usr/local/lib/python3.7/site-packages/paramiko/kex_ecdh_nist.py:96: CryptographyDeprecationWarning: Support for unsafe construction of public numbers from encoded data will be removed in a future version. Please use EllipticCurvePublicKey.from_encoded_point self.curve, Q_S_bytes

2019-04-03 16:16:07,476| WAR | Thread-1/0110@warnings | /usr/local/lib/python3.7/site-packages/paramiko/kex_ecdh_nist.py:111: CryptographyDeprecationWarning: encode_point has been deprecated on EllipticCurvePublicNumbers and will be removed in a future version. Please use EllipticCurvePublicKey.public_bytes to obtain both compressed and uncompressed point encoding. hm.add_string(self.Q_C.public_numbers().encode_point())

2019-04-03 16:16:07,542| INF | Srv-5432/1389@sshtunnel | Opening tunnel: 0.0.0.0:PUERTO <> str_number:PUERTO

2019-04-03 16:16:08,184| INF | MainThrea/1408@sshtunnel | Shutting down tunnel ('0.0.0.0', PUERTO)

2019-04-03 16:16:08,229| INF | Srv-5432/1395@sshtunnel | Tunnel: 0.0.0.0:PUERTO <> str_number:PUERTO released

which is almost the same but, as you can checked, don't stop the connection with the tunnel. Also, if you break the code after setting the value of dic_df you can check that it's working fine equally, so I'm pretty sure that the fail coming from the stopping of the tunnel...

Many thanks in advance!!

Maybe the engine is still connected and it's refusing to close the tunnel. Maybe try with engine.connect() as conn: or try closing conn explicitly. I don't use sqlalchemy, I use psycopg2 but I know that my connection block uses with just like the SSHTunnelForwarder. I've used a very similar setup to what you have here with Python3.6 — J Spratt
– J Spratt, Commented Apr 3, 2019 at 15:48

J Spratt · Accepted Answer · 2019-04-03 15:52:52Z

1

Based on my comment here is an example of how I used psycopg2.

with SSHTunnelForwarder((SSH_HOST, 22),
                         ssh_username=SSH_USER,
                         ssh_password=SSH_PW,
                         remote_bind_address=('localhost', SSH_FOREIGN_PORT),
                         local_bind_address=('localhost', SSH_INTERNAL_PORT)
                         ) as server:

        with psycopg2.connect(host=server.local_bind_host,
                              port=server.local_bind_port,
                              dbname=DB_DATABASE,
                              user=DB_USER,
                              password=DB_PASSWORD
                              ) as conn:

answered Apr 3, 2019 at 15:52

J Spratt

2,0521 gold badge16 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Á. Garzón Over a year ago

Thanks for your answer, it was a good point. I don't know why but I have had problems using psycopg2, so I have tried to adapt your solution to my code replacing conn = engine.connect() with with engine.connect() as conn:, but my problem persists ...

J Spratt Over a year ago

Sorry, I don't think I have much more to offer. I just know while using Linux that if you have some process open related to your tunnel, it will hang until that connection is closed. For example, using an SSH tunnel to link a port to some foreign server. Then using another application like DBeaver that has an open connection to the foreign server, the DBeaver connection must be disconnected and then the tunnel will close.

Collectives™ on Stack Overflow

Problem closing a ssh tunnel in python 3 but not in python 2

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related