出于安全考虑,数据库服务器只允许堡垒机通过ssh访问,这对日常的使用带来了麻烦。昨天的工作中,就遇到了这样的问题,MySQL数据库放在了服务器A上,只允许服务器B来访问,而我在机器C上,可以通过ssh连接服务器B。为了解决在机器C上连接mysql这个问题。
1:use sqlalchemy
from sshtunnel import SSHTunnelForwarder #Run pip install sshtunnel from sqlalchemy.orm import sessionmaker #Run pip install sqlalchemy with SSHTunnelForwarder( ('10.160.1.24', 22), #Remote server IP and SSH port ssh_username = "<usr>", ssh_password = "<pwd>", remote_bind_address=('127.0.0.1', 5432) ) as server: server.start() #start ssh sever print 'Server connected via SSH' #connect to PostgreSQL local_port = str(server.local_bind_port) engine = create_engine('postgresql://<db_user>:<db_pwd>@127.0.0.1:' + local_port +'/<db_name>') Session = sessionmaker(bind=engine) session = Session() print 'Database session created' #test data retrieval test = session.execute("SELECT * FROM <table_name>")
2: use mysqldb
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', 3306)) 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')