Saturday, October 4, 2014

Connecting to Database in Python using psycopg2

In Python, we can connect to database using psycopg2. To install psycopg2, follow the link here. The link contains the details of how to install psycopg2 in different operating systems. The following example is validated in  python 2.7. This example connects to a postgres database but you can replace it with other database. It will fetch the record from a users table having field email, first_name, last_name, role and status.

# -*- coding: utf-8 -*-
# installation of psycopg2

import psycopg2

connection = None

    # Provide the connection properties to database. 
    # These are same as used in any database connection client.
    connection = psycopg2.connect(host='localhost', port = '5432', database='springHibernateWebApp', user='postgres',  password='postgres') 
    #Execute the statement on database
    cursor = connection.cursor()
    cursor.execute("select email,first_name, last_name, role, status from users ")          
    #Fetch and iterate over the result.
    rows = cursor.fetchall()
    for row in rows:
        email = row[0];
        firstName = row[1];
        lastName = row[2];
        role = row[3];
        status = row[4];
        print 'User details ' + 'email: ' + email
#catch if  exception happens
except psycopg2.DatabaseError, exception:
    print 'Exception  %s' % exception    
#Abnormal or normal, release the connection
    if connection:

No comments:

Post a Comment