使用 Python 通过 SSH 隧道连接到远程 PostgreSQL 数据库 [英] Connecting to remote PostgreSQL database over SSH tunnel using Python

问题描述

我在使用 SSH 隧道连接到远程数据库时遇到问题(现在我正在尝试使用 Paramiko).这是我的代码:

I have a problem with connecting to a remote database using SSH tunnel (now I'm trying with Paramiko). Here is my code:

#!/usr/bin/env python3
import psycopg2
import paramiko
import time

#ssh = paramiko.SSHClient()
#ssh.load_system_host_keys()
#ssh.set_missing_host_key_policy(paramiko.AutoAddPolicy())
#ssh.connect('pluton.kt.agh.edu.pl', 22, username='aburban', password='pass')

t = paramiko.Transport(('pluton.kt.agh.edu.pl', 22))
t.connect(username="aburban", password='pass') 
c = paramiko.Channel(t)
conn = psycopg2.connect(database="dotest")
curs = conn.cursor()
sql = "select * from tabelka"
curs.execute(sql)
rows = curs.fetchall()
print(rows)

一个问题是程序总是试图连接到本地数据库.我尝试使用其他 SSH 隧道，但情况相同.远程服务器上的数据库存在并且通过终端使用经典"SSH 连接可以正常工作.

A problem is that the program always tries to connect to the local database. I tried with other SSH tunnels and there was the same situation. Database on remote server exists and works fine using "classical" SSH connection via terminal.

推荐答案

您可以尝试使用 sshtunnel 模块，该模块使用 Paramiko 并且与 Python 3 兼容.

You can try using sshtunnel module that uses Paramiko and it's Python 3 compatible.

希望它对你有所帮助...我也挠了一阵子想在 Python 代码中做这件事并避免 SSH 外部隧道，我们需要感谢开发人员将复杂的库包装成简单的代码！

Hopefully it helps you... I scratched myself the head for a while too to do it within Python code and avoid SSH external tunnels, we need to thank developers that wrap complex libraries into simple code!

很简单，从本地端口生成到远程服务器 localhost 中端口 5432 的隧道，然后使用它通过 localhost 连接到远程数据库.

Will be simple, generate a tunnel to port 5432 in localhost of remote server from a local port then you use it to connect via localhost to the remote DB.

这将是您需要的示例工作代码:

This will be a sample working code for your needs:

#!/usr/bin/env python3
import psycopg2
from sshtunnel import SSHTunnelForwarder
import time

with SSHTunnelForwarder(
         ('pluton.kt.agh.edu.pl', 22),
         ssh_password="password",
         ssh_username="aburban",
         remote_bind_address=('127.0.0.1', 5432)) as server:

    conn = psycopg2.connect(database="dotest",port=server.local_bind_port)
    curs = conn.cursor()
    sql = "select * from tabelka"
    curs.execute(sql)
    rows = curs.fetchall()
    print(rows)

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