Odoo is a suite of open source business apps that cover all your company needs: CRM, eCommerce, accounting, inventory, point of sale, project management, etc.

This article provides an overview to get started with the Odoo connector in Peliqan. Please contact support if you have any additional questions or remarks.

Contents
Connect Odoo
Authentication
Database name
Sync additional standard and/or custom models
Odoo model explorer
Sync custom fields
Custom pipelines for Odoo
Odoo data model
Check completeness of data in DWH
Resync an Odoo table
Remove deleted records from the data warehouse
Writeback to Odoo from Python scripts
Basic writeback functions
Generic functions
Applying multiple filters in Odoo
Custom fields
Fields of type One2Many
Fields of type Many2Many and One2Many
Writing to Odoo in batch
Working with product template & attributes

Connect Odoo

In Peliqan, go to Connections > Add Connection > Select Odoo in the list > Enter the details of your Odoo instance.

Authentication

You can choose to authenticate with your username and password or with your username and an API key.

If 2FA (2-factor authentication) is enabled on your Odoo instance, you have to use an API key.

To create an API key in Odoo go to > My profile > Account Security > API keys.

Database name

If you don’t know your database name, you can make an unauthenticated API call, e.g. in Postman, to list your database names:

GET https://{domain}/web/database/list
GET https://{sub_domain}.odoo.com/web/database/list   (for Odoo.sh)

Headers:
Accept: application/json
Content-Type: application/json

Body:
{}

The body must contain an empty JSON object !

Or you can reach out to Peliqan Support (support@peliqan.io) to retrieve the name of your Odoo database.

Sync additional standard and/or custom models

You can include additional modules in the ETL pipeline, by adding a comma-separated list with the names of your standard and/or custom models, in the field “Additional models”. These models will now also be synced to the data warehouse as tables.

You can find the model names by doing an initial sync of table “models” (under Advanced in the connect form):

The models table contains a list of all your Odoo models:

Odoo model explorer

You can also run a script to get a list of models, and all fields of a selected model.

‣

Click to expand the script

Sync custom fields

Custom fields will automatically be added as columns in the tables in the data warehouse. If you add new custom fields in Odoo, perform a “Full resync” to include them in the data warehouse.

Custom pipelines for Odoo

Peliqan will sync all fields of the selected models, except for calculated fields:

field property store=True: field is included in Peliqan
field property store=False: field is not included in Peliqan (calculated field)

Use below custom pipeline to sync a table with a custom selection of fields (e.g. all fields except a given set of fields to exclude because they cause errors).

‣

Click here to see an example custom pipeline for Odoo

Odoo data model

Use the below data app to explore models and fields in your Odoo instance.

‣

Odoo data model explorer

Use the below custom pipeline script, to sync the fields of a selected model, as well as the options of “Selection” fields, to tables in the data warehouse

‣

Click to expand script

Check completeness of data in DWH

The below script will compare the number of records (count) in the data warehouse with the actual count in Odoo.

‣

Click here to expand script

Resync an Odoo table

Resync an Odoo table, e.g. when fields were added in Odoo (schema is updated), using the below script.

‣

Click to expand script

Remove deleted records from the data warehouse

If records are deleted in Odoo, they are not automatically deleted in the data warehouse. Use below custom pipeline to delete records in the data warehouse, when they are no longer present in Odoo.

‣

Click to expand script

Writeback to Odoo from Python scripts

Basic writeback functions

Example to add a Product to Odoo:

odoo_api = pq.connect('Odoo')

product = {
    'name': "My new product"
}
result = odoo_api.add('product', product)
st.json(result)

Example to update a Product in Odoo:

odoo_api = pq.connect('Odoo')

product = {
    'id': 123,
    'name': "My new product updated"
}
result = odoo_api.update('product', product)
st.json(result)

Generic functions

These functions can be used to work with any model in Odoo, a standard or custom model.

Add object from a given model:

odoo_api = pq.connect('Odoo')

object = {
    "model": "product.template", # this could be a custom model
    "payload": [
            {
                "name": "My new object"
            }
        ],
    "additional_params": {}
    }

#result = odoo_api.add('object', object)
#st.json(result)

Update object from a given model:

object = {
    "model": "product.template", # this could be a custom model
    "payload": [ 
            [123],               # id of the object to update
            {
                "name": "My new product updated",
            }
        ],
    "additional_params": {}
    }
result = odoo_api.update('object', object)
st.json(result)

