Psycopg2 access PostgreSQL database on remote host without manually opening ssh tunnel

You could also use sshtunnel, short and sweet:

from sshtunnel import SSHTunnelForwarderPORT=5432with 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(...)

Call your ssh via os.system in a separate thread/process. You can also use -N with ssh to avoid opening a remote shell.

With sshtunnel package

I was not familiar with SSH tunnels, so I had some difficulties to use mrts's answer.Maybe thoses precisions could help someone.

In psycopg2.connect(), host and port are the one you just created by connecting remote host by ssh tunnel.

Here is my code :

from sshtunnel import SSHTunnelForwarderserver = SSHTunnelForwarder((REMOTE_HOST, REMOTE_SSH_PORT),         ssh_username=REMOTE_USERNAME,         ssh_password=REMOTE_PASSWORD,         remote_bind_address=('localhost', PORT),         local_bind_address=('localhost', PORT))server.start()import psycopg2conn = psycopg2.connect(    database=DATABASE,    user=USER,    host=server.local_bind_host,    port=server.local_bind_port,    password=PWD)cur = conn.cursor()cur.execute("select * from yourtable limit 1;")data = cur.fetchall()print(data)

I hope this example make it clearer.