python远程连接ssh_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中

封装

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