Execute a custom method or action on an object, for example Confirm a quotation to turn it into a sales order:

odoo_api = pq.connect('Odoo')
object_method = {
    'model': "sale.order",
    'method': "action_confirm",
    'payload': [[17]], # id of the quotation to update
    'additional_params': {}
}
result = odoo_api.update('object_method', object_method)
st.json(result)

Make a generic API call to Odoo:

odoo_api = pq.connect('Odoo')
request_payload = {
    'payload': [[['id', '=', '1']]],
    'odoo_model': "res.partner",
    'odoo_method': "search_read",
    'additional_params': {}
}
result = odoo_api.apicall(path = '', method = 'POST', **request_payload)
st.json(result)

Get objects using paging, and a fields list (note that some Odoo models require a fields list to be given in order to get a response from the API):

Applying multiple filters in Odoo

→ One can use generic get_object endpoint which can help applying multiple filters in on API request with dynamically defining the Model name.

→ this will return the first page only, max. 50 results

→ Please find below the example of working with multiple filters:

Custom fields

Set value of a custom field:

odoo_api = pq.connect('Odoo')

product = {
    'name': "My new product",
    "x_my_custom_field": "Some value"
}
result = odoo_api.add('product', product)
st.json(result)

Fields of type One2Many

Examples of working with fields (basic or custom) of type one2many:

odoo_api = pq.connect('Odoo')

ADD_LINK = 4
REPLACE_LINKS = 6

# Add new product with one one2many field value
# Link this product to id 40
odoo_api = pq.connect('Odoo')
product = {
    'name': "My new product",
    "x_related_product": 40
}
result = odoo_api.add('product', product)
st.json(result)

Fields of type Many2Many and One2Many

Examples of working with fields (basic or custom) of type many2many or one2many:

For a many2many and one2many fields, following value format is required:

[[ 0, 0, { values } ]] link to a new record that needs to be created with the given values dictionary
[[ 1, ID, { values } ]] update the linked record with id = ID (write values on it)
[[ 2, ID ]] remove and delete the linked record with id = ID (calls unlink on ID, that will delete the object completely, and the link to it as well)
[[ 3, ID ]] cut the link to the linked record with id = ID (delete the relationship between the two objects but does not delete the target object itself)
[[ 4, ID ]] link to existing record with id = ID (adds a relationship)
[[ 5 ]] unlink all
[[ 6, 0, [IDs] ]] replace the list of linked IDs

Writing to Odoo in batch

Example to insert new records in Odoo in batch, to speed up large data migrations or data syncs:

Here’s a second example, that creates invoices in Odoo in batch:

Working with product template & attributes

Example to link attribute values (e.g. Small, Medium, Large) for an attribute (e.g. “Size”) to a product template (e.g. “Shoe”):

‣

Click here to see the code of an end-to-end script including creation of product template, attributes, linking attributes to the template, updating the attributes and the automatically created product variants.

Odoo data model:

Odoo is a suite of open source business apps that cover all your company needs: CRM, eCommerce, accounting, inventory, point of sale, project management, etc.

This article provides an overview to get started with the Odoo connector in Peliqan. Please contact support if you have any additional questions or remarks.

Contents
Connect Odoo
Authentication
Database name
Sync additional standard and/or custom models
Odoo model explorer
Sync custom fields
Custom pipelines for Odoo
Odoo data model
Check completeness of data in DWH
Resync an Odoo table
Remove deleted records from the data warehouse
Writeback to Odoo from Python scripts
Basic writeback functions
Generic functions
Applying multiple filters in Odoo
Custom fields
Fields of type One2Many
Fields of type Many2Many and One2Many
Writing to Odoo in batch
Working with product template & attributes

Connect Odoo

In Peliqan, go to Connections > Add Connection > Select Odoo in the list > Enter the details of your Odoo instance.

Authentication

You can choose to authenticate with your username and password or with your username and an API key.

If 2FA (2-factor authentication) is enabled on your Odoo instance, you have to use an API key.

To create an API key in Odoo go to > My profile > Account Security > API keys.

Database name

If you don’t know your database name, you can make an unauthenticated API call, e.g. in Postman, to list your database names:

GET https://{domain}/web/database/list
GET https://{sub_domain}.odoo.com/web/database/list   (for Odoo.sh)

Headers:
Accept: application/json
Content-Type: application/json

Body:
{}

The body must contain an empty JSON object !

Or you can reach out to Peliqan Support (support@peliqan.io) to retrieve the name of your Odoo database.

