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:
- Should I check that the connection is open every time I want to use it? How do I do that?
- 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?
- Can I forget about closing the tunnel manually?
- 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')