python连接远程服务器、并执行sql_通过Python用pymysql,通过sshtunnel模块ssh连接远程数据库。...

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

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

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

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

我先整版拷贝过来。

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

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

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

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

原代码是下边这样：

1 importparamiko

2 from sshtunnel importSSHTunnelForwarder

3

4 with SSHTunnelForwarder(

5 (REMOTE_SERVER_IP, 443),

6 ssh_username="",

7 ssh_pkey="/var/ssh/rsa_key",

8 ssh_private_key_password="secret",

9 remote_bind_address=(PRIVATE_SERVER_IP, 22),

10 local_bind_address=('0.0.0.0', 10022)

11 ) as tunnel:

12 client =paramiko.SSHClient()

13 client.load_system_host_keys()

14 client.set_missing_host_key_policy(paramko.AutoAddPolicy())

15 client.connect('127.0.0.1', 10022)

16 #do some operations with client session

17 client.close()

18

19 print('FINISH!')

连接mysql数据库

代码如下：

1 importMySQLdb

2 from sshtunnel importSSHTunnelForwarder

3

4 with SSHTunnelForwarder(

5 ('sshhost.domain.com', 22), #B机器的配置

6 ssh_password="sshpasswd",

7 ssh_username="sshusername",

8 remote_bind_address=('mysqlhost.domain.com', mysql.port)) as server: #A机器的配置

9

10 conn = MySQLdb.connect(host='127.0.0.1', #此处必须是是127.0.0.1

11 port=server.local_bind_port,

12 user='user',

13 passwd='password',

14 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语句。

1 defconnect(self):

2 '''

3 self.client = MySQLdb.connect(host=self.server, port=self.port, user=self.user,

4 passwd=self.password, db=self.database,

5 charset=self.charset)

6 # log.info('Connect to MySQL Server: ' + self.server)

7 '''

8

9 server =SSHTunnelForwarder(

10 ('sshhost.domain.com', 22), #B机器的配置

11 ssh_password='ssh_password',

12 ssh_username='ssh_username',

13 remote_bind_address=('mysqlhost.domain.com', mysql.port)

14 )

15 server.start()

16

17 self.client = MySQLdb.connect(host='127.0.0.1', #此处必须是是127.0.0.1

18 port=server.local_bind_port,

19 user='username',

20 passwd='password',

21 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的连接。