【python-SSH链接本地数据库方法】使用sshtunnel库连接数据库方法

因公司的安全要求线上数据库连接时需要通过ssh方式链接 这样Python的mysql-client库的就无法满足需求了 借鉴使用了 sshtunnel库 

如下是链接实例

from sshtunnel import SSHTunnelForwarder

def dbconnect_ssh(ssh_host, ssh_port, keyfile, ssh_user, db_host, db_name, sql, db_port, db_user, db_passwd):
    with SSHTunnelForwarder(
            (ssh_host, ssh_port),
            # ssh_password="sshpasswd",
            ssh_pkey=keyfile,
            ssh_username=ssh_user,
            remote_bind_address=(db_host, db_port)
    ) as server:

        db = pymysql.connect(
            host='127.0.0.1',
            port=server.local_bind_port,
            user=db_user,
            passwd=db_passwd,
            db=db_name,
            charset="utf8",
            cursorclass=pymysql.cursors.DictCursor)

        cursor = db.cursor()

        try:
            cursor.execute(sql)
            data = cursor.fetchall()
            db.commit()
        except:
            db.rollback()

        collect = []
        for result in data:
            collect.append(result)

        db.close()
        cursor.close()

        return collect

def test_mysql():
    # data_id=[]
    # data_name=[]
    ssh_host = "1.0.0.1"  # SSH服务器地址
    ssh_port = 22  # SSH端口
    keyfile = "./"  # SSH密钥
    ssh_user = ""  # SSH用户名
    db_host = "rdsxxxxxxx.com"  # 数据库地址
    db_name = 'xxxxxx'  # 数据库名
    db_port = 3306  # 数据库端口
    db_user = 'test'  # 数据库用户名
    db_passwd = 'test'  # 数据库密码
    # sql = f'select * from user where mobile=*********'        #SQL
    result = dbconnect_ssh(ssh_host, ssh_port, keyfile, ssh_user, db_host, db_name, sql, db_port, db_user, db_passwd)
　　 print(result)