Sync additional standard and/or custom models

You can find the model names by doing an initial sync of table “models” (under Advanced in the connect form):

The models table contains a list of all your Odoo models:

Odoo model explorer

You can also run a script to get a list of models, and all fields of a selected model.

‣

Click to expand the script

Sync custom fields

Custom fields will automatically be added as columns in the tables in the data warehouse. If you add new custom fields in Odoo, perform a “Full resync” to include them in the data warehouse.

Custom pipelines for Odoo

Peliqan will sync all fields of the selected models, except for calculated fields:

field property store=True: field is included in Peliqan
field property store=False: field is not included in Peliqan (calculated field)

Use below custom pipeline to sync a table with a custom selection of fields (e.g. all fields except a given set of fields to exclude because they cause errors).

‣

Click here to see an example custom pipeline for Odoo

Odoo data model

Use the below data app to explore models and fields in your Odoo instance.

‣

Odoo data model explorer

Use the below custom pipeline script, to sync the fields of a selected model, as well as the options of “Selection” fields, to tables in the data warehouse

‣

Click to expand script

Check completeness of data in DWH

The below script will compare the number of records (count) in the data warehouse with the actual count in Odoo.

‣

Click here to expand script

Resync an Odoo table

Resync an Odoo table, e.g. when fields were added in Odoo (schema is updated), using the below script.

‣

Click to expand script

Remove deleted records from the data warehouse

If records are deleted in Odoo, they are not automatically deleted in the data warehouse. Use below custom pipeline to delete records in the data warehouse, when they are no longer present in Odoo.

‣

Click to expand script

Writeback to Odoo from Python scripts

Basic writeback functions

Example to add a Product to Odoo:

odoo_api = pq.connect('Odoo')

product = {
    'name': "My new product"
}
result = odoo_api.add('product', product)
st.json(result)

Example to update a Product in Odoo:

odoo_api = pq.connect('Odoo')

product = {
    'id': 123,
    'name': "My new product updated"
}
result = odoo_api.update('product', product)
st.json(result)

Generic functions

These functions can be used to work with any model in Odoo, a standard or custom model.

Add object from a given model:

odoo_api = pq.connect('Odoo')

object = {
    "model": "product.template", # this could be a custom model
    "payload": [
            {
                "name": "My new object"
            }
        ],
    "additional_params": {}
    }

#result = odoo_api.add('object', object)
#st.json(result)

Update object from a given model:

object = {
    "model": "product.template", # this could be a custom model
    "payload": [ 
            [123],               # id of the object to update
            {
                "name": "My new product updated",
            }
        ],
    "additional_params": {}
    }
result = odoo_api.update('object', object)
st.json(result)

Execute a custom method or action on an object, for example Confirm a quotation to turn it into a sales order:

odoo_api = pq.connect('Odoo')
object_method = {
    'model': "sale.order",
    'method': "action_confirm",
    'payload': [[17]], # id of the quotation to update
    'additional_params': {}
}
result = odoo_api.update('object_method', object_method)
st.json(result)

Make a generic API call to Odoo:

odoo_api = pq.connect('Odoo')
request_payload = {
    'payload': [[['id', '=', '1']]],
    'odoo_model': "res.partner",
    'odoo_method': "search_read",
    'additional_params': {}
}
result = odoo_api.apicall(path = '', method = 'POST', **request_payload)
st.json(result)

Get objects using paging, and a fields list (note that some Odoo models require a fields list to be given in order to get a response from the API):

odoo_api = pq.connect('Odoo')
object = {
    'model': "product.product",
    'payload': [
        [
           
        ]
    ],
    'additional_params': {
        "order": "id desc",
        "offset": 0,
        "limit": 50,      # maximum allowed by Odoo is 50
        "fields": ["id", "name"]
    },
}
result=odoo_api.get('object', object)
st.write(result)

Applying multiple filters in Odoo

→ One can use generic get_object endpoint which can help applying multiple filters in on API request with dynamically defining the Model name.

→ this will return the first page only, max. 50 results

→ Please find below the example of working with multiple filters:

# AND operator
odoo_api = pq.connect('Odoo')
object = {    
				'model': "product.product",    
				'payload': 
					[
					        ['&', 
									['id', '=', 200],            
									['write_date', '>', '2024-09-01T00:00:00']
	        ]  ],    
				'additional_params': {   },
				}
result=odoo_api.get('object', object)
st.write(result)

