Psycopg2 在远程主机上访问 PostgreSQL 数据库，无需手动打开 ssh 隧道
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22046708/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Psycopg2 access PostgreSQL database on remote host without manually opening ssh tunnel
提问by Luca Fiaschi
My standard procedure for accessing a PostgreSQL database on a remote server is to first create an ssh tunnel as:
我在远程服务器上访问 PostgreSQL 数据库的标准程序是首先创建一个 ssh 隧道，如下所示：
ssh [email protected] -L 5432:localhost:5432 -p 222
and then run my query in python from another shell as:
然后从另一个 shell 在 python 中运行我的查询：
conn = psycopg2.connect("host=localhost" + " dbname=" +
conf.dbname + " user=" + conf.user +
" password=" + conf.password)
cur = conn.cursor()
cur.execute(query)
This piece of python code works nicely once the tunnel is created. However, I would like psycopg2 to already open the SSH tunnel or reach "somehow" the remote database without need to redirect it on my localhost.
一旦创建了隧道，这段 python 代码就可以很好地工作。但是，我希望 psycopg2 已经打开 SSH 隧道或“以某种方式”到达远程数据库，而无需将其重定向到我的本地主机上。
Is it possible to do this with psycopg2?
可以用 psycopg2 做到这一点吗？
Is otherwise possible open the ssh tunnel in my python code?
否则可以在我的 python 代码中打开 ssh 隧道吗？
if I use:
如果我使用：
os.system("ssh [email protected] -L 5432:localhost:5432 -p 222")
The shell will be redirected to the remote host blocking the execution of main thread.
shell 将被重定向到阻止主线程执行的远程主机。
采纳答案by Luca Fiaschi
For the moment I am using a solution bsed on this gist:
目前我正在使用基于此要点的解决方案：
class SSHTunnel(object):
"""
A context manager implementation of an ssh tunnel opened from python
"""
def __init__(self, tunnel_command):
assert "-fN" in tunnel_command, "need to open the tunnel with -fN"
self._tunnel_command = tunnel_command
self._delay = 0.1
def create_tunnel(self):
tunnel_cmd = self._tunnel_command
import time, psutil, subprocess
ssh_process = subprocess.Popen(tunnel_cmd, universal_newlines=True,
shell=True,
stdout=subprocess.PIPE,
stderr=subprocess.STDOUT,
stdin=subprocess.PIPE)
# Assuming that the tunnel command has "-f" and "ExitOnForwardFailure=yes", then the
# command will return immediately so we can check the return status with a poll().
while True:
p = ssh_process.poll()
if p is not None: break
time.sleep(self._delay)
if p == 0:
# Unfortunately there is no direct way to get the pid of the spawned ssh process, so we'll find it
# by finding a matching process using psutil.
current_username = psutil.Process(os.getpid()).username
ssh_processes = [proc for proc in psutil.get_process_list() if proc.cmdline == tunnel_cmd.split() and proc.username == current_username]
if len(ssh_processes) == 1:
self.ssh_tunnel = ssh_processes[0]
return ssh_processes[0]
else:
raise RuntimeError, 'multiple (or zero?) tunnel ssh processes found: ' + str(ssh_processes)
else:
raise RuntimeError, 'Error creating tunnel: ' + str(p) + ' :: ' + str(ssh_process.stdout.readlines())
def release(self):
""" Get rid of the tunnel by killin the pid
"""
self.ssh_tunnel.terminate()
def __enter__(self):
self.create_tunnel()
return self
def __exit__(self, type, value, traceback):
self.release()
def __del__(self):
self.release()
def test():
#do things that will fail if the tunnel is not opened
print "done =========="
command = "ssh [email protected] -L %d:localhost:%d -p 222 -fN" % (someport, someport)
with SSHTunnel(command):
test()
Please let me know if anybody has a better idea
如果有人有更好的主意，请告诉我
回答by mrts
You could also use sshtunnel, short and sweet:
你也可以使用sshtunnel，简短而甜蜜：
from sshtunnel import SSHTunnelForwarder
PORT=5432
with SSHTunnelForwarder((REMOTE_HOST, REMOTE_SSH_PORT),
ssh_username=REMOTE_USERNAME,
ssh_password=REMOTE_PASSWORD,
remote_bind_address=('localhost', PORT),
local_bind_address=('localhost', PORT)):
conn = psycopg2.connect(...)
回答by piro
Call your ssh via os.systemin a separate thread/process. You can also use -Nwith ssh to avoid opening a remote shell.
通过os.system在单独的线程/进程中调用您的 ssh 。您还可以-N与 ssh 一起使用以避免打开远程 shell。
回答by mathewguest
Clodoaldo Neto's code worked for me perfectly but beware it doesn't clean up the process afterward.
Clodoaldo Neto 的代码对我来说效果很好，但要注意它之后不会清理过程。
The method shown by Luca Fiaschi also works for me. I updated it a bit for python3 and the updated psutil module. The changes were just that process.username and process.cmdline are now functions and that the iterator is process_iter() instead of get_process_list().
Luca Fiaschi 展示的方法也适用于我。我为 python3 和更新的 psutil 模块更新了一点。变化只是 process.username 和 process.cmdline 现在是函数，迭代器是 process_iter() 而不是 get_process_list()。
Here is an example of a very slightly modified version of the code Luca Fiaschi posted that works with python3 (requires psutil module). I hope it is at least mostly correct!
这是 Luca Fiaschi 发布的代码的稍微修改版本的示例，该版本适用于 python3（需要 psutil 模块）。我希望它至少大部分是正确的！
#!/usr/bin/env python3
import psutil
import psycopg2
import subprocess
import time
import os
# Tunnel Config
SSH_HOST = "111.222.333.444"
SSH_USER = "user"
SSH_KEYFILE = "key.pem"
SSH_FOREIGN_PORT = 5432 # Port that postgres is running on the foreign server
SSH_INTERNAL_PORT = 5432 # Port we open locally that is forwarded to
# FOREIGN_PORT on the server.
# Postgres Config
DB_HOST = "127.0.0.1"
DB_PORT = SSH_INTERNAL_PORT
DB_PASSWORD = "password"
DB_DATABASE = "postgres"
DB_USER = "user"
class SSHTunnel(object):
"""
A context manager implementation of an ssh tunnel opened from python
"""
def __init__(self, tunnel_command):
assert "-fN" in tunnel_command, "need to open the tunnel with -fN"
self._tunnel_command = tunnel_command
self._delay = 0.1
self.ssh_tunnel = None
def create_tunnel(self):
tunnel_cmd = self._tunnel_command
ssh_process = subprocess.Popen(tunnel_cmd, universal_newlines=True,
shell=True, stdout=subprocess.PIPE, stderr=subprocess.STDOUT,
stdin=subprocess.PIPE)
# Assuming that the tunnel command has "-f" and "ExitOnForwardFailure=yes", then the
# command will return immediately so we can check the return status with a poll().
while True:
p = ssh_process.poll()
if p is not None: break
time.sleep(self._delay)
if p == 0:
# Unfortunately there is no direct way to get the pid of the spawned ssh process, so we'll find it
# by finding a matching process using psutil.
current_username = psutil.Process(os.getpid()).username()
ssh_processes = [proc for proc in psutil.process_iter() if proc.cmdline() == tunnel_cmd.split() and proc.username() == current_username]
if len(ssh_processes) == 1:
self.ssh_tunnel = ssh_processes[0]
return ssh_processes[0]
else:
raise RuntimeError('multiple (or zero?) tunnel ssh processes found: ' + str(ssh_processes))
else:
raise RuntimeError('Error creating tunnel: ' + str(p) + ' :: ' + str(ssh_process.stdout.readlines()))
def release(self):
""" Get rid of the tunnel by killin the pid
"""
if self.ssh_tunnel:
self.ssh_tunnel.terminate()
def __enter__(self):
self.create_tunnel()
return self
def __exit__(self, type, value, traceback):
self.release()
def __del__(self):
self.release()
command = "ssh -i %s %s@%s -fNL %d:localhost:%d"\
% (SSH_KEYFILE, SSH_USER, SSH_HOST, SSH_INTERNAL_PORT, SSH_FOREIGN_PORT)
with SSHTunnel(command):
conn = psycopg2.connect(host = DB_HOST, password = DB_PASSWORD,
database = DB_DATABASE, user = DB_USER, port = DB_PORT)
curs = conn.cursor()
sql = "select * from table"
curs.execute(sql)
rows = curs.fetchall()
print(rows)
回答by Clodoaldo Neto
from time import sleep
os.system("ssh [email protected] -fNL 5432:localhost:5432 -p 222")
while True:
try:
conn = psycopg2.connect(
"host=localhost dbname={0} user={1} password={2}".format(
conf.dbname, conf.user, conf.password
)
)
break
except psycopg2.OperationalError:
sleep(3)
