Postgres database connection in python

How do i access a postgre database which is on a linux server machine in python? I have accessed it with putty terminal but i need to access the tables in the database through python and I am unable to do that.

Solution

#!/usr/bin/env python3
import sys
import psycopg2

try:
    conn = psycopg2.connect("dbname='dbname' user='dbuser' host='localhost' port='5432' password='dbpass'")
except psycopg2.DatabaseError:
    sys.exit('Failed to connect to database')


try:
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM test_table WHERE id < 10000")
    dbRecord = cursor.fetchone()

    if dbRecord == None:
        print('ERROR: First record not found', file=sys.stderr)
    else:
        print('Loaded {}'.format(dbRecord))
    dbRecordId = dbRecord[0]

    conn.commit()
    cursor.close()
except (Exception, psycopg2.DatabaseError) as error:
    print(error)
finally:
    if conn is not None:
        conn.close()

Also you might need to allow access in postgres. To allow users connecting to server with login/password pair uncomment or add the following lines in pg_hba.conf (typically located in /etc/postgresql/{postgres_version}/main/pg_hba.conf):

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

Edit: oh, I just realized that your postgres server is on a remote machine. The following options may apply then:

Add your IP to the pg_hba.conf configuration above and change host='localhost' to host='your.server.address.or.ip' in the script. OR:
Use ssh port forwarding to be able to access the remote server's 5432 port at your local machine. In that case leave localhost in the script as is.

The latter might be a cleanest and fastest option as you seem to already have the ssh access and you won't need to mess with database config that way. Moreover, you might not be permitted to change configs.