How to Sync Odoo with External Databases Using Python

Pull External Data into Odoo

Many businesses run legacy systems or third-party applications that store critical data in separate databases. Integrating this data into Odoo is often necessary for reporting, invoicing, inventory management, or customer engagement.

This guide walks you through a practical, developer-friendly approach to syncing external databases with Odoo using Python.

We’ll explore how to:

Connect Python to external databases (MySQL, PostgreSQL)
Fetch and process external data
Sync it with Odoo models using the ORM
Automate the process via Scheduled Actions
Secure and optimise the workflow

Why Sync External Databases with Odoo?

Centralise business data scattered across systems
Avoid manual data entry and reduce errors
Integrate with legacy ERP, CRM, and WMS systems
Automate regular data imports
Enable consolidated reporting

Common Use Cases

Customer or supplier info from a CRM
Inventory data from a warehouse management system
Sales records from a legacy POS system

Technical Architecture Overview

[External Database] ←→ [Python script (inside Odoo custom model)] ←→ [Odoo ORM] ←→ [Odoo PostgreSQL Database]

Workflow

Use Python libraries (mysql-connector-python for MySQL, psycopg2 for PostgreSQL)
Fetch records from the external DB
Map fields to Odoo models
Use the Odoo ORM for create/update operations
Schedule the sync via Odoo’s Scheduled Actions

Implementation: Full Code Example

Custom Field for Mapping (to avoid duplicates)

Add a technical field like legacy_id to track external records:

from odoo import models, fields
class ResPartner(models.Model):
    _inherit = 'res.partner'
    legacy_id = fields.Integer(string="Legacy External ID", readonly=True)

Connect to an External MySQL Database

import mysql.connector
connection = mysql.connector.connect(
    host='192.168.1.100',
    database='legacy_db',
    user='db_user',
    password='db_pass'
)

For PostgreSQL

import psycopg2
connection = psycopg2.connect(
    host='192.168.1.100',
    database='external_system',
    user='db_user',
    password='db_pass'
)

Fetch Records (MySQL example)

cursor = connection.cursor(dictionary=True)
cursor.execute("SELECT id, name, email FROM legacy_customers")
customers = cursor.fetchall()

Sync Data into Odoo

from odoo import models, api
import logging
import psycopg2
_logger = logging.getLogger(__name__)
class ResPartner(models.Model):
    _inherit = 'res.partner'
    @api.model
    def sync_legacy_customers(self):
        connection = psycopg2.connect(
            host='192.168.1.100',
            database='legacy_db',
            user='db_user',
            password='db_pass'
        )
        cursor = connection.cursor()
        cursor.execute("SELECT id, name, email FROM legacy_customers")
        customers = cursor.fetchall()
        for customer in customers:
            partner = self.search([( 'legacy_id', '=', customer['id'])], limit=1)
            if partner:
                partner.write({
                    'name': customer['name'],
                    'email': customer['email']
                })
            else:
                self.create({
                    'name': customer['name'],
                    'email': customer['email'],
                    'legacy_id': customer['id']
                })
        connection.close()
        _logger.info("Legacy customers synced successfully.")

Automating the Process

Go to Settings → Technical → Automation → Scheduled Actions.

Create a new action

Model: res.partner
Python Code:

model.sync_legacy_customers()

Security & Best Practices

Never hardcode DB credentials

Use ir.config_parameter to store database credentials securely.

self.env['ir.config_parameter'].sudo().get_param('legacy_db_password')

Log all operations

Use Odoo’s _logger to track every sync run.

Use transactions or batch commits

For large data sets, use Odoo’s env.cr.commit() after every batch to avoid transaction timeouts.

Validate external data before import

Check for missing or malformed fields before writing to Odoo.

Make the process idempotent

Use unique external IDs (legacy_id) to avoid duplicate records.

Possible Enhancements

Two-way sync (writeback changes from Odoo to external DB)
Sync attachments or images (if URLs/paths are stored externally)
Sync other models (products, orders, invoices)
Notify users via email after each sync
Sync via Odoo scheduled cron jobs
Use Odoo external API or OdooRPC instead of direct DB connections for loosely coupled systems
Migrate to a message queue (RabbitMQ, Redis) for real-time updates

The Takeaway

Connecting Odoo with external databases via Python scripts is a powerful technique for modernising legacy systems and consolidating business data. With careful planning and secure coding practices, you can automate regular sync jobs and free up hours of manual work while reducing errors.

Found this article useful?

Explore more development guides and solutions from our team.

Check out more posts

in Odoo Development

Vijay Elangovan 2 June, 2025