Copying entity locations from one database to another

[soininen]

This is a short example on a Toolbox workflow that copies entity locations from one database to another.

Our workflow looks like this:

The Source Data store contains the source database from which we want to copy the location data to matching entities in the database referenced by Target. The copying is done by a Python script in Copy locations Tool.

The script looks like this:

import sys
import spinedb_api as api

input_url = sys.argv[1]  # 1.
output_url = sys.argv[2]


with api.DatabaseMapping(input_url) as source:
    locations = source.fetch_all("entity_location")
    location_by_entity = {(loc["entity_class_name"], loc["entity_name"]): loc for loc in locations}  # 2.
    with api.DatabaseMapping(output_url) as target:
        entities = target.fetch_all("entity")
        for entity in entities:
            try:
                location = location_by_entity[(entity["entity_class_name"], entity["name"])]  # 3.
            except KeyError:
                continue
            entity.update(
                lat=location["lat"],
                lon=location["lon"],
                alt=location["alt"],
                shape_name=location["shape_name"],
                shape_blob=location["shape_blob"],
            )
        try:
            target.commit_session("Copy entity location data.")  # 4.
        except api.exception.NothingToCommit:
            pass

1. We get the source and target database URLs from command line arguments which makes the script portable. Using sys.argv to access the arguments in a simple use case like this is OK but personally, I would switch to argparse for anything more complex.
2. After connecting to the source database with DatabaseMapping, we fetch entity location data and reorganize it by entity class and entity so we can find the correct location for each target entity more easily.
3. Then, we connect to the target database and loop over all entities. If an entity has a location record in the location_by_entity dict, we update the entity. Note, that we modify entity items directly here, not entity_location items --- it is just more convenient to do so.
4. Finally, we commit the changes and do not care if no changes were made.

To make Toolbox feed the Copy location Tool the URLs as command line arguments, we need to drag and drop the database resources provided by the Source and Target databases into the Tool arguments list in the properties dock of the Copy locations Tool:

That's it! The entity locations will be copied from Source to Target when executing the project.