CODEWITHSUNDEEP
pythonpostgresqlssh
GobSmack
GobSmack

Reputation: 2261

Connect postgres and Python

I have a database available on a remote host. When I use putty and SSH, I am able to access it. The database itself has no password. Only, the SSH connection has a password. But, when I try to connect to it using Python, it asks for password. I'm new to postgres and paramiko.

Here's what I've tried:

import psycopg2
import paramiko
import time
t = paramiko.Transport(('xxx.com', 22))
t.connect(username="xxx", password='xxx') 
c = paramiko.Channel(t)
conn = psycopg2.connect("dbname='xxx'")
curs = conn.cursor()
sql = "select * from xxx"
curs.execute(sql)
rows = curs.fetchall()
print(rows)

Other method that I tried was: 

import os, psycopg2
os.system("ssh [email protected] -fNL 5432:localhost:5432 -p 22")
while True:
    try:
        conn = psycopg2.connect("dbname='xxx'")
        curs = conn.cursor()
        sql = "select * from xxx"
        curs.execute(sql)
        rows = curs.fetchall()
        print(rows)
    except:
        print "I am unable to connect to the database"

This gives me a 'Could not request local forwarding' error.

Is there some other way to go about this? I have a Windows 7 (x64) machine with Python 2.7. Please help me. Thanks.

Upvotes: 4

Views: 5544

Answers (1)

Akisame
Akisame

Reputation: 774

You should connect to the remote server and make port forwarding of remote PostgreSQL to a local port.

Without paramiko, it's something like this:

# start port forwarding
$ ssh -L PGSQL_LOCAL_PORT:localhost:PGSQL_REMOTE_PORT [email protected]
# in python
psycopg.connect("dbname='xxx' host='localhost' port='PGSQL_LOCAL_PORT'")

Here is an example of doing this with paramiko
https://code.ros.org/trac/wg-ros-pkg/browser/pkg/trunk/paramiko/demos/forward.py?rev=30

Note: Port forwarding is blocking operation. So, you have to start port forwarding in separate thread/process.

Upvotes: 1

Related Questions