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

How to install and configure PostgreSQL pqsl in linux?

How to install and configure PostgreSQL pqsl in linux?

This topic contains installation and configration of PostgreSQL

1. Download setup from

https://get.enterprisedb.com/postgresql/postgresql-9.5.6-1-linux-x64.run

2. Run the installer and use default options ( as root )

./postgresql-9.5.6-1-linux-x64.run

[root@oel6 tmp]# ./postgresql-9.5.6-1-linux-x64.run

----------------------------------------------------------------------------

Welcome to the PostgreSQL Setup Wizard.

----------------------------------------------------------------------------

Please specify the directory where PostgreSQL will be installed.

Installation Directory [/opt/PostgreSQL/9.5]:

----------------------------------------------------------------------------

Please select a directory under which to store your data.

Data Directory [/opt/PostgreSQL/9.5/data]:

----------------------------------------------------------------------------

Please provide a password for the database superuser (postgres). A locked Unix

user account (postgres) will be created if not present.

Password :

Retype password :

----------------------------------------------------------------------------

Please select the port number the server should listen on.

Port [5432]:

----------------------------------------------------------------------------

Advanced Options

Select the locale to be used by the new database cluster.

Locale

[1] [Default locale]

[2] aa_DJ

[3] aa_DJ.iso88591

[4] aa_DJ.utf8

[5] aa_ER

[534] nso_ZA.utf8

[700] xh_ZA.iso88591

[718] zu_ZA.utf8

Please choose an option [1] : 1

----------------------------------------------------------------------------

Setup is now ready to begin installing PostgreSQL on your computer.

Do you want to continue? [Y/n]: Y

----------------------------------------------------------------------------

Please wait while Setup installs PostgreSQL on your computer.

Installing

0% ______________ 50% ______________ 100%

#########################################

----------------------------------------------------------------------------

Setup has finished installing PostgreSQL on your computer.

2. Reboot the OS

Verify all the process are coming or not

reboot

3. After reboot verify the port and process

netstat -anp | grep 5432

[root@oel6 ~]# netstat -anp | grep 5432

tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 1432/postgres

tcp 0 0 :::5432 :::* LISTEN 1432/postgres

unix 2 [ ACC ] STREAM LISTENING 10845 1432/postgres /tmp/.s.PGSQL.5432

[root@oel6 ~]#

4. If DB is running , then configure setup for postgres user

chown -R postgres:postgres /opt

5.Login as postgres user and edit .bashrc file

su - postgres

vi .bashrc

Add

…....

PATH=$PATH:/opt/PostgreSQL/9.5/bin/

export PATH

…..

Source .bashrc or re login

6.Create DB

createdb -h localhost -p 5432 -U postgres testdb

Enter the password

also testdb as new Database

6.Get the sql promt

psql

enter password

/l

To display the Dbs

/c testdb;

To accesss/enter the DB

SQL opertaions :

-bash-4.1$ psql

Password:

psql.bin (9.5.6)

Type "help" for help.

postgres=# \l

List of databases

Name | Owner | Encoding | Collate | Ctype | Access privileges

-----------+----------+----------+-------------+-------------+-----------------------

postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |

template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +

| | | | | postgres=CTc/postgres

template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +

| | | | | postgres=CTc/postgres

testdb | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |

(4 rows)

postgres=# \c testdb;

You are now connected to database "testdb" as user "postgres".

testdb=# CREATE TABLE COMPANY(

testdb(# ID INT PRIMARY KEY NOT NULL,

testdb(# NAME TEXT NOT NULL,

testdb(# AGE INT NOT NULL,

testdb(# ADDRESS CHAR(50),

testdb(# SALARY REAL

testdb(# );

CREATE TABLE

testdb=# select * from COMPANY;

id | name | age | address | salary

----+------+-----+---------+--------

(0 rows)

^

testdb=# INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00 );

INSERT 0 1

testdb=# select * from COMPANY;

id | name | age | address | salary

----+------+-----+----------------------------------------------------+--------

1 | Paul | 32 | California | 20000

(1 row)

testdb=#