Obtenez des données de VPS MySQL avec Python 3 et 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='Nom d'utilisateur VPS Mysql',
    password='Mot de passe VPS MySQL',
    host='localhost',
    port='3307',
    db='Nom de la base de données que vous souhaitez utiliser')

SSH_INFO = {
    'ssh_host': 'hoge.sakura.ne.jp',  #La frappe directe IP est OK
    'ssh_port': 22,  #Port généralement utilisé pour la connexion SSH
    'ssh_username': 'Nom d'utilisateur SSH',
    'ssh_pkey': os.path.expanduser('~/.ssh/id_rsa'),  #Chemin vers la clé privée
    'remote_bind_port': 3306,  #télécommande 3306
    'local_bind_port': 3307,  #Vers le port local 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