Get data from MySQL on a VPS with Python 3 and SQLAlchemy

from sshtunnel import SSHTunnelForwarder

import os
from sshtunnel import SSHTunnelForwarder
from sqlalchemy import create_engine
import pandas as pd

URL = "{connector}://{user}:{password}@{host}:{port}/{db}?charset=utf8".format(
    connector='mysql+mysqlconnector',
    user='VPS Mysql username',
    password='VPS MySQL password',
    host='localhost',
    port='3307',
    db='DB name you want to use')

SSH_INFO = {
    'ssh_host': 'hoge.sakura.ne.jp',  #IP direct hit is OK
    'ssh_port': 22,  #Port that is usually used for SSH connection
    'ssh_username': 'SSH username',
    'ssh_pkey': os.path.expanduser('~/.ssh/id_rsa'),  #Path to private key
    'remote_bind_port': 3306,  #remote 3306
    'local_bind_port': 3307,  #To local port 3307
}

engine = create_engine(URL)


def with_ssh(func):
    def ssh_func(*args, **kwargs):
        with SSHTunnelForwarder(
            (SSH_INFO['ssh_host'], SSH_INFO['ssh_port']),
            ssh_username=SSH_INFO['ssh_username'],
            ssh_pkey=SSH_INFO['ssh_pkey'],
            remote_bind_address=('localhost', SSH_INFO['remote_bind_port']),
            local_bind_address=('localhost', SSH_INFO['local_bind_port']),
        ):
            return func(*args, **kwargs)
    return ssh_func


@with_ssh
def read_remote_sql(query):
    df = pd.read_sql(query, engine)
    return df


read_remote_sql('select * from {}'.format('t_piyo'))

Recommended Posts