Archive

Archive for May, 2010

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

May 16, 2010 Leave a comment

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

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

Categories: Eclipse, OpenERP, Python

OpenERP Source Customization and Developement in Eclipse using PyDev

May 13, 2010 1 comment

I assume that you have read out my previous tutorial about Python Development in Eclipse, If Not then First Read out this.

After this download openERP and install in ubuntu. In Future I will discuss the issues involve in openERP Installation in Ububtu.

Now Follow the following steps;

Step 1: Open Eclipse, Click Add-> New -> Project. In new project dialog box select project from General. Click Next.

Step 2:  Assign project name Like ‘OpenerpClient’. UnCheck ‘Use Default Location’ and click Browse.

Step 3: In Browse diolog box select openerp-client folder. In my case openerp-client folder path is /opt/openerp-client and click OK.

Step 5: Click Finish Now you are ready to run openerp-client from eclipse.

Step 6: In Eclipse package explorer, open the list of openerpClient-> open Bin -> Right Click on openerp-client.py -> Mouse Over ‘Run As’ -> Select ‘Python Run’.

Step 7: OpenERP-Client successfully run from eclipse. Now you can customize or make change in openerp-client. Even you can develop new module.

Step 8: Similarly add projects of openerp-server and openerp-web in eclipse.

step 9: openerp-server already runs on startup. So first remove openerp-server on start up then run in eclipse.

Categories: Eclipse, OpenERP, Python

Open ERP: The Python Web Services (XMP-RPC Based)

May 12, 2010 2 comments

Open ERP used XMP-RPC communication Protocol using Python language. XML-RPC works by sending a HTTP request to a XML-RPC based server and XML-RPC based client want to call a remote method. Python provide two classes 1st: SimpleXMLRPCServer and 2nd: xmlrpclib. below is an example to understand how its work.

Step 1: Create a folder in your home directory having name ‘PythonXMLRPC’.

Step 2: Create python file having name ‘ServerScript.py’ in ‘PythonXMLRPC’ folder.

Step 3: Paste the below code in ServerScript.py:

# Code starts from here

import SimpleXMLRPCServer
server = SimpleXMLRPCServer.SimpleXMLRPCServer((“localhost”, 8888))
def Sum(val1, val2):
#Note: Remove prefix dots from below two line and insert a single tab or minimum four spaces
….print “PrintMsg Call from Client …… ”
….return val1+val2
server.register_function(Sum,’IntSum’)
#Go into the main listener loop
print “Listening on port 8888”
server.serve_forever()

# Code Ends here

Step 4: Run ServerScript.py in terminal like $ Python ServerScript.py. Now XML-RPC Server is running and listening at 8888 port.

Step 5: Create python file having name ‘ClientScript.py’ in ‘PythonXMLRPC’ folder.

Step 6: Paste the below code in ClientScript.py.

# Code Starts Here

import xmlrpclib
server = xmlrpclib.ServerProxy(“http://localhost:8888”)
print “Please Enter two integers value:”
integer1=raw_input(“Enter Value One: “)
integer1=int(integer1)
integer2=raw_input(“Enter Value One: “)
integer2=int(integer2)
print “Result from Server is : “, server.IntSum(integer1,integer2)

# Code End Here

Step 7: Run ClientScript.py in terminal like $ python ClientScript.py.

Thanks

Categories: OpenERP, Python