Holen Sie sich Daten von VPS MySQL mit Python 3 und SQL Alchemy

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-Benutzername',
    password='VPS MySQL Passwort',
    host='localhost',
    port='3307',
    db='DB-Name, den Sie verwenden möchten')

SSH_INFO = {
    'ssh_host': 'hoge.sakura.ne.jp',  #IP-Direkttreffer ist OK
    'ssh_port': 22,  #Port, der normalerweise für die SSH-Verbindung verwendet wird
    'ssh_username': 'SSH-Benutzername',
    'ssh_pkey': os.path.expanduser('~/.ssh/id_rsa'),  #Pfad zum privaten Schlüssel
    'remote_bind_port': 3306,  #Fernbedienung 3306
    'local_bind_port': 3307,  #Zum lokalen 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