python通过sshtunnel连接远程服务器

环境

• win10_x64
• centos7
• python37

模块

sshtunnel

pip3 install sshtunnel

这里的pip3是因为本人电脑同时安装了python2和python3，根据你的电脑安装实际情况输入相应的下载命令

调用模块

#!/usr/bin/python
# -*- coding: UTF-8 -*-
# author: liuqin
# date: 2020-05-16

'''通过ssh连接linux服务器A，远程访问另一台数据库服务器B，然后连接服务器Bs上面的数据库'''
from sshtunnel import SSHTunnelForwarder
import MySQLdb
ssh_ip = 'A_ip'
ssh_port = 22
ssh_user = 'user'
ssh_passwd = 'password'
db_host = 'B_ip'
db_port = 3306

db_ssh_ip = 'A_ip'
db_ssh_port = 22
db_ssh_user = 'user'
db_ssh_pass = 'passwd'
with SSHTunnelForwarder(
     (db_ssh_ip, db_ssh_port),               ##A机器配置
     ssh_username = db_ssh_user,
     ssh_password = db_ssh_pass,
     remote_bind_address = (db_host, db_port)       ##B机器配置
    ) as server:
     conn = MySQLdb.connect(host='127.0.0.1',  # 此处必须是是127.0.0.1                       11
     port = server.local_bind_port,
     user = 'user',
     passwd = 'password',
     db = 'dbname')
     cursor = conn.cursor()
     cursor.execute("select * from idoxu_bak")

以上代码中有一个问题是：
我们对于数据库连接这一部分，往往是在一个单独的函数里，与其他数据库的查询插入删除更新操作往往不在一起，这样的话，with as 有个特点就是，离开这块作用域，对象就被销毁掉了，别的函数里是没法用的，也就会出现一种情况是，连接上了，但是对象又给销毁掉了，结果查询的时候直接显示这个错误：OperationalError: (2006, 'MySQL server has gone away')， 而网上查询这个错误，多半说的是因为你查询的 sql操作的时间过长，或者是传送的数据太大 ，但是我这个地方实际上就是因为出了with as 的作用域，导致连接又给关闭掉了，所以出现这样的结果。因此进行改造一下，将SSHTunnelForwarder出来的对象赋值给server，然后启动server，然后进行一系列操作之后，再stop掉，具体见2中

2. 封装

from sshtunnel import SSHTunnelForwarder
import MySQLdb
from sqlalchemy import Column, String, create_engine,event
from sqlalchemy.orm import sessionmaker
from sqlalchemy.exc import DisconnectionError

class dbsession():
    def __init__(self):
        db_user = 'db_user'
        db_pass = 'db_pass'
        db_host = 'db_host'
        db_port = 'db_port'     ##3306
        database = 'db_database'
        db_ssh_ip = 'ssh_ip'
        db_ssh_port = 'ssh_port'    ##22
        db_ssh_user = 'ssh_user'
        db_ssh_pass = 'ssh_pass'
        self.server = SSHTunnelForwarder(
            (db_ssh_ip, int(db_ssh_port)),  # Remote server IP and SSH port
            ssh_username=db_ssh_user,
            ssh_password=db_ssh_pass,
            remote_bind_address=(db_host, int(db_port))
        )
        self.server.start()
        local_port = str(self.server.local_bind_port)
        engine = create_engine(
            "mysql://" +
            db_user +
            ":" +
            db_pass +
            "@" +
            "127.0.0.1" +
            ":" +
            local_port +
            "/" +
            database, connect_args={'charset': 'utf8'},pool_recycle=3600,pool_size=100)
        event.listen(engine,'checkout',checkout_listener) # 防止报连接池相关的错误
        # print('连接已经建立')
        DBSession = sessionmaker(bind=engine)
        self.session = DBSession()
    def __del__(self):
        self.session.close()
        self.server.stop()
def checkout_listener(dbapi_con, con_record, con_proxy):
    try:
        try:
            dbapi_con.ping(False)
        except TypeError:
            dbapi_con.ping()
    except dbapi_con.OperationalError as exc:
        if exc.args[0] in (2006, 2013, 2014, 2045, 2055):
            raise DisconnectionError()
        else:
            raise