Connection to remote MySQL db from Python 3.4

Question

I'm trying to connect to two MySQL databases (one local, one remote) at the same time using Python 3.4 but I'm really struggling. Splitting the problem into three:

• Step 1: connect to the local DB. This is working fine using PyMySQL. (MySQLdb isn't compatible with Python 3.4, of course.)
• Step 2: connect to the remote DB (which needs to use SSH). I can get it to work from the Linux command prompt but not from Python... see below.
• Step 3: connect to both at the same time. I think I'm supposed to use a different port for the remote database so that I can have both connections at the same time but I'm out of my depth here! If it's relevant then the two DBs will have different names. And if this question isn't directly related, please tell me and I'll post it separately.

Unfortunately I'm not really starting in the right place for a newbie... once I can get this working I can happily go back to basic Python and SQL but hopefully someone will take pity on me and give me a hand to get started!

For Step 2, my code is below. It seems to be quite close to the sshtunnel example which answers this question Python - SSH Tunnel Setup and MySQL DB Access - though that uses MySQLdb. For the moment I'm embedding the connection parameters – I'll move them to the config file once it's working properly.

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

This is the error I'm getting:

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

Thanks in advance.

Answer 1

Answering my own question because, with a lot of help from J.M. Fernández on Github, I have a solution: the example that I copied at the beginning uses port 3308 but port 3306 is the standard. Once I'd changed this it started working.