通过Python中的SSH隧道连接到PostgreSQL数据库 [英] Connecting to PostgreSQL database through SSH tunneling in Python

问题描述

我试图远程连接到服务器，然后使用Python访问它的本地数据库。尽管似乎无法连接到服务器上的数据库，但我已成功连接到服务器。我的代码如下：

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:

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)

我知道我可以连接到数据库，因为在我的机器上；我可以使用 sqlectron 进行隧道连接并正确连接。

I know I can connect to the database because on my machine; I am able to use sqlectron to tunnel in and connect appropriately.

以防万一我不清楚我要做什么从上面开始，我需要使用计算机上的ssh私钥将ssh隧道连接到远程服务器（工作正常），然后我需要连接到 localhost 在端口 5432 。

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 localhost at port 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')

推荐答案

这两个例子都很有帮助。我只是需要结合两者的优点。

Both these examples were very helpful. I just needed to combine the good parts from both.

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()

这篇关于通过Python中的SSH隧道连接到PostgreSQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！