【Python】 使用pymysql模块通过ssh隧道连接mysql

通常运维为了保证数据库安全，不会允许直接连接数据库，需要通过SSH隧道去连接服务器背后的数据库。下面直接上代码：

import pymysql
from sshtunnel import SSHTunnelForwarder

if __name__ == '__main__':
    server = SSHTunnelForwarder(
        ssh_address_or_host=('<服务器地址>', 22),  # 指定ssh登录的跳转机的address
        ssh_username='root',  # 跳转机的用户
        ssh_password='ZHIrensha123456',  # 跳转机的密码
        remote_bind_address=('<数据库地址>', 3306))
    server.start()
    db = 'dbname'
    myConfig = pymysql.connect(
            user="root",
            passwd="password",
            host="127.0.0.1",  # 此处必须是 127.0.0.1
            db=db,
            port=server.local_bind_port)
    cursor =myConfig.cursor()
    cursor.execute('SELECT COUNT(*) FROM table;')
    print(cursor.fetchall())
    server.stop()
    cursor.close()

使用 with 语句

import pymysql
from sshtunnel import SSHTunnelForwarder

if __name__ == '__main__':
     with SSHTunnelForwarder(
        ssh_address_or_host=('<服务器地址>', 22),  # 指定ssh登录的跳转机的address
        ssh_username='root',  # 跳转机的用户
        ssh_password='ZHIrensha123456',  # 跳转机的密码
        remote_bind_address=('<数据库地址>', 3306)) as server:
        db = 'resultdb'
        myConfig = pymysql.connect(
            user="root",
            passwd="password",
            host="127.0.0.1", # 此处必须是 127.0.0.1
            db=db,
            port=server.local_bind_port)
        cursor =myConfig.cursor()
        cursor.execute('SELECT COUNT(*) FROM table;')
        print(cursor.fetchall())
        cursor.close()

with 会自动 start 和 stop。