PostgreSQL Python Connector

Introduction

"PostgreSQL Python" connector enables Python programs to access PostgreSQL databases, the connector uses an API which is compliant with the Python Database API Specification.

There are several Python drivers for PostgreSQL. Here is a list of drivers :

Psycopg is the most popular PostgreSQL adapter for the Python programming language. At its core, it fully implements the Python DB API 2.0 specifications. Several extensions allow access to many of the features offered by PostgreSQL.

Psycopg is released under the terms of the GNU Lesser General Public License, allowing use of both free and proprietary software.

Psycopg features :
Psycopg is written mostly in C and wraps the libpq library with the result of being both fast and secure.

• Supports Python versions from 2.5 to 3.4.
• Supports PostgreSQL versions from 7.4 to 9.4.
• Fully compliant implementation of the Python DB API specification for database adapters.
• Thread-safe: threads can use different connections or share the same connection.
• Asynchronous nonblocking I/O also integrated with coroutine-based libraries.
• Adaptation of many Python objects to database type: tuples to records, lists to an array, dictionaries to hstore, flexible JSON support.
• Extendible with new adapters to convert Python objects to SQL syntax and type casters to convert PostgreSQL types back into Python objects.
• Server-side cursors.
• COPY support.
• Large objects support.
• Can send and receive asynchronous notification.
• Support for two-phase commit.

Download and installation

The python connector runs on any platform where Python is installed. Python comes preloaded on many Linux distribution or Unix-like systems, such as Mac OS X and FreeBSD.

Install from a package

Linux:
On Debian, Ubuntu and other deb-based distributions execute the following command to install the package with all its dependencies.

sudo apt-get install python-psycopg2

Mac OS X :
Psycopg is available as a fink package in the unstable tree: you may install it with:

fink install psycopg2-py27

The library is also available on MacPorts try :

sudo port install py27-psycopg2

Install Python Connector on Microsoft Windows

Before installing the Connector on Windows :

• To complete the installation operation, root or administrator privileges may be required.
• Before installing the Python distribution on your system, enable python.exe in Windows %PATH% setting ( path environment variable) or manually add it to, if it is not enabled.

Release Files:

Python psycopg2 module APIs

The basic Psycopg usage is common to all the database adapters implementing the DB API 2.0 protocol.

Connect to an existing database:

import psycopg2
conn = psycopg2.connect(database="postgres", user="postgres", password="datasoft123", host="127.0.0.1", port="5432")
print("Database Connected....")

Output:

Database Connected....

Python Command to open a cursor to perform database operations :

cur = conn.cursor()

Creates a new table:

import psycopg2
conn = psycopg2.connect(database="postgres", user="postgres", password="datasoft123", host="127.0.0.1", port="5432")
print("Database Connected....")
cur = conn.cursor()
cur.execute("CREATE TABLE test(id serial PRIMARY KEY, sname CHAR(50), roll_num integer);")
print("Table Created....")
conn.commit()
conn.close()

Output :

Database Connected....
Table Created....

Insert some records:

 import psycopg2
conn = psycopg2.connect(database="postgres", user="postgres", password="datasoft123", host="127.0.0.1", port="5432")
cur = conn.cursor()
cur.execute("INSERT INTO test (id, sname, roll_num) \
      VALUES (10, 'Sara', 3)");

cur.execute("INSERT INTO test (id, sname, roll_num) \
      VALUES (20, 'Ema', 4)");

cur.execute("INSERT INTO test (id, sname, roll_num) \
      VALUES (30, 'Drabir', 2)");

cur.execute("INSERT INTO test (id, sname, roll_num) \
      VALUES (40, 'Surya', 1)");

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

Output :

Records created successfully.

Select all the recods:

import psycopg2
conn = psycopg2.connect(database="postgres", user="postgres", password="datasoft123", host="127.0.0.1", port="5432")
cur = conn.cursor()
cur.execute("SELECT id, sname, roll_num from test")
print("ID   Roll No. Student Name")
print("--------------------------") 
rows = cur.fetchall()
for row in rows:
   print(row[0],' ',str(row[2]).strip(),'      ',row[1].strip())

conn.close()

Output :

ID   Roll No. Student Name
--------------------------
10   3        Sara
20   4        Ema
30   2        Drabir
40   1        Surya

Updates columns of existing rows in the named table with new values :

import psycopg2
conn = psycopg2.connect(database="postgres", user="postgres", password="datasoft123", host="127.0.0.1", port="5432")
cur = conn.cursor()
cur.execute("UPDATE test set roll_num = 5 where ID=10")
conn.commit
cur.execute("SELECT id, sname, roll_num from test")
print("ID   Roll No. Student Name")
print("--------------------------") 
rows = cur.fetchall()
for row in rows:
   print(row[0],' ',str(row[2]).strip(),'      ',row[1].strip())

conn.close()

Output :

ID   Roll No. Student Name
--------------------------
20   4        Ema
30   2        Drabir
40   1        Surya
10   5        Sara

Delete Record(s):

import psycopg2
conn = psycopg2.connect(database="postgres", user="postgres", password="datasoft123", host="127.0.0.1", port="5432")
cur = conn.cursor()
cur.execute("DELETE from test where ID=10;")
conn.commit
cur.execute("SELECT id, sname, roll_num from test")
print("ID   Roll No. Student Name")
print("--------------------------") 
rows = cur.fetchall()
for row in rows:
   print(row[0],' ',str(row[2]).strip(),'      ',row[1].strip())

conn.close()

Output :

ID   Roll No. Student Name
--------------------------
20   4        Ema
30   2        Drabir
40   1        Surya