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()