SSH连接数据库,两种不同的写法:
第二种记得server.start()
方式一:
#!/usr/bin/env python3
import os
import pymysql as pymysql
from sshtunnel import SSHTunnelForwarder
class connect_ssh:
def connect(self, cmd):
jump_host_ip = "**" # 跳板机
jump_host_user = "user"
jump_host_key = os.getenv('HOME') + "/.ssh/id_rsa"
target_bind_address = ('**', 22)
print("connecting to the jump host")
with SSHTunnelForwarder(
ssh_address_or_host=(jump_host_ip, 22), # 跳板机
ssh_username=jump_host_user,
ssh_pkey=jump_host_key,
remote_bind_address=target_bind_address,
local_bind_address=('127.0.0.1', 13306)
) as tunnel:
print(tunnel.local_bind_address)
# 连接数据库
conn = pymysql.connect(
host='master.****',
port=6606,
database='test_db',
user='user',
password='pw'
)
cursor = conn.cursor()
query = cmd
cursor.execute(query)
data = cursor.fetchall()
print(data)
tunnel.close()
if __name__ == '__main__':
server = connect_ssh()
server.connect(cmd="select * from task_tab order by id desc limit 1;")
print("done")
方式二:
#!/usr/bin/env python3
import os
import pymysql as pymysql
from sshtunnel import SSHTunnelForwarder
class connect_ssh:
def connect(self, cmd):
jump_host_ip = "**" # 跳板机
jump_host_user = "user"
jump_host_key = os.getenv('HOME') + "/.ssh/id_rsa"
target_bind_address = ('**', 22)
print("connecting to the jump host")
server = SSHTunnelForwarder(
ssh_address_or_host=(jump_host_ip, 22), # 跳板机
ssh_username=jump_host_user,
ssh_pkey=jump_host_key,
remote_bind_address=target_bind_address,
local_bind_address=('127.0.0.1', 13306)
)
server.start()
print(server.local_bind_address)
# 连接数据库
conn = pymysql.connect(
host='master.****',
port=6606,
database='test_db',
user='user',
password='pw'
)
cursor = conn.cursor()
query = cmd
cursor.execute(query)
data = cursor.fetchall()
print(data)
server.close()
if __name__ == '__main__':
server = connect_ssh()
server.connect(cmd="select * from task_tab order by id desc limit 1;")
print("done")