Sample Python script: ssh tunnel

这是一个使用Python脚本，通过ssh tunnel来连接数据库的示例脚本，测试环境使用的是

import pandas as pd
import pymysql
import logging
import sshtunnel
from sshtunnel import SSHTunnelForwarder

ssh_host = 'xxxxxxx'
ssh_port=22
ssh_username = 'xxx'
ssh_password = 'xxxx'

database_host='xxxxxx'
database_port=3306
database_username = 'xxxxxxx'
database_password = 'xxxxxxx'
database_name = 'xxxxxx'
'''
-------------------------------------------------------------------
                         |
-------------+           |   +----------+                +---------
 local client|           |   | ECS/CVM  |                | Database
  web  client| <== SSH ====> | in VPC-A | <== in VPC ==> | in VPC-A
-------------+           |   +----------+                +---------
                         |
-------------------------------------------------------------------
'''
tunnel = SSHTunnelForwarder(
    (ssh_host, ssh_port),
    ssh_username = ssh_username,
    ssh_password = ssh_password,
    remote_bind_address = (database_host, database_port),
)
tunnel.start()

connection = pymysql.connect(
    host='127.0.0.1',
    user=database_username,
    passwd=database_password,
    db=database_name,
    port=tunnel.local_bind_port
)

df = pd.read_sql_query("SELECT VERSION()", connection)
print(df)

df = pd.read_sql_query("SELECT * FROM a LIMIT 10", connection)
print(df)

df.head()
connection.close()
tunnel.close

测试环境说明：

$ python3
Python 3.8.2 (default, Apr  8 2021, 23:19:18)
[Clang 12.0.5 (clang-1205.0.22.9)] on darwin