current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Join the Stack Overflow Community
Stack Overflow is a community of 4.7 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 2 down vote favorite

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)

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.

share|improve this question
up vote 0 down vote

You can try using sshtunnel that uses paramiko and its python3 compatible.

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!!

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)
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.