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