Connect postgres and Python

Question

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.

possible duplicate of Connecting to remote server with pgadmin — Brent Washburne
– Brent Washburne, Commented Jul 6, 2015 at 16:17
It looks like you were able to connect by changing your port number. Is this a duplicate of stackoverflow.com/questions/17937615/… ? — Brent Washburne
– Brent Washburne, Commented Jul 6, 2015 at 16:18

Akisame · Accepted Answer · 2015-01-24 19:48:24Z

1

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.

edited Jan 24, 2015 at 19:48

answered Jan 24, 2015 at 19:22

Akisame

7847 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

GobSmack Over a year ago

I have tried forwarding as well. But it didn't work. I'm not sure what mistake I am making. I've made changes to my question.

Akisame Over a year ago

Strange. This works for me. Have you tried with different local port? And you could check if remote port is correct. As I remember, default port for PostgreSQL is 5433. ssh -L 14141:localhost:5433 myuser@myserver #.... psycopg2.connect("dbname='my' host='localhost' port='14141' user='myuser' password='*******")

GobSmack Over a year ago

It gives me a bind: Not owner and then could not forward error for any port. Sorry, this is my first time dealing with SSH. So I don't know much. Thanks for helping.

GobSmack Over a year ago

Phew! Was able to connect to ssh finally! But, for the database, I get a connection refused error :(

Akisame Over a year ago

You've got this error when connecting from psycopg2?

|

Collectives™ on Stack Overflow

Connect postgres and Python

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related