title: Connection to remote MySQL db from Python 3.4
body: <p>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:</p>
  
  <ul>
  <li>Step 1: connect to the local DB.  This is working fine
  using PyMySQL.  (MySQLdb isn't compatible with Python 3.4, of
  course.)</li>
  <li>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.</li>
  <li>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.</li>
  </ul>
  
  <p>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!</p>
  
  <p>For Step 2, my code is below.  It seems to be quite close to the <a href="https://github.com/pahaz/sshtunnel" rel="nofollow noreferrer">sshtunnel</a> example which answers this question <a href="https://stackoverflow.com/questions/12989866/python-ssh-tunnel-setup-and-mysql-db-access/31639144">Python - SSH Tunnel Setup and MySQL DB Access</a> - 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.</p>
  
  <pre><code>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()
  </code></pre>
  
  <p>This is the error I'm getting:</p>
  
  <pre><code>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 &lt;-- ('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 &lt;-- ('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 &lt;module&gt;
      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')
  </code></pre>
  
  <p>Thanks in advance.</p>