SSH tunnel from Python is closing automatically

Question

I need some advice about the structure of my program. I'm connecting to an external MySQL database using sshtunnel. It's now working correctly (I can issue SQL commands and get results) but only if the commands are in the same function as the opening of the connection. If they're in a different function, the tunnel closes automatically before I can use it. (See code below - it closes between the two checkpoints.) So my questions are:

1. Should I check that the connection is open every time I want to use it? How do I do that?
2. How do I use the connection from a different function? I've seen a property called "keepalive" in sshtunnel (which will keep the connection open for a specified length of time) - is this what I need? How do I use it?
3. Can I forget about closing the tunnel manually?
4. Anything else that would help me get this working? As you can probably tell, I'm a newbie on the subject!

Thanks.

Python script:

import pymysql, shlex, shutil, subprocess
import logging
import sshtunnel
from sshtunnel import SSHTunnelForwarder
import iot_config as cfg

def OpenRemoteDB():
    global remotecur, remotedb
    sshtunnel.DEFAULT_LOGLEVEL = logging.DEBUG
    with SSHTunnelForwarder(
            (cfg.sshconn['host'], cfg.sshconn['port']),
            ssh_username = cfg.sshconn['user'],
            ssh_private_key = cfg.sshconn['private_key_loc'],
            ssh_private_key_password = cfg.sshconn['private_key_passwd'],
            remote_bind_address = ('127.0.0.1', 3306)) as server:
        remotedb = None
        remotedb = pymysql.connect(host='127.0.0.1', user=cfg.remotedbconn['user'], passwd=cfg.remotedbconn['passwd'], db=cfg.remotedbconn['db'], port=server.local_bind_port)
        remotecur = remotedb.cursor()
        print("Checkpoint 1")
        #The next three lines work fine
#        remotecur.execute("SELECT ActionID, Description FROM cmAction")
#        for r in remotecur:
#            print(r)

def SyncActions():
    print("Checkpoint 2")
    #the next three lines don't work (because the connection has closed)
    remotecur.execute("SELECT ActionID, Description FROM cmAction")
    for r in remotecur:
        print(r)

# Main program starts here
OpenRemoteDB()
SyncActions()

Output:

2016-10-06 12:34:21,088| WAR | MainThrea/0954@sshtunnel | Could not read SSH configuration file: ~/.ssh/config
2016-10-06 12:34:21,153| INF | MainThrea/0981@sshtunnel | 0 keys loaded from agent
2016-10-06 12:34:21,963| DEB | MainThrea/1160@sshtunnel | Private key file (/etc/ssh/lavenham_private_key.key, <class 'paramiko.rsakey.RSAKey'>) successfully loaded
2016-10-06 12:34:22,003| INF | MainThrea/0901@sshtunnel | Connecting to gateway: lavenham.co.uk:22 as user 'lavenham'
2016-10-06 12:34:22,062| DEB | MainThrea/0904@sshtunnel | Concurrent connections allowed: True
2016-10-06 12:34:22,117| DEB | MainThrea/1300@sshtunnel | Trying to log in with key: b'611711d06f2b671960c3458d25ca3c20'
2016-10-06 12:34:23,083| INF | Srv-39033/1334@sshtunnel | Opening tunnel: 0.0.0.0:39033 <> 127.0.0.1:3306
Checkpoint 1
2016-10-06 12:34:23,290| INF | MainThrea/1350@sshtunnel | Shutting down tunnel ('0.0.0.0', 39033)
2016-10-06 12:34:23,424| INF | Srv-39033/1340@sshtunnel | Tunnel: 0.0.0.0:39033 <> 127.0.0.1:3306 released
2016-10-06 12:34:23,426| DEB | MainThrea/1363@sshtunnel | Transport is closed
Checkpoint 2
Traceback (most recent call last):
  File "/home/pi/Documents/iot_pm2/2016-10-06.py", line 33, in <module>
    SyncActions()
  File "/home/pi/Documents/iot_pm2/2016-10-06.py", line 27, in SyncActions
    remotecur.execute("SELECT ActionID, Description FROM cmAction")
  File "/usr/local/lib/python3.4/dist-packages/pymysql/cursors.py", line 146, in execute
    result = self._query(query)
  File "/usr/local/lib/python3.4/dist-packages/pymysql/cursors.py", line 296, in _query
    conn.query(q)
  File "/usr/local/lib/python3.4/dist-packages/pymysql/connections.py", line 819, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/local/lib/python3.4/dist-packages/pymysql/connections.py", line 1001, in _read_query_result
    result.read()
  File "/usr/local/lib/python3.4/dist-packages/pymysql/connections.py", line 1285, in read
    first_packet = self.connection._read_packet()
  File "/usr/local/lib/python3.4/dist-packages/pymysql/connections.py", line 945, in _read_packet
    packet_header = self._read_bytes(4)
  File "/usr/local/lib/python3.4/dist-packages/pymysql/connections.py", line 981, in _read_bytes
    2013, "Lost connection to MySQL server during query")
pymysql.err.OperationalError: (2013, 'Lost connection to MySQL server during query')

Answer 1

As per steven-rumbalski's comment above:

Replace: with SSHTunnelForwarder(...) as server
With: server = SSHTunnelForwarder(...)
Then wrap: server.start() ... server.stop()
around the code you want to be sent via the SSH Tunnel.

Here is the converted code:

import pymysql, shlex, shutil, subprocess
import logging
import sshtunnel
from sshtunnel import SSHTunnelForwarder
import iot_config as cfg

def OpenSSHTunnel():
    global server
    sshtunnel.DEFAULT_LOGLEVEL = logging.DEBUG
    server = SSHTunnelForwarder(
        (cfg.sshconn['host'], cfg.sshconn['port']),
        ssh_username = cfg.sshconn['user'],
        ssh_private_key = cfg.sshconn['private_key_loc'],
        ssh_private_key_password = cfg.sshconn['private_key_passwd'],
        remote_bind_address = ('127.0.0.1', 3306)
    )

def OpenRemoteDB():
    global remotecur, remotedb
    remotedb = None
    remotedb = pymysql.connect(
        host='127.0.0.1',
        user=cfg.remotedbconn['user'],
        passwd=cfg.remotedbconn['passwd'],
        db=cfg.remotedbconn['db'],
        port=server.local_bind_port
    )
    remotecur = remotedb.cursor()
    print("Checkpoint 1")

def SyncActions():
    print("Checkpoint 2")
    # this should now work as expected
    remotecur.execute("SELECT ActionID, Description FROM cmAction")
    for r in remotecur:
        print(r)

# Main program starts here
OpenSSHTunnel()
server.start()
OpenRemoteDB()
SyncActions()
server.stop()