通过Python用pymysql,通过sshtunnel模块ssh连接远程数据库。

会哭的雨 2020-03-26

最近在改自己的一个小脚本,准备从本地shelve文档,josn数据等格式,转移到MySQL和redis。

服务器本来就是通过SHH登录的,但我不想开放MySQL的外网端口,要设置,还怕不安全,我的MySQL就几位数的密码。

查了下资料,Python第三方有ssh登录的模块sshtunnel

这是我参考的链接:https://www.cnblogs.com/luyingfeng/p/6386093.html

我先整版拷贝过来。

安全起见,数据库的访问多半是要做限制的,所以就有一个直接的问题是,往往多数时候,在别的机器上(比如自己本地),是不能访问数据库的,给日常使用造成了很大不便。所以前几天做了个需求,是希望在任何机器上都可以ssh到某台在数据库白名单的机器上,然后访问数据库。

经人推荐,查询了一个工具叫sshtunnel ,需要在你想要登录数据库的机器上安装。

sshtunnel的基本介绍 : http://sshtunnel.readthedocs.io/en/latest/?badge=latest

这里边的图2,正好是我们描述的场景,数据库在远程某台服务器上,只能同在一起的另一台服务器才能访问,但是我们本地就需要ssh到这台服务器上

通过Python用pymysql,通过sshtunnel模块ssh连接远程数据库。

但是这个里边涉及到的代码是服务器相关,远程连接的是服务器,我们需要的是远程服务器的数据库,这个也一样,只需要变更代码中后半部分就可以了。

原代码是下边这样:

import paramiko
from sshtunnel import SSHTunnelForwarder

with SSHTunnelForwarder(
    (REMOTE_SERVER_IP, 443),
    ssh_username="",
    ssh_pkey="/var/ssh/rsa_key",
    ssh_private_key_password="secret",
    remote_bind_address=(PRIVATE_SERVER_IP, 22),
    local_bind_address=(‘0.0.0.0‘, 10022)
) as tunnel:
    client = paramiko.SSHClient()
    client.load_system_host_keys()
    client.set_missing_host_key_policy(paramko.AutoAddPolicy())
    client.connect(‘127.0.0.1‘, 10022)
    # do some operations with client session
    client.close()

print(‘FINISH!‘)

连接mysql数据库

连接mysql数据库的时候,看到网上有一位程序员已经实现了: python 使用mysqldb模块通过ssh隧道连接mysql

代码如下:

import MySQLdb
from sshtunnel import SSHTunnelForwarder

with SSHTunnelForwarder(
         (‘sshhost.domain.com‘, 22),    #B机器的配置
         ssh_password="sshpasswd",
         ssh_username="sshusername",
         remote_bind_address=(‘mysqlhost.domain.com‘, mysql.port)) as server:  #A机器的配置

    conn = MySQLdb.connect(host=‘127.0.0.1‘,              #此处必须是是127.0.0.1
                           port=server.local_bind_port,
                           user=‘user‘,
                           passwd=‘password‘,
                           db=‘dbname‘)

 然后接下来的查询什么的,直接写在with那里边,与conn对齐就可以了。

对于我这边来说有一个问题是,因为我们对于数据库连接这一部分,往往是在一个单独的函数里,与其他数据库的查询插入删除更新操作往往不在一起,这样的话,with as 有个特点就是,离开这块作用域,对象就被销毁掉了,别的函数里是没法用的,也就会出现一种情况是,连接上了,但是对象又给销毁掉了,结果查询的时候直接显示这个错误:OperationalError: (2006, ‘MySQL server has gone away‘), 而网上查询这个错误,多半说的是因为你查询的 sql操作的时间过长,或者是传送的数据太大 ,但是我这个地方实际上就是因为出了with as 的作用域,导致连接又给关闭掉了,所以出现这样的结果。

关于 with as ,有篇文章写得很详尽。理解Python中的with…as…语法

所以我把上边那个ssh代码改掉了,像是sshtunnel文档里边图一所对应的代码,一样,将SSHTunnelForwarder出来的对象赋值给server,然后启动server,然后进行一系列操作之后,再stop掉。

本来数据库连接我们写成了一个单独的函数,改了之后,直接也还放在这个函数里就好了,替代原来的connect语句。

def connect(self):
    ‘‘‘
    self.client = MySQLdb.connect(host=self.server, port=self.port, user=self.user,
                                  passwd=self.password, db=self.database,
                                  charset=self.charset)
    # log.info(‘Connect to MySQL Server: ‘ + self.server)
    ‘‘‘

    server = SSHTunnelForwarder(
            (‘sshhost.domain.com‘, 22),  # B机器的配置
            ssh_password=‘ssh_password‘,
            ssh_username=‘ssh_username‘,
            remote_bind_address=(‘mysqlhost.domain.com‘, mysql.port)
    )
    server.start()

    self.client = MySQLdb.connect(host=‘127.0.0.1‘,  # 此处必须是是127.0.0.1
                                  port=server.local_bind_port,
                                  user=‘username‘,
                                  passwd=‘password‘,
                                  db=‘dbname‘)

