データベースを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に入れられるのは便利ですね。
参考
kunimitsu higashi