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):
IfTrue, only updates existing records. IfFalse, 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=Truefor incremental updates after initial migration. - Check
DataverseClient.logfor 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!