データサイエンス

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