Enable Python to Connect to MySQL via SSH Tunnelling

by

Only this worked for me

import pymysql
import paramiko
import pandas as pd
from paramiko import SSHClient
from sshtunnel import SSHTunnelForwarder
from os.path import expanduser

home = expanduser('~')
mypkey = paramiko.RSAKey.from_private_key_file(home + pkeyfilepath)
# if you want to use ssh password use - ssh_password='your ssh password', bellow

sql_hostname="sql_hostname"
sql_username="sql_username"
sql_password = 'sql_password'
sql_main_database="db_name"
sql_port = 3306
ssh_host="ssh_hostname"
ssh_user="ssh_username"
ssh_port = 22
sql_ip = '1.1.1.1.1'

with SSHTunnelForwarder(
        (ssh_host, ssh_port),
        ssh_username=ssh_user,
        ssh_pkey=mypkey,
        remote_bind_address=(sql_hostname, sql_port)) as tunnel:
    conn = pymysql.connect(host="127.0.0.1", user=sql_username,
            passwd=sql_password, db=sql_main_database,
            port=tunnel.local_bind_port)
    query = '''SELECT VERSION();'''
    data = pd.read_sql_query(query, conn)
    conn.close()

More Related Contents:

  1. Can’t install mysql-python (newer versions) in Windows
  2. Wait until task is completed on Remote Machine through Python [duplicate]
  3. Creating multiple python dictionaries from a file
  4. Why doesn’t Python run my bash code?
  5. What is the result of % in Python?
  6. Convert string to variable name in python [duplicate]
  7. Is there a simple way to get rid of junk values that come when you SSH using Python’s Paramiko library and fetch output from CLI of a remote machine?
  8. Lost connection to MySQL server during query
  9. List on python appending always the same value [duplicate]
  10. How to install PIL with pip on Mac OS?
  11. sklearn error ValueError: Input contains NaN, infinity or a value too large for dtype(‘float64’)
  12. ImportError: numpy.core.multiarray failed to import
  13. How to get PID by process name?
  14. Error importing hashlib with python 2.7 but not with 2.6
  15. How to normalize json correctly by Python Pandas
  16. Is it good practice to use `import __main__`?
  17. Get output from a Paramiko SSH exec_command continuously
  18. Bulk insert with SQLAlchemy ORM
  19. Matplotlib: display plot on a remote machine
  20. RuntimeError: working outside of application context
  21. Type hinting in Python 2
  22. Nested SSH session with Paramiko
  23. Script skips second for loop when reading a file
  24. “Failed to load HostKeys” warning while connecting to SFTP server with pysftp
  25. flake8 complains on boolean comparison “==” in filter clause
  26. What does “table” in the string.translate function mean?
  27. Catching exception in context manager __enter__()
  28. confused about random_state in decision tree of scikit learn
  29. Is possible to mapping view with class using mapper in SqlAlchemy?
  30. Python 2.7 : LookupError: unknown encoding: cp65001 [duplicate]

Leave a Comment