---

Installation

The PostgreSQL can be coordinated with Python utilizing psycopg2 module. sycopg2 is a PostgreSQL information base connector for the Python programming language. psycopg2 was composed with the point of being little and quick, and steady as a stone. You don't have to introduce this module independently on the grounds that it is transported, of course, alongside Python rendition 2.5.x onwards. 

In the event that you don't have it introduced on your machine, at that point you can utilize yum order to introduce it as follows − 

$yum install python-psycopg2

 To utilize psycopg2 module, you should initially make a Connection object that addresses the information base and afterward alternatively you can make cursor object which will help you in executing all the SQL explanations. 

Python psycopg2 module APIs

Coming up next are significant psycopg2 module schedules, which can get the job done your necessity to work with PostgreSQL data set from your Python program. On the off chance that you are searching for a more complex application, at that point you can investigate Python psycopg2 module's true documentation. 

S. No.	API & Description
1	psycopg2.connect(database="testdb", user="postgres", password="cohondob", host="127.0.0.1", port="5432") This API opens a connection to the PostgreSQL database. If database is opened successfully, it returns a connection object.
2	connection.cursor() This routine creates a cursor which will be used throughout of your database programming with Python.
3	cursor.execute(sql [, optional parameters]) This routine executes an SQL statement. The SQL statement may be parameterized (i.e., placeholders instead of SQL literals). The psycopg2 module supports placeholder using %s sign For example:cursor.execute("insert into people values (%s, %s)", (who, age))
4	cursor.executemany(sql, seq_of_parameters) This routine executes an SQL command against all parameter sequences or mappings found in the sequence sql.
5	cursor.callproc(procname[, parameters]) This routine executes a stored database procedure with the given name. The sequence of parameters must contain one entry for each argument that the procedure expects.
6	cursor.rowcount This read-only attribute which returns the total number of database rows that have been modified, inserted, or deleted by the last last execute*().
7	connection.commit() This method commits the current transaction. If you do not call this method, anything you did since the last call to commit() is not visible from other database connections.
8	connection.rollback() This method rolls back any changes to the database since the last call to commit().
9	connection.close() This method closes the database connection. Note that this does not automatically call commit(). If you just close your database connection without calling commit() first, your changes will be lost!
10	cursor.fetchone() This method fetches the next row of a query result set, returning a single sequence, or None when no more data is available.
11	cursor.fetchmany([size=cursor.arraysize]) This routine fetches the next set of rows of a query result, returning a list. An empty list is returned when no more rows are available. The method tries to fetch as many rows as indicated by the size parameter.
12	cursor.fetchall() This routine fetches all (remaining) rows of a query result, returning a list. An empty list is returned when no rows are available.

Connecting to Database

The accompanying Python code tells the best way to associate with a current data set. On the off chance that the data set doesn't exist, at that point it will be made lastly an information base article will be returned. 

#!/usr/bin/python

import psycopg2

conn = psycopg2.connect(database="testdb", user = "postgres", password = "pass123", host = "127.0.0.1", port = "5432")

print "Opened database successfully"

Here, you can likewise supply data set testdb as name and in the event that information base is effectively opened, at that point it will give the accompanying message − 

Open database successfully

Create a Table 

The accompanying Python program will be utilized to make a table in recently made information base − 

#!/usr/bin/python

import psycopg2

conn = psycopg2.connect(database = "testdb", user = "postgres", password = "pass123", host = "127.0.0.1", port = "5432")
print "Opened database successfully"

cur = conn.cursor()
cur.execute('''CREATE TABLE COMPANY
      (ID INT PRIMARY KEY     NOT NULL,
      NAME           TEXT    NOT NULL,
      AGE            INT     NOT NULL,
      ADDRESS        CHAR(50),
      SALARY         REAL);''')
print "Table created successfully"

conn.commit()
conn.close()

At the point when the above given program is executed, it will make COMPANY table in your test.db and it will show the accompanying messages − 

Opened database successfully
Table created successfully

INSERT Operation

The accompanying Python program shows how we can make records in our COMPANY table made in the above model − 

#!/usr/bin/python

import psycopg2

conn = psycopg2.connect(database = "testdb", user = "postgres", password = "pass123", host = "127.0.0.1", port = "5432")
print "Opened database successfully"

cur = conn.cursor()

cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (1, 'Paul', 32, 'California', 20000.00 )");

cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (2, 'Allen', 25, 'Texas', 15000.00 )");

cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (3, 'Teddy', 23, 'Norway', 20000.00 )");

cur.execute("INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) \
      VALUES (4, 'Mark', 25, 'Rich-Mond ', 65000.00 )");

conn.commit()
print "Records created successfully";
conn.close()

At the point when the above given program is executed, it will make given records in COMPANY table and will show the accompanying two lines − 

Opened database successfully
Records created successfully

SELECT Operation

The accompanying Python program shows how we can bring and show records from our COMPANY table made in the above model − 

#!/usr/bin/python

import psycopg2

conn = psycopg2.connect(database = "testdb", user = "postgres", password = "pass123", host = "127.0.0.1", port = "5432")
print "Opened database successfully"

cur = conn.cursor()

cur.execute("SELECT id, name, address, salary  from COMPANY")
rows = cur.fetchall()
for row in rows:
   print "ID = ", row[0]
   print "NAME = ", row[1]
   print "ADDRESS = ", row[2]
   print "SALARY = ", row[3], "\n"

print "Operation done successfully";
conn.close()

At the point when the above given program is executed, it will create the accompanying outcome − 

Opened database successfully
ID =  1
NAME =  Paul
ADDRESS =  California
SALARY =  20000.0

ID =  2
NAME =  Allen
ADDRESS =  Texas
SALARY =  15000.0

ID =  3
NAME =  Teddy
ADDRESS =  Norway
SALARY =  20000.0

ID =  4
NAME =  Mark
ADDRESS =  Rich-Mond
SALARY =  65000.0

Operation done successfully

UPDATE Operation

The accompanying Python code shows how we can utilize the UPDATE explanation to refresh any record and afterward bring and show refreshed records from our COMPANY table − 

#!/usr/bin/python

import psycopg2

conn = psycopg2.connect(database = "testdb", user = "postgres", password = "pass123", host = "127.0.0.1", port = "5432")
print "Opened database successfully"

cur = conn.cursor()

cur.execute("UPDATE COMPANY set SALARY = 25000.00 where ID = 1")
conn.commit()
print "Total number of rows updated :", cur.rowcount

cur.execute("SELECT id, name, address, salary  from COMPANY")
rows = cur.fetchall()
for row in rows:
   print "ID = ", row[0]
   print "NAME = ", row[1]
   print "ADDRESS = ", row[2]
   print "SALARY = ", row[3], "\n"

print "Operation done successfully";
conn.close()

At the point when the above given program is executed, it will create the accompanying outcome − 

Opened database successfully
Total number of rows updated : 1
ID =  1
NAME =  Paul
ADDRESS =  California
SALARY =  25000.0

ID =  2
NAME =  Allen
ADDRESS =  Texas
SALARY =  15000.0

ID =  3
NAME =  Teddy
ADDRESS =  Norway
SALARY =  20000.0

ID =  4
NAME =  Mark
ADDRESS =  Rich-Mond
SALARY =  65000.0

Operation done successfully

DELETE Operation

The accompanying Python code shows how we can utilize the DELETE articulation to erase any record and afterward get and show the leftover records from our COMPANY table − 

#!/usr/bin/python

import psycopg2

conn = psycopg2.connect(database = "testdb", user = "postgres", password = "pass123", host = "127.0.0.1", port = "5432")
print "Opened database successfully"

cur = conn.cursor()

cur.execute("DELETE from COMPANY where ID=2;")
conn.commit()
print "Total number of rows deleted :", cur.rowcount

cur.execute("SELECT id, name, address, salary  from COMPANY")
rows = cur.fetchall()
for row in rows:
   print "ID = ", row[0]
   print "NAME = ", row[1]
   print "ADDRESS = ", row[2]
   print "SALARY = ", row[3], "\n"

print "Operation done successfully";
conn.close()

At the point when the above given program is executed, it will deliver the accompanying outcome − 

Opened database successfully
Total number of rows deleted : 1
ID =  1
NAME =  Paul
ADDRESS =  California
SALARY =  20000.0

ID =  3
NAME =  Teddy
ADDRESS =  Norway
SALARY =  20000.0

ID =  4
NAME =  Mark
ADDRESS =  Rich-Mond
SALARY =  65000.0

Operation done successfully