How to Read Data from Odoo Database and Write Into a CSV File Using Python

Abid Patel
06-Nov-2024 Updated : 06-Nov-2024

Learn how to read data from the Odoo database and write it into a CSV file using a simple Python script. Export data for reports and analysis with ease.

How to Read Data from Odoo Database and Write Into a CSV File Using Python

Working in Odoo often needs getting data from the system. You also perform various operations on it. An example of a task is exporting data from Odoo database. You then save it to a CSV file. This can be useful for many things. Generating reports data analysis and backing up critical information.

In this post we guide you on how to read data from the Odoo database. Then you write it into a CSV file. We use simple Python script for this. You can do this using Odoo's ORM methods. Python built-in CSV module is also used.

What is a CSV File?

CSV means Comma Separated Values. It is a format that sees wide use for storing tabular data. In this format each line stands for a row. Each value in that row is separated by a comma. This format is simple and human-readable. It is also easy to use in data analysis tools. Examples include Excel or Google Sheets.

Steps to Export Data from Odoo to CSV

Step 1: Setting Up Your Python Environment

Begin by making certain Python is installed on your system. Odoo environment for scripting also needs to be set up. You can achieve this by running script within Odoo environment. You can alternately configure the environment for use of Odoo's libraries.

Make sure necessary libraries are present. Among these is csv module. It comes as part of Python by default.

Step 2: Connect to the Odoo Database

You need to connect to Odoo environment. After that you should authenticate. This is to access the data. You can use the xmlrpc client for this. It is used to connect to your Odoo server. A simple connection setup is shown below:

python

import xmlrpc.client

# Odoo connection settings
url = "http://your-odoo-instance-url"
db = "your-database-name"
username = "your-username"
password = "your-password"

# Set up the common RPC connection
common = xmlrpc.client.ServerProxy(f'{url}/xmlrpc/2/common')
uid = common.authenticate(db, username, password, {})

# Set up the object RPC connection
models = xmlrpc.client.ServerProxy(f'{url}/xmlrpc/2/object')

Step 3: Read Data from Odoo

Now you are connected to Odoo. You can query the database for the data you need. For example let us export data from the sale.order model. We will query the sale orders. Fields like the order reference, customer name and order date will be included.

python

# Define the model to retrieve data from
model = 'sale.order'

# Define the fields we want to extract
fields = ['name', 'partner_id', 'date_order', 'amount_total']

# Use Odoo's search_read method to fetch data
orders = models.execute_kw(db, uid, password, model, 'search_read', [], {'fields': fields})

In this example search_read is a convenient method. It retrieves records from the database and returns them as a list of dictionaries.

Step 4: Write Data to a CSV File

Next we write the data we have fetched into a CSV file. We will use Python’s csv module for this task. Here's the process:

python

import csv

# Specify the file path where the CSV will be saved
csv_file = '/path/to/your/output.csv'

# Open the CSV file in write mode
with open(csv_file, mode='w', newline='', encoding='utf-8') as file:
    writer = csv.DictWriter(file, fieldnames=fields)
    
    # Write the header (field names) to the CSV file
    writer.writeheader()
    
    # Write each order to the CSV file
    for order in orders:
        writer.writerow({
            'name': order['name'],
            'partner_id': order['partner_id'][1],  # Partner name (second element in tuple)
            'date_order': order['date_order'],
            'amount_total': order['amount_total'],
        })

In the above code:

  • ▹ DictWriter is used to write a dictionary of values to the CSV file. It ensures the correct order of columns.
  • ▹ The fieldnames parameter defines the columns that will appear in the CSV header.
  • ▹ The write() method writes the column names to the CSV file.
  • ▹ The writer.writerow() method writes the individual rows of data.

Step 5: Run the Script

After you have written script it’s time to run it. You can do this from a terminal or command line. When running this script fetches data from your Odoo instance. Then it saves this data into a CSV file. You can open this CSV file in Excel. Also in Google Sheets. Or in any other application that can support CSV files.

Conclusion

Python and Odoo's XML-RPC API make it easy to extract data from your Odoo instance and save it in a CSV file. This feature is of utmost importance when one needs to export large datasets for the purposes of reporting analysis, backups. Using few lines of code, automation is a possibility. This process can be integrated into business workflows.

This strategy can be applied to any model present in Odoo, giving users the capability to export various types of data. They can export sales orders invoices, products. The output is a format that is both easily shareable and storable. It is also suitable for analysis.

Make a Comment

Your email address will not be published. Required fields are marked *