0%

Python通过SSH隧道连接MySQL数据库

方式一:通过MySQLdb模块

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
def con_sshDb(self, sql):
# ssh的地址,端口,用户名,密码
from sshtunnel import SSHTunnelForwarder
with SSHTunnelForwarder(
('XXX.XXX.XXX.XXX', 22),
ssh_password="XXXXXX",
ssh_username="XXXXXX",
remote_bind_address=('XXX.XXX', 3306)
) as server:
# 此处必须是是127.0.0.1
import MySQLdb
conn = MySQLdb.connect(host='127.0.0.1',
port=server.local_bind_port,
# Navicat常规处的链接用户名和密码,以及连接数据库名称
user='XXXXXX',
passwd='XXXXXX',
db='XXXXXX')
# .cursor()用来获得python执行Mysql命令的方法
cursor = conn.cursor()
select = sql
# .execute()执行mysql语句
cursor.execute(select)
# fetchall()则是接收全部的返回结果行
data = cursor.fetchall()
return data

方式二:通过paramiko模块

1
2
3
4
5
6
7
8
9
def con_sshDb(self, sql):
import paramiko
ssh = paramiko.SSHClient()
ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
ssh.connect("XXX.XXX.XXX.XXX", 22, "ssh_username", "ssh_password")
select = sql
stdin, stdout, stderr = ssh.exec_command("mysql -u user -p passwd -D mysql -e 'select'")
print stdout.readlines()
ssh.close()

方式二:通过sqlalchemy模块

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
def con_sshDb(self, sql):
from sshtunnel import SSHTunnelForwarder
with SSHTunnelForwarder(
('XXX.XXX.XXX.XXX', 22),
ssh_password="XXXXXX",
ssh_username="XXXXXX",
remote_bind_address=('XXX.XXX', 3306)
) as server:
server.start() # start ssh sever
print 'Server connected via SSH'

# connect to PostgreSQL
local_port = str(server.local_bind_port)
from sqlalchemy import create_engine
engine = create_engine('postgresql://<db_user>:<db_pwd>@127.0.0.1:' + local_port +'/<db_name>')

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
session = Session()
print 'Database session created'

select = sql
data = session.execute(select)
return data