Download Odoo Stock Move using API/RPC and save it to excel using pandas with flatten fields

odoo_stock_move_download.py

import time
import odoorpc
import pandas


def odoo_download_stock_move_to_excel():
  """
  Download stock move data from Odoo and save it to an Excel file.
  """
  # Odoo connection
  odoo = odoorpc.ODOO('odoo.yourdomain.com', port=8069)
  odoo.login('your_database', 'your_username', 'your_password')
  
  # Define the Stock Move model
  StockMove = odoo.env['stock.move']
  
  # Define the search domain
  domain = [
    ('state', '!=', 'cancel'),
    ('picking_id', '!=', False),
    ('quantity', '!=', 0)
  ]

  # Define the fields to retrieve
  specification =  {
    'id': {},
    'reference': {},
    'date': {},
    'product_uom_qty': {},
    'quantity': {},
    'state': {},
    'product_id': {
      'fields': {
        'id': {},
        'name': {},
        'unified_product_code': {},
        'default_code': {},
      },
    },
    'location_id': {
      'fields': {
        'id': {},
        'name': {},
        'display_name': {},
      },
    },
    'picking_id': {
      'fields': {
        'id': {},
        'name': {},
        'origin': {},
        'note': {},
        'state': {},
      },
    },
  }

  total_count = StockMove.search_count(domain)
  print(f"Total stock moves to download: {total_count}")

  # Download in batches
  batch_size = 1000
  offset = 0
  all_records = []

  while offset < total_count:
    try:
      # Fetch records using web_search_read
      records = StockMove.web_search_read(domain, specification, order='date desc', limit=batch_size, offset=offset)
      if not records:
        print(f"no more records found at offset {offset}. Ending download.")
        break
      all_records.extend(records.get('records', []))
    except Exception as e:
      print(f"Error fetching records at offset {offset}: {e}. Retrying after 10 seconds...")
      # sleep for 10 seconds and retry
      time.sleep(10)
      records = StockMove.web_search_read(domain, spec, order='date desc', limit=batch_size, offset=offset)
      if not records:
        print(f"no more records found at offset {offset} after retry. Ending download.")
        break
      all_records.extend(records.get('records', []))

    offset += batch_size
    print(f"Downloaded {len(all_records)}/{total_count} records...")
  
  # Check if any records were fetched
  if not all_records:
    print("No records fetched from Odoo.")
    return None
  
  # make records flat from nested dict
  # single e.g: {'product_id': {'id': 1, 'name': 'Product A'}} -> {'product_id_id': 1, 'product_id_name': 'Product A'}
  def flatten_record(record):
    flat_record = {}
    for key, value in record.items():
      if isinstance(value, dict):
        for sub_key, sub_value in value.items():
          if isinstance(sub_value, dict):
            for sub_sub_key, sub_sub_value in sub_value.items():
              flat_record[f"{key}_{sub_key}_{sub_sub_key}"] = sub_sub_value
          else:
            flat_record[f"{key}_{sub_key}"] = sub_value
      else:
        flat_record[key] = value
    return flat_record
  flatten_records = [flatten_record(rec) for rec in all_records]

  # Convert to DataFrame
  df = pandas.DataFrame(flatten_records)

  # manipulate dataframe to add `Note` and `PosNumber` column
  # parse `Note` by cleaning the html tags if any
  def remove_tags(string):
    result = re.sub('<.*?>','',string)
    return result
  df['Note'] = df['picking_id_note'].apply(lambda x: remove_tags(x) if isinstance(x, str) else '')
  # sample `Note` value that already cleaned: `POS/26152 - 10100238/5438`
  # PosNumber is after `-` stripped.
  df['PosNumber'] = df['Note'].apply(lambda x: x.split('-')[1].strip() if isinstance(x, str) and '-' in x else '')

  # sort by if needed
  # df = df.sort_values(by=['date'], ascending=[False])

  # add prefix `odoo_` on all columns, if needed
  df = df.add_prefix('odoo_')

  # Save to Excel
  fname = 'odoo-stock.move.xlsx'
  df.to_excel(fname, index=False)
  print(f"Downloaded {len(df)} stock moves to {fname}")
  return fname


if __name__ == '__main__':
  st = time.time()
  print(f"Start download stock move from Odoo")

  # download odoo stock move
  odoo_download_stock_move_to_excel()

  ed = time.time()
  print(f"Total time taken: {ed - st:.2f} seconds")