Insert a record into a relationship table using API for Python in ArcGIS Notebooks

Summary

In ArcGIS Online, a record can be manually inserted into a relationship table to create a relationship between records in the origin and destination tables. Alternatively, ArcGIS API for Python can be used to programmatically insert the records using primary key values. As a result, new records containing the corresponding foreign key values are added to the relationship table, creating relationships between the associated records in the origin and destination tables.

This article describes the workflow to insert records into a many-to-many relationship table using ArcGIS API for Python in ArcGIS Notebooks.

Procedure

1. Open the notebook in ArcGIS Online.
2. Copy and paste the code below to insert the record into the relationship table.
   1. Import the libraries from the Python API and authenticate the connection to ArcGIS Online.

from arcgis.gis import GIS
from arcgis.features import FeatureLayer
gis = GIS("home")

Note:
After pasting the code, click Run this cell and advance  to execute the cell before proceeding to the next step. 

2. Connect to the relationship table. Replace <URLofFeatureService> with the REST endpoint URL of the hosted feature service layer containing the relationship table. Ensure the layer URL ends with the layer index.

relationship_table = FeatureLayer(
    "<URLofFeatureService>",
    gis
)

3. Insert the record using the primary key values from the origin and destination tables. Replace <originTableForeignKey> with the origin table foreign key field, <destinationTableForeignKey> with the destination table foreign key field, <originTablePrimaryKey> with the origin table primary key value, and <destinationTablePrimaryKey> with the destination table primary key value.

result = relationship_table.edit_features(
    adds=[
        {
            "attributes": {
                "<originTableForeignKey>": "<originTablePrimaryKey>",
                "<destinationTableForeignKey>": "<destinationTablePrimaryKey>"
            }
        },
        {
            "attributes": {
                "<originTableForeignKey>": "<originTablePrimaryKey>",
                "<destinationTableForeignKey>": "<destinationTablePrimaryKey>"
            }
        }, 
        {
            "attributes": {
                "<originTableForeignKey>": "<originTablePrimaryKey>",
                "<destinationTableForeignKey>": "<destinationTablePrimaryKey>"
            }
        }
    ]
)

4. Display the relationship table name and verify the record insertion is successful.

relationship_table_name = relationship_table.properties.name

if result["addResults"][0]["success"]:
    print(
        f"Relationship record created successfully. "
        f"A new record has been added to the {relationship_table_name} table."
    )
else:
    print("Failed to create relationship record.")
    print(result)

5. Display the latest record inserted. Replace <ObjectIDFieldName> with the relationship table’s ObjectID field name.

latest_record = max(
    relationship_table.query(
        where="1=1",
        out_fields="*"
    ).features,
    key=lambda f: f.attributes["<ObjectIDFieldName>"]
)

print(latest_record.attributes)

The code below shows the full working script.

from arcgis.gis import GIS
from arcgis.features import FeatureLayer
gis = GIS("home")

relationship_table = FeatureLayer(
     "https://services9.arcgis.com/LMuydzYxR6YWiqk2/arcgis/rest/services/Relationship_Table/FeatureServer/3",
      gis
  )

result = relationship_table.edit_features(
    adds=[
        {
            "attributes": {
                "ProjectGUID": "bfff21b8-e352-40be-87dc-c46f24fb8c32",
                "EmployeeGUID": "713588da-ee1b-4bd0-95bd-e69dc6020fe5"
            }
        },
        {
            "attributes": {
                "ProjectGUID": "bfff21b8-e352-40be-87dc-c46f24fb8c32",
                "EmployeeGUID": "8760b436-664a-4430-b312-cf38f375a293"
            }
        },
        {
            "attributes": {
                "ProjectGUID": "bec6b0e8-2d45-4bcb-b650-5812d6448a5c",
                "EmployeeGUID": "713588da-ee1b-4bd0-95bd-e69dc6020fe5"
            }
        }
    ]
)

relationship_table_name = relationship_table.properties.name

if result["addResults"][0]["success"]:
    print(
        f"Relationship record created successfully. "
        f"A new record has been added to the {relationship_table_name} table."
    )
else:
    print("Failed to create relationship record.")
    print(result)

latest_record = max(
    relationship_table.query(
        where="1=1",
        out_fields="*"
    ).features,
    key=lambda f: f.attributes["RID"]
)
print(latest_record.attributes)

The table below displays the inserted records.