pythonから踏み台サーバを経由したデータベースへの接続

データベースをpythonで操作したい。直接繋げるのは簡単ですが、セキュリティ上踏み台サーバを経由するケースも多々あると思います

今回はpythonのsshtunnelを使い、踏み台サーバ経由でDatabaseの実行をしたいと思います DatabaseはMySQL, 操作ツールはSQLAlchemyを使います

インストール

まずは必要なパッケージをインストールします

pip install sshtunnel PyMySQL SQLAlchemy

早速実行してみます。 sshtunnelのgithubページにあるexampleと近いやり方にですので、よかったらそちらをご覧ください

まずは単純にローカルからリモート接続の例

from sshtunnel import SSHTunnelForwarder


SSH_PKEY = '~/.ssh/burahburah.pem'
SSH_ADDRESS = '※SSHサーバのIPアドレスまたはホスト名'
SSH_USERNAME = '※ユーザー名'
SSH_PASSWORD = '※パスワード'
SSH_REMOTE_BIND_ADDRESS = '※踏み台サーバから接続するサーバのIPまたはホスト名'
DATABASE_NAME = '※接続するデータベース名'

server = SSHTunnelForwarder(
    JUMP_SERVER_IP,
    ssh_username=SSH_USERNAME,
    ssh_password=SSH_PASSWORD,
    ssh_private_key_password=SSH_PRIVATE_KEY_PASSWORD,
    ssh_pkey=SSH_PKEY, # JUMP_SERVER_IPにホスト名を入れている(sshのconfigを設定している)場合はなくてもよい
    remote_bind_address=(REMOTE_BIND_ADDRESS, 3306)
)

server.start()

print(server.local_bind_port)

server.close()

>53126

何かしらの数値がでれば問題なく接続できた結果となります

REMOTE_BIND_ADDRESSにはDatabaseのホスト名とポート番号を入れます. 上記はMySQLに接続するため、デフォルトの3306になってますが postgresの場合は5432, RedShiftは5439など適宜変えてください

出力された 53126 はローカルから踏み台サーバを介してDatabaseのサーバに接続に接続するためのポート番号となっておりますこのポートはテンポラリの接続ポートで、実行の度に変わります

次にこの接続ポートを使ってデータベースに接続します

先ほどはデータベースのサーバまでの接続でしたが、実際にデータベースの応答までやってみます

from sqlalchemy import create_engine
from sshtunnel import SSHTunnelForwarder

SSH_PKEY = '~/.ssh/burahburah.pem'
SSH_ADDRESS = '※SSHサーバのIPアドレスまたはホスト名'
SSH_USERNAME = '※ユーザー名'
SSH_PASSWORD = '※パスワード'
SSH_REMOTE_BIND_ADDRESS = '※踏み台サーバから接続するサーバのIPまたはホスト名'
DB_USERNAME = '※DBユーザ名'
DB_PASSWORD = '※DBパスワード'
DATABASE_NAME = '※使用するデータベース名'

server = SSHTunnelForwarder(
    JUMP_SERVER_IP,
    ssh_username=SSH_USERNAME,
    ssh_password=SSH_PASSWORD,
    ssh_private_key_password=SSH_PRIVATE_KEY_PASSWORD,
    ssh_pkey=SSH_PKEY, # JUMP_SERVER_IPにホスト名を入れている場合はなくてもよい
    remote_bind_address=(REMOTE_BIND_ADDRESS, 3306)
)

server.start()

local_port = server.local_bind_port

engine = create_engine(
    f'mysql+pymysql://{DB_USERNAME}:{DB_PASSWORD}@127.0.0.1:{local_port}/{DATABASE_NAME}'
)

sql = f"""
    SELECT 'Hello Database';
"""

with engine.connect() as conn:
    result = conn.execute(sql)
    for row in result:
        print(row)

server.close()

# 結果
('Hello Database',)

pymysqlはsqlalchemyから呼ぶため明示的にimportはしません代わりにcreate_engineのurlに +pymysql を入れています

pandasを使って結果をそのままDataFrameにすることもできます

import pandas as pd

...省略...

engine = create_engine(
    f'mysql+pymysql://{DB_USERNAME}:{DB_PASSWORD}@127.0.0.1:{local_port}/{DATABASE_NAME}'
)

conn = engine.connect()

sql = f"""
    SELECT 'Hello Database' AS col1;
"""

df = pd.read_sql(sql, conn)
print(df)

conn.close()
server.close()

# 結果
             col1
0  Hello Database

できましたでしょうか？ pandasと連携することでクエリ結果をそのままDataFrameに入れられるのは便利ですね

参考

SQLAlchemy

sshtunnel

kunimitsu higashi