How To: upsert_rows() in DataversePython

How to Use the upsert_rows() Method in DataversePython

The upsert_rows() method in the DataverseClient class is designed for advanced data migration and update scenarios in Microsoft Dataverse. Unlike insert_rows(), which lets Dataverse generate GUIDs for new records, upsert_rows() gives you full control over record GUIDs—essential for migrating existing databases and maintaining relationships. It also supports a true update mode, allowing you to update only existing records, which is perfect for enriching data after an initial migration.

---

What is upsert_rows()?

upsert_rows() lets you insert or update records in Dataverse entities using a pandas DataFrame. You specify the primary key column, so you can preserve relationships and GUIDs from your source system. The behavior of the method is controlled by the only_update_if_exists parameter:

• Upsert: If only_update_if_exists=False (default), the method will insert new records with your specified GUIDs if they do not exist, or update them if they already exist. This is ideal for migrations where you want to preserve existing relationships and record GUIDs.
• Update: If only_update_if_exists=True, the method will only update records that already exist in Dataverse, skipping any that do not. This is useful for incremental updates, such as adding lookup fields or updating option sets after an initial migration.

---

Method Signature

upsert_rows(
    entity: str,
    df: pd.DataFrame,
    primary_key_col: str,
    only_update_if_exists: bool = False
) -> None

---

Parameters Explained

• entity (str, required):
  The plural name of the Dataverse entity (e.g., 'accounts', 'contacts').
• df (pd.DataFrame, required):
  DataFrame containing the data to upsert. Must include a column with the primary key GUIDs.
• primary_key_col (str, required):
  The name of the column in your DataFrame that contains the GUIDs for each record.
• only_update_if_exists (bool, optional):
  If True, only updates existing records. If False, inserts new records or updates existing ones.

---

---

Basic Example: Upsert Accounts

from DataversePython.DataverseClient import DataverseClient
import pandas as pd

client = DataverseClient('sample_config.json')

# DataFrame with GUIDs from legacy system
accounts_df = pd.DataFrame({
    'accountid': ['guid1', 'guid2'],
    'name': ['Contoso', 'Fabrikam'],
    'revenue': [100000, 250000]
})

# Upsert accounts, preserving GUIDs
client.upsert_rows(
    entity='accounts',
    df=accounts_df,
    primary_key_col='accountid',
    only_update_if_exists=False
)

---

Example: Update Only Existing Records

# Update only existing accounts with new lookup fields
update_df = pd.DataFrame({
    'accountid': ['guid1', 'guid2'],
    'primarycontactid@odata.bind': ['/contacts(contact-guid1)', '/contacts(contact-guid2)'],  # Lookup field
    'industrycode': ['1', '2']  # Optionset field
})

client.upsert_rows(
    entity='accounts',
    df=update_df,
    primary_key_col='accountid',
    only_update_if_exists=True
)

---

Advanced Example: Upsert Different Column Types

• Optionset: Pass the integer value for the optionset column (e.g., '1') as a string.
• Multioptionset: Pass a integer values as string seperated by comma (e.g., '1, 2') for multioptionset columns.
• Lookup: Pass '/' followed by the related entitys plural name with the GUID in parentheses (e.g., /contacts(contact-guid1)).

# Upsert with optionset, multioptionset, and lookup fields
accounts_df = pd.DataFrame({
    'accountid': ['guid1'],
    'name': ['Contoso'],
    'industrycode': ['1'],  # Optionset
    'custom_multioptionset': ['1, 2'],  # Multioptionset
    'primarycontactid@odata.bind': ['/contacts(contact-guid1)']  # Lookup
})

client.upsert_rows(
    entity='accounts',
    df=accounts_df,
    primary_key_col='accountid',
    only_update_if_exists=False
)

---

Tips

• For lookups, the column name needs '@odata.bind' appended. For multi entity lookups, like parentcustomerid, you need to append the specific entity to the column name. e.g. parentcustomerid_contacts@odata.bind or parentcustomerid_accounts@odata.bind
• Use only_update_if_exists=True for incremental updates after initial migration.
• Check DataverseClient.log for detailed error messages and troubleshooting.
• Refer to the Microsoft Dataverse Web API documentation for advanced scenarios.

---

Conclusion

The upsert_rows() method is essential for data migration and advanced update scenarios in Dataverse. It gives you full control over record IDs, supports all major column types, and enables incremental updates—making it a must-have tool for any Dynamics 365 or Power Platform specialist working with Python.

Happy coding and successful migrations!