python使用跳板机访问数据库 mysql

python使用跳板机访问数据库 mysql

安装python模块sshtunnel

pip install sshtunnel
pip install pymysql

代码

from sshtunnel import SSHTunnelForwarder
import pymysql
import pymysql.cursors

pymysql.install_as_MySQLdb()

db_host = "101.2.1.22"
db_port = 3306
db_user = "stat_query"
db_password = "pggXNBviboEfl@hk"

pk_file = "/Users/admin/***.pem"

with SSHTunnelForwarder(
        ('跳板机地址', 端口),
        ssh_username="username",
        ssh_password="password",
        ssh_pkey=pk_file,
        remote_bind_address=(db_host, db_port)
) as tunnel:
    conn = pymysql.connect(host='127.0.0.1',
                           port=tunnel.local_bind_port,
                           user=db_user,
                           password=db_password,
                           db='rentcar',
                           charset='utf8mb4',
                           cursorclass=pymysql.cursors.DictCursor)


    cur = conn.cursor()
    cur.execute("select customer_id, phone from table where customer_id>" + str(max_id) + " and customer_id < " + str(max_id + 10000) + " and phone like '% %' ")
    rows = cur.fetchall()
    # print(rows)
    try:
        for row in rows:
            print(str(row['customer_id']) + ", " + row['phone'])
    except Exception as e:
        print("error" + e)
    conn.close()