# OR operator
odoo_api = pq.connect('Odoo')
object = {    
				'model': "product.product",    
				'payload': 
					[
					        ['|', 
									['id', '=', 200],            
									['write_date', '>', '2024-09-01T00:00:00'],
									['display_name']. '=' ,'Sample Product']
	        ]  ],    
				'additional_params': {   },
				}
result=odoo_api.get('object', object)
st.write(result)

Custom fields

Set value of a custom field:

odoo_api = pq.connect('Odoo')

product = {
    'name': "My new product",
    "x_my_custom_field": "Some value"
}
result = odoo_api.add('product', product)
st.json(result)

Fields of type One2Many

Examples of working with fields (basic or custom) of type one2many:

odoo_api = pq.connect('Odoo')

ADD_LINK = 4
REPLACE_LINKS = 6

# Add new product with one one2many field value
# Link this product to id 40
odoo_api = pq.connect('Odoo')
product = {
    'name': "My new product",
    "x_related_product": 40
}
result = odoo_api.add('product', product)
st.json(result)

Fields of type Many2Many and One2Many

Examples of working with fields (basic or custom) of type many2many or one2many:

odoo_api = pq.connect('Odoo')

ADD_LINK = 4
REPLACE_LINKS = 6

# Add new product with one many2many field value
# Link this product to id 40
odoo_api = pq.connect('Odoo')
product = {
    'name': "My new product",
    "x_related_product": [[ADD_LINK, 40]]
}
result = odoo_api.add('product', product)
st.json(result)

# Update product with id=47, replace many2many field values
# Link this product to ids 41 and 42 
odoo_api = pq.connect('Odoo')
product = {
    'id': 47,
    'name': "My new product updated",
    "x_related_product": [[REPLACE_LINKS, 0, [41, 42]]]
}
result = odoo_api.update('product', product)
st.json(result)

For a many2many and one2many fields, following value format is required:

[[ 0, 0, { values } ]] link to a new record that needs to be created with the given values dictionary
[[ 1, ID, { values } ]] update the linked record with id = ID (write values on it)
[[ 2, ID ]] remove and delete the linked record with id = ID (calls unlink on ID, that will delete the object completely, and the link to it as well)
[[ 3, ID ]] cut the link to the linked record with id = ID (delete the relationship between the two objects but does not delete the target object itself)
[[ 4, ID ]] link to existing record with id = ID (adds a relationship)
[[ 5 ]] unlink all
[[ 6, 0, [IDs] ]] replace the list of linked IDs

Writing to Odoo in batch

Example to insert new records in Odoo in batch, to speed up large data migrations or data syncs:

dbconn = pq.dbconnect(pq.DW_NAME)
odoo_api = pq.connect('Odoo V2')

partners = [
    {"name": "Partner 1"},
    {"name": "Partner 2"},
    {"name": "Partner 3"}
]

params = {
    'model': "res.partner",
    'payload': [partners],
    'additional_params': {}
}
result = odoo_api.add('object', params)
insert_ids = result["detail"]["result"]

merged_list = [ {**partner, "odoo_id": insert_id} for partner, insert_id in zip(partners, insert_ids) ]

sql_inserts = []
for item in merged_list:
    sql_insert = f"({item['odoo_id']}, '{item['name']}')"
    sql_inserts.append(sql_insert)
sql_inserts_str = ", ".join(sql_inserts)

insert_query = f"INSERT INTO logs.partner_log (odoo_id, name) VALUES {sql_inserts_str};"
dbconn.execute(pq.DW_NAME, query = insert_query)

Here’s a second example, that creates invoices in Odoo in batch:

odoo_api = pq.connect('Odoo V2')

command = 0    # Create new invoice line (needed in invoice lines, see below)
command_id = 0 # Not used when command = 0

def odoo_create_invoices_in_bulk(invoices):
    params = {
        'model': "account.move",
        'payload': [invoices],
        'additional_params': {}
    }
    result = odoo_api.add('object', params)
    insert_ids = result["detail"]["result"]
    st.text("List of Odoo ids after inserts:")
    st.write(insert_ids)

    # Merge source list with odoo ids created in target
    merged_list = [ {**invoice, "odoo_id": insert_id} for invoice, insert_id in zip(invoices, insert_ids) ]
    st.text("Original list of invoices with Odoo id added:")
    st.write(merged_list)
    return merged_list


######### Invoice 1 #########
######### Example adding lines one by one to the invoice #########

invoice1 = {
    "move_type": "out_invoice",
    "partner_id": 1
}