然后在进行查询更新删除等操作的时候,先连接一下数据库就好了,用self.client.

连接sqlserver数据库

跟mysql的一致,但是db那里要注意,SQLServer的是database, 然后是pymssql.connect就可以了,但是这个地方还要说我踩过的一个坑,我写完sqlserver之后怎么连接都连不上数据库,后来才发现是版本的问题,我把本地SQLServer更新了之后就可以了。感觉版本是个大坑

上面是原文的作者写的。其实这个模块本来是向通过中间服务器登录目标服务器的。但我的中间服务器就是目标服务器了。

import pymysql
from sshtunnel import SSHTunnelForwarder


class DataBaseHandle:
    ‘‘‘ 定义一个 MySQL 操作类‘‘‘

    def __init__(self, host=‘127.0.01‘, username=‘xxx‘, password=‘xxx‘
                 , database=‘xxx‘, port=10022):
        ‘‘‘初始化数据库信息并创建数据库连接‘‘‘
        self.w_server = SSHTunnelForwarder(
                # 中间服务器地址
                ("xxx.64.47.xxx", 22),
                ssh_username="xxx",
                ssh_pkey="~/.ssh/id_rsa",
                # ssh_private_key_password="~/.ssh/id_rsa",
                # 目标的地址与端口,因为目标地址就是中间地址所以写127.0.0.1或者localhost
                remote_bind_address=(‘127.0.0.1‘, 3306),
                # 本地的地址与端口
                local_bind_address=(‘0.0.0.0‘, 10022)
                )
        # 启动ssh实例,后续的MySQL网络连接都将在这个环境下运行。
        self.w_server.start()
        # 后面开始对MySQL的数据进行初始化
        self.host = host
        self.username = username
        self.password = password
        self.database = database
        self.port = port
        self.db = pymysql.connect(host=self.host,
                                  user=self.username,
                                  password=self.password,
                                  database=self.database,
                                  port=self.port,
                                  charset=‘utf8‘)

    #  这里 注释连接的方法,是为了 实例化对象时,就创建连接。不许要单独处理连接了。
    #
    # def connDataBase(self):
    #     ‘‘‘ 数据库连接 ‘‘‘
    #
    #     self.db = pymysql.connect(self.host,self.username,self.password,self.port,self.database)
    #
    #     # self.cursor = self.db.cursor()
    #
    #     return self.db

    def insertDB(self, sql):
        ‘‘‘ 插入数据库操作 ‘‘‘

        self.cursor = self.db.cursor()

        try:
            # 执行sql
            self.cursor.execute(sql)
            # tt = self.cursor.execute(sql)  # 返回 插入数据 条数 可以根据 返回值 判定处理结果
            # print(tt)
            self.db.commit()
        except:
            # 发生错误时回滚
            self.db.rollback()
        finally:
            self.cursor.close()

    def deleteDB(self, sql):
        ‘‘‘ 操作数据库数据删除 ‘‘‘
        self.cursor = self.db.cursor()

        try:
            # 执行sql
            self.cursor.execute(sql)
            # tt = self.cursor.execute(sql) # 返回 删除数据 条数 可以根据 返回值 判定处理结果
            # print(tt)
            self.db.commit()
        except:
            # 发生错误时回滚
            self.db.rollback()
        finally:
            self.cursor.close()

    def updateDb(self, sql):
        ‘‘‘ 更新数据库操作 ‘‘‘

        self.cursor = self.db.cursor()

        try:
            # 执行sql
            self.cursor.execute(sql)
            # tt = self.cursor.execute(sql) # 返回 更新数据 条数 可以根据 返回值 判定处理结果
            # print(tt)
            self.db.commit()
        except:
            # 发生错误时回滚
            self.db.rollback()
        finally:
            self.cursor.close()

    def selectDb(self, sql):
        ‘‘‘ 数据库查询 ‘‘‘
        self.cursor = self.db.cursor()
        try:
            self.cursor.execute(sql)  # 返回 查询数据 条数 可以根据 返回值 判定处理结果

            data = self.cursor.fetchall()  # 返回所有记录列表

            print(data)

            # 结果遍历
            for row in data:
                sid = row[0]
                name = row[1]
                # 遍历打印结果
                print(‘sid = %s,  name = %s‘ % (sid, name))
        except:
            print(‘Error: unable to fecth data‘)
        finally:
            self.cursor.close()

    def closeDb(self):
        ‘‘‘ 数据库连接关闭 ‘‘‘
        self.db.close()
        self.w_server.close()



if __name__ == ‘__main__‘:
    DbHandle = DataBaseHandle()
    DbHandle.selectDb(‘SELECT VERSION()‘)
    DbHandle.closeDb()

这个操作数据库的类也是我网上找来的,感觉写的还行,就直接拿来用了。

最后记得,在退出的时候记得关闭ssh与MySQL的连接。

相关推荐