Use of Python DB APIs (psycopg2) for a PostgreSQL Database

Python provide a module psycopg2 for interaction with postgresql. In this document, trying to provide some basic concepts that how python connection establish, how selection, insertion, updation and deletion performs in python with postgresql. Install python, postgresql, pgadmin (if required) and psycopg2.

Step 1: create a database file having name ‘dbtest’ in postgresql using pgadmin GUI interface or from command line using below commands.
mohsin@mohsin-desktop $: su postgres
password
postgres@mohsin-desktop $ createcb dbtest

I suppose that u will used postgres user for database creation or you can use any other postgresql database user that u have created for postgresql interaction

Step 2: create a table in dbtest having name ‘tbl’ with two columns one no (integer type) and other name (varchar type)
Create table tbl (no integer, name varchar)

Step 3: Create a python file having name ‘dbProgram.py’ in your home directory and write a header like
#! /usr/bin/python2.6
import psycopg2

Step 4: Connect method of psycopg2 used to establish a connection with posgresql database. Its parameter contains host, database, user and passwords etc. For detail read out documentation of python. Connect method returns a object that provide to initiate a cursor object and connection close operation etc. like
con = psycopg2.connect (database=”dbtest”,  user=”postgres”, password=”……..” )

Next con is user define reference of the object return by pshcopg2.connect method.

Step 5: Initiate a cursor object and store the reference in user define cursor variable by calling con.cursor method of con object. Like
cursor = con.cursor()

Step 6: Now we are ready to query a table having name tbl of dbtest postgresql database. Cursor object provide a execute method to runs any sql query from python. Take input of two values for table tbl from user one is no and other is name. Now Provide a sql query to execute method with two user input values like.
no=int(input(“Enter Emplyee No (Integer Value): “))
name=str(raw_input(“Enter Employee Name (String Value): “))
cursor.execute(“insert into tbl values (“+str(no)+”,’”+name+”‘)”)

Step 7: finally set these changes to actual dbtest database by using the commit method of psycopg2.connet like.
con.commit()

Step 8: Check these rows providing select sql query to execute method of cursor Object and use fetchall method or cursor object to return the rows of table tbl in the form of list data structure like
cursor.execute(“Select no, name from tbl”)
rows=cursor.fetchall()
for row in rows:
….print row

Step 9: similarly we can provide update and delete query to execute method to update and delete a record from table tbl of tbtest postgresql database and commit changes like
Update code:
no=int(input(“Select the Record no for updation: “))
name=str(raw_input(“Enter the New name that want to change: “))
cursor.execute(“update tbl set name=’”+ name +”‘ where no=”+str(no)+”")
con.commit()

Delete Code:
no=int(input(“Select Record No for deletion: “))
cursor.execute(“delete from tbl where no=” + str(no))
con.commit()

Step 10: use close method to free reference variable of connect and cursor like
cursor.close()
con.close()

Step 11: Below is full working dbProgram.py read it and compile it using python. Note remove ….  from below code that are used for indentation.

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

#! /usr/bin/python2.6
# Last Update 16-05-2010
# Author Mohsin Yaseen

# program name dbProgram.py # develop in python, database used postgressql and psycopg2 as Pyhtonmodule for postgres
# simple table used with two columns employee no and name. # program explain simple selection, insertion, deletion and updationin python with postgresql

import psycopg2

print “\n…. Demonstration of Python DB APIs for Postgresql ….\n”

con = psycopg2.connect (database=”dbtest”,  user=”postgres”, password=”shah123″ )
cursor = con.cursor()

def main():
….printingRows()
….options()
….while optionsSelections():
……..options()
#end of main method

def printingRows():
….print “\nResult: Data in table”
….cursor.execute(“Select no, name from tbl”)
….rows=cursor.fetchall()
….for row in rows:
….….print row
#end of printingRows Method

def optionsSelections():
….option = raw_input(“Waiting For Value: “)
….if option==”1″:
….….insertingNewRow()
….….return 1
….elif option==”2″:
….….updatingRow()
….….return 1
….elif option==”3″:
….….deletingRow()
….….return 1
….elif option==”4″:
….….print “Program Going to Closed”
….….return 0
….else:
….….print “Invalid option selected please try again.”
….….return 1
#end of optionsSelections method

def options():
….print “\n… Follow the intructions …\n”
….print “Enter 1 for Insert new record”
….print “Enter 2 for Update a record”
….print “Enter 3 for Delete a record”
….print “Enter 4 for Exit”
#end of options method

def insertingNewRow():
….no=int(input(“Enter Emplyee No (Integer Value): “))
….name=str(raw_input(“Enter Employee Name (String Value): “))
….cursor.execute(“insert into tbl values (“+str(no)+”,’”+name+”‘)”)
….con.commit()
….print “Value inserted successfully”
….printingRows()
#end of insertingRows method

def updatingRow():
….no=int(input(“Select the Record no for updation: “))
….name=str(raw_input(“Enter the New name that want to change:”))
…. cursor.execute(“update tbl set name=’”+ name +”‘ where no=”+str(no)+”")
…. con.commit()
….print “Record Updated Successfully”
….printingRows()
#end of updatingRow method

def deletingRow():
….no=int(input(“Select Record No for deletion: “))
….cursor.execute(“delete from tbl where no=” + str(no))
….con.commit()
….print “Record Deleted Successfully”
….printingRows()
#end of deletingRow method

main()
cursor.close()
con.close()

#END of dbPython module

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

About these ads