invoice_line1 = {
    'name': 'Shoes',
    'quantity': 2,
    'product_id': 1,
    'price_unit': 150,
}

invoice_line2 = {
    'name': 'Socks',
    'quantity': 4,
    'product_id': 2,
    'price_unit': 30,
}

invoice_line_ids = []
invoice_line_ids.append((command, command_id, invoice_line1))
invoice_line_ids.append((command, command_id, invoice_line2))
invoice1["invoice_line_ids"] = invoice_line_ids

######### Invoice 2 #########
######### Example with invoice lines inside invoice object #########

invoice2 = {
    "move_type": "out_invoice",
    "partner_id": 1,
    'invoice_line_ids': [
        (command, command_id, {
            'name': 'Shirts',
            'quantity': 6,
            'product_id': 3,
            'price_unit': 100,
        }),
         (command, command_id, {
            'name': 'Rings',
            'quantity': 1,
            'product_id': 4,
            'price_unit': 130,
        })
    ]
}

######### Make list of invoices #########

invoices = []
invoices.append(invoice1)
invoices.append(invoice2)

######### Example processing a list in batch #########

def split_list_in_batches(arr, batch_size=100):
    return [arr[i:i+batch_size] for i in range(0, len(arr), batch_size)]

invoice_batches = split_list_in_batches(invoices, 100)

for invoice_batch in invoice_batches:
    odoo_create_invoices_in_bulk(invoice_batch)

Working with product template & attributes

Example to link attribute values (e.g. Small, Medium, Large) for an attribute (e.g. “Size”) to a product template (e.g. “Shoe”):

odoo_api = pq.connect('Odoo')

ADD_LINK = 4
REPLACE_LINKS = 6

# Create product template attribute line (= link attribute values to template)
# Odoo will automatically create product.template.attribute.value items and product variants (product.product)
obj = {
    'model': 'product.template.attribute.line',
    'payload': [{
            'product_tmpl_id': product_tmpl_id, # e.g. Shoe
            'attribute_id': attribute_id,       # e.g. Size
            'value_ids': [[ REPLACE_LINKS, 0, [value1_id, value2_id] ]]  # e.g. Small, Medium
        }],
    'additional_params': {}
}
result = odoo_api.add('object', obj)
st.json(result)
line_id = result["detail"]["result"]


# Add additional attribute value
obj = {
    'model': "product.template.attribute.line",
    'payload': [[line_id], {'value_ids': [[ ADD_LINK, value3_id ]]}],   # e.g. Large
    'additional_params': {},
}
result = odoo_api.update('object', obj)
st.json(result)


# Get details of created line
search = {
    'model': "product.template.attribute.line",
    'payload': [[ 
            ['id', '=', line_id] 
        ]],
    'additional_params': {},
}
result = odoo_api.get('object', search)
st.json(result)
product_template_attribute_value1_id = result["product_template_value_ids_0"]
product_template_attribute_value2_id = result["product_template_value_ids_1"]


# Get details of attribute value 1 (to extract id of created product variant)
search = {
    'model': "product.template.attribute.value",
    'payload': [[ 
            ['id', '=', product_template_attribute_value1_id] 
        ]],
    'additional_params': {},
}
result = odoo_api.get('object', search)
st.json(result)
product_variant1_id = result["ptav_product_variant_ids_0"]

‣

Odoo data model:

Odoo - Getting started in Peliqan

Contents

Connect Odoo

Authentication

Database name

Sync additional standard and/or custom models

Odoo model explorer

Sync custom fields

Custom pipelines for Odoo

Odoo data model

Check completeness of data in DWH

Resync an Odoo table

Remove deleted records from the data warehouse

Writeback to Odoo from Python scripts

Basic writeback functions

Generic functions

Applying multiple filters in Odoo

Custom fields

Fields of type One2Many

Fields of type Many2Many and One2Many

Writing to Odoo in batch

Working with product template & attributes

Odoo - Getting started in Peliqan

Contents

Connect Odoo

Authentication

Database name

Sync additional standard and/or custom models

Odoo model explorer

Sync custom fields

Custom pipelines for Odoo

Odoo data model

Check completeness of data in DWH

Resync an Odoo table

Remove deleted records from the data warehouse

Writeback to Odoo from Python scripts

Basic writeback functions

Generic functions

Applying multiple filters in Odoo

Custom fields

Fields of type One2Many

Fields of type Many2Many and One2Many

Writing to Odoo in batch

Working with product template & attributes