python - 从 Python 3.4 连接到远程 MySQL 数据库

Question

我正在尝试使用 Python 3.4 同时连接到两个 MySQL 数据库(一个本地，一个远程)，但我真的很挣扎。将问题分为三个:

• 第 1 步:连接到本地数据库。这工作正常使用 PyMySQL。 (MySQLdb 与 Python 3.4 不兼容，当然。)
• 第2步:连接到远程数据库(需要使用 SSH)。我可以让它在 Linux 命令提示符下工作，但不行来自 Python...见下文。
• 第 3 步:连接到两者同时。我想我应该使用不同的端口远程数据库，以便我可以同时拥有两个连接但我已经超出了我的深度!如果相关，那么两个数据库将有不同的名字。如果这个问题没有直接关系，请告诉我，我会单独发布。

不幸的是，对于新手来说，我并没有真正从正确的地方开始......一旦我能做到这一点，我就可以很高兴地回到基本的 Python 和 SQL，但希望有人会怜悯我并帮助我开始吧!

对于第 2 步，我的代码如下。看起来和sshtunnel相当接近回答这个问题的例子 Python - SSH Tunnel Setup and MySQL DB Access - 虽然使用 MySQLdb。目前我正在嵌入连接参数 - 一旦它正常工作，我会将它们移动到配置文件中。

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

def CloseLocalDB():
    localcur.close()
    localdb.close()

def CloseRemoteDB():
    # Disconnect from the database
#    remotecur.close()
#    remotedb.close()
    # Close the SSH tunnel
#    ssh.close()
    print("end of CloseRemoteDB function")

def OpenLocalDB():
    global localcur, localdb
    localdb = pymysql.connect(host=cfg.localdbconn['host'], user=cfg.localdbconn['user'], passwd=cfg.localdbconn['passwd'], db=cfg.localdbconn['db'])
    localcur = localdb.cursor()

def OpenRemoteDB():
    global remotecur, remotedb
    with SSHTunnelForwarder(
            ('my_remote_site', 22),
            ssh_username = "my_ssh_username",
            ssh_private_key = "/etc/ssh/my_private_key.ppk",
            ssh_private_key_password = "my_private_key_password",
            remote_bind_address = ('127.0.0.1', 3308)) as server:
        remotedb = None
#Following line gives an error if uncommented
#        remotedb = pymysql.connect(host='127.0.0.1', user='remote_db_user', passwd='remote_db_password', db='remote_db_name', port=server.local_bind_port)
        #remotecur = remotedb.cursor()

# Main program starts here
OpenLocalDB()
CloseLocalDB()
OpenRemoteDB()
CloseRemoteDB()

这是我收到的错误:

2016-04-21 19:13:33,487 | ERROR   | Secsh channel 0 open FAILED: Connection refused: Connect failed
2016-04-21 19:13:33,553 | ERROR   | In #1 <-- ('127.0.0.1', 60591) to ('127.0.0.1', 3308) failed: ChannelException(2, 'Connect failed')
----------------------------------------
Exception happened during processing of request from ('127.0.0.1', 60591)
Traceback (most recent call last):
  File "/usr/local/lib/python3.4/dist-packages/sshtunnel.py", line 286, in handle
    src_address)
  File "/usr/local/lib/python3.4/dist-packages/paramiko/transport.py", line 834, in open_channel
    raise e
paramiko.ssh_exception.ChannelException: (2, 'Connect failed')

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/usr/lib/python3.4/socketserver.py", line 613, in process_request_thread
    self.finish_request(request, client_address)
  File "/usr/lib/python3.4/socketserver.py", line 344, in finish_request
    self.RequestHandlerClass(request, client_address, self)
  File "/usr/lib/python3.4/socketserver.py", line 669, in __init__
    self.handle()
  File "/usr/local/lib/python3.4/dist-packages/sshtunnel.py", line 296, in handle
    raise HandlerSSHTunnelForwarderError(msg)
sshtunnel.HandlerSSHTunnelForwarderError: In #1 <-- ('127.0.0.1', 60591) to ('127.0.0.1', 3308) failed: ChannelException(2, 'Connect failed')
----------------------------------------
Traceback (most recent call last):
  File "/home/pi/Documents/iot_pm2/iot_ssh_example_for_help.py", line 38, in <module>
    OpenRemoteDB()
  File "/home/pi/Documents/iot_pm2/iot_ssh_example_for_help.py", line 32, in OpenRemoteDB
    remotedb = pymysql.connect(host='127.0.0.1', user='remote_db_user', passwd='remote_db_password', db='remote_db_name', port=server.local_bind_port)
  File "/usr/local/lib/python3.4/dist-packages/pymysql/__init__.py", line 88, in Connect
    return Connection(*args, **kwargs)
  File "/usr/local/lib/python3.4/dist-packages/pymysql/connections.py", line 678, in __init__
    self.connect()
  File "/usr/local/lib/python3.4/dist-packages/pymysql/connections.py", line 889, in connect
    self._get_server_information()
  File "/usr/local/lib/python3.4/dist-packages/pymysql/connections.py", line 1190, in _get_server_information
    packet = self._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

回答我自己的问题，因为在 Github 上 J.M. Fernández 的大力帮助下，我有一个解决方案:我一开始复制的示例使用端口3308，但端口3306是标准的。一旦我改变了它，它就开始工作了。