通过Python用pymysql,通过sshtunnel模块ssh连接远程数据库。
最近在改自己的一个小脚本,准备从本地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到这台服务器上
但是这个里边涉及到的代码是服务器相关,远程连接的是服务器,我们需要的是远程服务器的数据库,这个也一样,只需要变更代码中后半部分就可以了。
原代码是下边这样:
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的连接。