来自Python的SSH隧道自动关闭 [英] SSH tunnel from Python is closing automatically

问题描述

我需要一些有关程序结构的建议.我正在使用 sshtunnel 连接到外部MySQL数据库.现在它可以正常工作(我可以发出SQL命令并获得结果)，但前提是这些命令必须与打开连接具有相同的功能.如果它们的功能不同，隧道会在我可以使用之前自动关闭. (请参见下面的代码-它在两个检查点之间关闭.)所以我的问题是:

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. 是否应该在每次使用时检查连接是否打开?我该怎么办?
2. 如何使用其他功能的连接?我已经在sshtunnel中看到了一个名为"keepalive"的属性(它将在指定的时间段内保持打开状态)-这是我所需要的吗?如何使用?
3. 我能忘记手动关闭隧道吗?
4. 还有什么可以帮助我解决这个问题的?您可能会说，我是这个话题的新手！

谢谢.

Python脚本:

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()

输出:

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')

推荐答案

根据 steven-rumbalski 的上面的评论:

替换: with SSHTunnelForwarder(...) as server
使用:server = SSHTunnelForwarder(...)
然后包装:server.start() ... server.stop()
围绕您想通过SSH隧道发送的代码.

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.

这是转换后的代码:

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()

这篇关于来自Python的SSH隧道自动关闭的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！