Archive for the ‘ETL’ Category

Importing products with web-services in OpenERP

Python logo

Image via Wikipedia

Yesterday someone at the OpenERP forum asked me for an example of how to import product data into OpenERP, so here it is, below you will find an example of a Python script for loading basic product data.

#!/usr/bin/env python
# coding: utf-8

import xmlrpclib
import csv

username = ‘admin’ #the user
pwd = ‘pwd_admin’ #the password of the user
dbname = ‘blog_db’ #the database

# Get the uid
sock_common = xmlrpclib.ServerProxy (‘http://localhost:8069/xmlrpc/common’)
uid = sock_common.login(dbname, username, pwd)

#replace localhost with the address of the server
sock = xmlrpclib.ServerProxy(‘http://localhost:8069/xmlrpc/object’)

#load categories first
filename = “categories.csv”
reader = csv.reader(open(filename,”rb”))
for row in reader:
category = {
‘name’: row[1],
‘active’: True,
category_id = sock.execute(dbname, uid, pwd, ‘res.partner.category’, ‘create’, category)
print category_id

print “End categories load”

filename = “product_template.csv”
reader = csv.reader(open(filename,”rb”))
for row in reader:
product_template = {
‘name’: row[0].rstrip(),
template_id = sock.execute(dbname, uid, pwd, ‘product.template’, ‘create’, product_template)
print template_id

product_product = {
‘default_code’: row[0].rstrip(),
‘active’: True,
product_id = sock.execute(dbname,uid,pwd,’product.product’,’create’,product_product)
print product_product

print “End product load”

This is a simple script you can execute from the command line. In summary, first you need to create the categories and then you need to create the product.template objects and product.product objects. If you have any question about this, please let me know.



Loading data in OpenERP with XML-RPC Web services

Architectural elements involved in the XML-RPC.

Image via Wikipedia

Loading data from an external source into OpenERP is quite straight-forward. Before trying ETL tools (such as Talend and Kettle), you should try two things first. Understand OpenERP’s object model (you can do it in the customization menu or by reading the code), and then try it with Python.

Trying the data load with Python is quite easy. First, open the Python prompt. Then try an example, the best example is OpenERP’s XML-RPC Web services documentation itself. I mean, start your openerp-server, then line by line try the example. You will see how it works and how easy it is to load data with web services. Then, I would suggest reading the CSV Python module documentation, which explains how to read a CSV file. After that, you only need to put the pieces together, create a Python program that reads the information from a CSV file (which was exported from LibreOffice) and then l0ad that data into OpenERP with web services.


OpenERP, Kettle and TerminatOOOR

February 10, 2011 Leave a comment
The blue/white elephant logo of the PostgreSQL...

Image via Wikipedia

Yesterday I have been testing Kettle and OpenERP. Other OpenERP consultants recommended me to use TerminatOOR in order to update the OpenERP data model. I was intrigued by it and yesterday I gave it a try. So this is a summary of my yesterday’s experiences with Kettle and TerminatOOR.

So when it comes to dealing with OpenERP data model, pretty much you do two things. You either extract data or load data. Importing is pretty straightforward, I was able to connect to the OpenERP server with the Table Input step and extract data with JDBC (don’t forget to install the PostgreSQL JDBC driver). This might not be the  best idea for pulling data from the system but it is a quite intuitive and efficient one. Just be sure you are pulling the right data, double check against the system. And don’t forget to tell your DBA to set the database permissions so you don’t experience any undesired visit to your database.

The real challenge lies in updating the data. You can update the data with the Table Output step, but this approach has several drawbacks. The first one is security, OpenERP can not enforce the security rules with this approach since it bypasses the application security layer. Second one is you might end up with inconsistencies in the database. Don’t forget that objects in OpenERP might update more than one table everytime they are updated/created. So if you update the OpenERP tables with a database tool, chances are you will run into consistency problems sooner or later (actually sooner than you think).

This is where TerminatOOOR comes into the scene. TerminatOOOR is (other than the movie where Arnold Schwarzenegger was smartly cast as a robot) a Kettle Step that provides a connector between OpenERP and Kettle. TerminatOOOR is a project of the founder of the brazilian society Akretion. I will not go into the details on how to install it and configure it, you can find it in the project wiki. The wiki is quite complete and helps you figure out how to use the connector and test it. And there is more documentation on the way.

Before finishing this post, I would like to make a couple of suggestions if you are trying TerminatOOOR for the first time. First, learn to use Kettle before trying the connector. Learning ETL tools and how they are used and their potential takes more than one day, so devote at least one day to learn the ropes of developing Transformation in the Pentahoo tool. Don’t forget that you will update OpenERP with Kettle, not with  TerminatOOOR. The latter will connect Kettle and OpenERP. There are tutorials online for learning Kettle and it is quite an intuitive tool to learn. But if you want to learn the ropes of developing the scripts used by TerminatOOOR, you can check the OOOR page.

Second, study the examples that come with the connector (you can find them in the samples directory). Open them and try them. They will show you how TerminatOOOR works and how it updates OpenERP. Third, don’t forget that openerp-server has to be running! It took me a while to figure out what was wrong with my transformations and the main problem is, I did not start the openerp-server daemon, therefore Kettle was not able to call the webservices in the system.

And last but not least, TerminatOOOR comes with a very handy Ruby Test Console. Try it and debug your Ruby scripts in the console before running them with Kettle. It is very handy for debugging connection problems with OpenERP (it saved me lots of hours).