title: Accessing MySQL database using Python over SSH Tunnel port forwarding
body: <p>I have MySQL database on a remote server and before accessing it I would need to do port forwarding using ssh tunnel. Without python I usually execute the below command and type in my password in the shell. Once authenticated I access the database in mysql workbench by connecting to localhost:3306. I do everything from my Mac OS X El Capitan</p>
  
  <pre><code>ssh -L 3306:remote.database.host:3306 xxxx@xxxx.com
  </code></pre>
  
  <p>I would now like to access the database from Python and here is the script I used:</p>
  
  <pre><code>import pexpect
  import time
  import sqlalchemy as sql
  from sqlalchemy.engine.url import URL
  
  myDB = URL(drivername='mysql+pymysql', host='localhost',
  database='test_db',
  username='roott',
  password='xxxxxx',
  port=3306)
  
  child = pexpect.spawnu('ssh -L 3306:remote.database.host:3306 xxxx@xxxx.com')
  child.expect (u'password:')
  child.sendline ('xxxxx')
  
  while child.isalive():
      try:
      engine = sql.create_engine(name_or_url=myDB)
      connection = engine.connect()
      connection.close()
      break
  finally:
      child.close()
  </code></pre>
  
  <p>Above script is giving me the following error:</p>
  
  <pre><code>sqlalchemy.exc.OperationalError: (pymysql.err.OperationalError) (2003, "Can't connect to MySQL server on 'localhost' ([Errno 61] Connection refused)")
  </code></pre>
  
  <p>I also tried using sshtunnelforwarder by referring to jsjc's solution at <a href="https://stackoverflow.com/questions/12989866/python-ssh-tunnel-setup-and-mysql-db-access/31639144#31639144">Python - SSH Tunnel Setup and MySQL DB Access</a> and it doesn't work either.</p>
  
  <pre><code>from sshtunnel import SSHTunnelForwarder
  from sqlalchemy import create_engine
  from sqlalchemy.engine.url import URL
  
  with SSHTunnelForwarder(('ssh_host', 22),
                      ssh_username='xxxxx',
                      ssh_pkey='/path/to/key/file',
                      local_bind_address=('127.0.0.1', 3306),
                      remote_bind_address=('127.0.0.1', 3306)) as server:
  
  
  myDB = URL(drivername='mysql+pymysql', host='127.0.0.1',
             database='test_db',
             username='roott',
             password='xxxxx',
             port=3306
             )
  
  engine = create_engine(name_or_url=myDB)
  connection = engine.connect()    
  connection.close()
  </code></pre>
  
  <p>I get the below information logged into the console and nothing happens, it just hangs in there, I have to manually kill the program.</p>
  
  <pre><code>DEBUG:paramiko.transport:starting thread (client mode): 0xbe25940
  DEBUG:paramiko.transport:Local version/idstring: SSH-2.0-paramiko_1.16.0
  DEBUG:paramiko.transport:Remote version/idstring: SSH-2.0-OpenSSH_6.2
  INFO:paramiko.transport:Connected (version 2.0, client OpenSSH_6.2)
  DEBUG:paramiko.transport:........
  DEBUG:paramiko.transport:Kex agreed: diffie-hellman-group1-sha1
  DEBUG:paramiko.transport:Cipher agreed: aes128-ctr
  DEBUG:paramiko.transport:MAC agreed: hmac-sha2-256
  DEBUG:paramiko.transport:Compression agreed: none
  DEBUG:paramiko.transport:kex engine KexGroup1 specified hash_algo &lt;built-in function openssl_sha1&gt;
  DEBUG:paramiko.transport:Switch to new keys ...
  DEBUG:paramiko.transport:Attempting public-key auth...
  DEBUG:paramiko.transport:userauth is OK
  INFO:paramiko.transport:Auth banner: b'Amazon Linux AMI release 2014.09\nKernel \\r on an \\m\n'
  INFO:paramiko.transport:Authentication continues...
  DEBUG:paramiko.transport:Methods: ['password']
  DEBUG:paramiko.transport:[chan 0] Max packet in: 32768 bytes
  WARNING:paramiko.transport:Oops, unhandled type 3
  </code></pre>