Pymysql with SSH

pymysql_ssh.py

from os.path import expanduser

import pandas as pd
import paramiko
import pymysql
from paramiko import SSHClient
from sshtunnel import SSHTunnelForwarder

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

ssh_hostname = "ssh_hostname"
ssh_username = "ssh_username"
ssh_port = 22
sql_hostname = "sql_hostname"
sql_username = "sql_username"
sql_password = "sql_password"
sql_dbname = "db_name"
sql_port = 3306


with SSHTunnelForwarder(
    (ssh_hostname, ssh_port),
    ssh_username=ssh_username,
    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()