在 Python 中通过 SSH 隧道连接到 Postgresql 数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34965824/
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
Connecting to Postgresql database through SSH tunneling in Python
提问by Mark
I am trying to connect to a server remotely and then access it's local database with python. I am successfully connecting to the server, although I can't seem to connect to the database on the server. My code is below:
我正在尝试远程连接到服务器，然后使用 python 访问它的本地数据库。我已成功连接到服务器，但似乎无法连接到服务器上的数据库。我的代码如下：
import psycopg2
from sshtunnel import SSHTunnelForwarder
try:
with SSHTunnelForwarder(
('<server ip address>', 22),
ssh_private_key="</path/to/private/ssh/key>",
ssh_username="<server username>",
remote_bind_address=('localhost', 5432)) as server:
print "server connected"
conn = psycopg2.connect(database="<dbname>",port=server.local_bind_port)
curs = conn.cursor()
print "database connected
except:
print "Connection Failed"
These are pieces of code I have found on the internet and pieced together. I have also tried the connection statements below in place of the code above:
这些是我在互联网上找到并拼凑起来的代码片段。我还尝试了下面的连接语句来代替上面的代码：
params = {
'database': '<dbname>',
'user': '<dbusername>',
'password': '<dbuserpass>',
'host': 'localhost',
'port': 5432
}
conn = psycopg2.connect(**params)
I know I can connect to the database because on my machine; I am able to use sqlectronto tunnel in and connect appropriately.
我知道我可以连接到数据库，因为在我的机器上；我能够使用sqlectron隧道并适当地连接。
Just in case it is not clear what I am trying to do from above, I need to ssh tunnel into my remote server using a private ssh key on my computer (working properly), and then I need to connect to a PostgreSQL database that is on localhostat port 5432.
以防万一不清楚我想从上面做什么，我需要使用我计算机上的私有 ssh 密钥通过 ssh 隧道进入我的远程服务器（正常工作），然后我需要连接到一个 PostgreSQL 数据库对localhost在端口5432。
I am currently getting the current error message for both ways of trying to connect:
我目前收到两种尝试连接方式的当前错误消息：
2016-01-23 11:16:10,978 | ERROR | Tunnel: 0.0.0.0:49386 <> localhost:5432 error: (9, 'Bad file descriptor')
回答by Aliossandro
I don't know if this may be helpful, but I had to connect to a Postgresql database through SSH tunneling as well. I succeded to connect using your code with some modifications:
我不知道这是否有帮助，但我也必须通过 SSH 隧道连接到 Postgresql 数据库。我成功地使用您的代码进行了一些修改连接：
import psycopg2
from sshtunnel import SSHTunnelForwarder
try:
with SSHTunnelForwarder(
('<server ip address>', 22),
#ssh_private_key="</path/to/private/ssh/key>",
### in my case, I used a password instead of a private key
ssh_username="<server username>",
ssh_password="<mypasswd>",
remote_bind_address=('localhost', 5432)) as server:
server.start()
print "server connected"
params = {
'database': '<dbname>',
'user': '<dbusername>',
'password': '<dbuserpass>',
'host': 'localhost',
'port': local_bind_port
}
conn = psycopg2.connect(**params)
curs = conn.cursor()
print "database connected"
except:
print "Connection Failed"
After adding server.start(), the code worked nicely. Furthermore, inverted commas were missing after 'database connected'.
I hope this might be helpful to you, thanks for sharing your code!
添加后server.start()，代码运行良好。此外，在“数据库连接”之后缺少引号。我希望这可能对您有所帮助，感谢您分享您的代码！
回答by Jason Callahan
Both these examples were very helpful. I had an issue with binding the local port manually in the db connection. We have a dynamic port on our server I guess? I combined elements of the two examples. Using sever.local_bind_port may be a more robust and dynamic resolution.
这两个例子都非常有帮助。我在 db 连接中手动绑定本地端口时遇到问题。我猜我们的服务器上有一个动态端口？我结合了两个例子的元素。使用 sever.local_bind_port 可能是更健壮和动态的解决方案。
from sshtunnel import SSHTunnelForwarder #Run pip install sshtunnel
from sqlalchemy.orm import sessionmaker #Run pip install sqlalchemy
with SSHTunnelForwarder(
('<remote server ip>', 22), #Remote server IP and SSH port
ssh_username = "<username>",
ssh_password = "<password>",
remote_bind_address=('<local server ip>', 5432)) as server: #PostgreSQL server IP and sever port on remote machine
server.start() #start ssh sever
print 'Server connected via SSH'
#connect to PostgreSQL
local_port = str(server.local_bind_port)
engine = create_engine('postgresql://<username>:<password>@127.0.0.1:' + local_port +'/database_name')
Session = sessionmaker(bind=engine)
session = Session()
print 'Database session created'
#test data retrieval
test = session.execute("SELECT * FROM database_table")
for row in test:
print row['id']
session.close()
回答by Valerio DeAngelis
This solution solved the problem with Python3 on Windows 10.
此解决方案解决了 Windows 10 上 Python3 的问题。
I had to add:
我不得不补充：
from sqlalchemy import create_engine
