How to connect to PostgreSQL pqsl using python?

How to connect to PostgreSQL pqsl using python?

This topic contains psycopg2 installation and and configration

1. In client install psycopg2 libaries

yum install python-devel postgresql-devel

2. Client : Install psycopg2 usign pip

pip install psycopg2

3. Server : Edit pg_hba.conf to accept the client request

vi pg_hba.conf

host all all 0.0.0.0/0 md5

This will allow connection from all server . It can be edited to host specific

4 . Restart DB

5. Client : SELECT example

#!/bin/python

import psycopg2

try:

conn = psycopg2.connect("dbname='testdb' user='postgres' host='IP' password='welcome1'")

cursor = conn.cursor()

cursor.execute("SELECT * FROM COMPANY;")

records = cursor.fetchall()

for row in records:

print "ID = ", row[0]

print "NAME = ", row[1]

print "AGE = ", row[2]

print "address = ", row[3], "\n"

except Exception as e:

print "I am unable to connect to the database"

print e

6. Client : Update example

#!/bin/python

import psycopg2

SQL=" INSERT INTO COMPANY ( id, name, age, address, salary ) VALUES (2, 'Allen', 25, 'Texas', 15000.00 )";

try:

conn = psycopg2.connect("dbname='testdb' user='postgres' host='IP' password='welcome1'")

cursor = conn.cursor()

cursor.execute(SQL)

conn.commit()

conn.close()

except Exception as e:

print "I am unable to connect to the database"

print e