Tutorial

The Spine DB API allows one to create and manipulate Spine databases in a standard way, using SQLAlchemy as the underlying engine. This tutorial provides a quick introduction to the usage of the package.

To begin, make sure Spine database API is installed as described in Installation.

Database Mapping

The main mean of communication with a Spine DB is the DatabaseMapping, specially designed to retrieve and modify data from the DB. To create a DatabaseMapping, we just pass the URL of the DB to the class constructor:

import spinedb_api as api
from spinedb_api import DatabaseMapping

url = "mysql+pymysql://spine_db"  # The URL of an existing Spine DB

with DatabaseMapping(url) as db_map:
    # Do something with db_map
    pass

The URL should be formatted following the RFC-1738 standard, as described here.

Note

Currently supported database backends are SQLite and MySQL.

Creating a DB

If you’re following this tutorial, chances are you don’t have a Spine DB to play with just yet. We can remediate this by creating a SQLite DB (which is just a file in your system), as follows:

import spinedb_api as api
from spinedb_api import DatabaseMapping

url = "sqlite:///first.sqlite"

with DatabaseMapping(url, create=True) as db_map:
    # Do something with db_map
    pass

The above will create a file called first.sqlite in your current working directoy. Note that we pass the keyword argument create=True to DatabaseMapping to explicitly say that we want the DB to be created at the given URL if it does not exists already.

Note

In the remainder we will skip the above step and work directly with db_map. In other words, all the examples below assume we are inside the with block above.

Adding data

To insert data, we use e.g. add_entity_class_item(), add_entity_item(), and so on.

Let’s begin the party by adding a couple of entity classes:

db_map.add_entity_class_item(name="fish", description="It swims.")
db_map.add_entity_class_item(name="cat", description="Eats fish.")

Now let’s add a multi-dimensional entity class between the two above. For this we need to specify the class names as dimension_name_list:

db_map.add_entity_class_item(
    name="fish__cat",
    dimension_name_list=("fish", "cat"),
    description="A fish getting eaten by a cat?",
)

Let’s add entities to our zero-dimensional classes:

db_map.add_entity_item(entity_class_name="fish", name="Nemo", description="Lost (for now).")
db_map.add_entity_item(
    entity_class_name="cat", name="Felix", description="The wonderful wonderful cat."
)

Let’s add a multi-dimensional entity to our multi-dimensional class. For this we need to specify the entity names as element_name_list:

db_map.add_entity_item(entity_class_name="fish__cat", element_name_list=("Nemo", "Felix"))

Let’s add a parameter definition for one of our entity classes:

db_map.add_parameter_definition_item(entity_class_name="fish", name="color")

Finally, let’s specify a parameter value for one of our entities. First, we use to_database() to convert the value we want to give into a tuple of value and type:

value, type_ = api.to_database("mainly orange")

Now we create our parameter value:

db_map.add_parameter_value_item(
    entity_class_name="fish",
    entity_byname=("Nemo",),
    parameter_definition_name="color",
    alternative_name="Base",
    value=value,
    type=type_
)

Note that in the above, we refer to the entity by its byname. We also set the value to belong to an alternative called Base which is readily available in new databases.

Note

The data we’ve added so far is not yet in the DB, but only in an in-memory mapping within our db_map object. Don’t worry, we will save it to the DB soon (see Committing data if you’re impatient).

Retrieving data

To retrieve data, we use e.g. get_entity_class_item(), get_entity_item(), etc. This implicitly fetches data from the DB into the in-memory mapping, if not already there. For example, let’s find one of the entities we inserted above:

felix_item = db_map.get_entity_item(entity_class_name="cat", name="Felix")
assert felix_item["description"] == "The wonderful wonderful cat."

Above, felix_item is a PublicItem object, representing an item.

Let’s find our multi-dimensional entity:

nemo_felix_item = db_map.get_entity_item("entity", entity_class_name="fish__cat", element_name_list=("Nemo", "Felix"))
assert nemo_felix_item["dimension_name_list"] == ('fish', 'cat')

Now let’s retrieve our parameter value:

nemo_color_item = db_map.get_parameter_value_item(
    entity_class_name="fish",
    entity_byname=("Nemo",),
    parameter_definition_name="color",
    alternative_name="Base"
)

We can use the "parsed_value" field to access our original value:

nemo_color = nemo_color_item["parsed_value"]
assert nemo_color == "mainly orange"

To retrieve all the items of a given type, we use get_items():

assert [entity["entity_byname"] for entity in db_map.get_items("entity")] == [
    ("Nemo",), ("Felix",), ("Nemo", "Felix")
]

Now you should use the above to try and find Nemo.

Updating data

To update data, we use the update() method of PublicItem.

Let’s rename our fish entity to avoid any copyright infringements:

db_map.get_entity_item(entity_class_name="fish", name="Nemo").update(name="NotNemo")

To be safe, let’s also change the color:

new_value, new_type = api.to_database("not that orange")
db_map.get_parameter_value_item(
    entity_class_name="fish",
    entity_byname=("NotNemo",),
    parameter_definition_name="color",
    alternative_name="Base",
).update(value=new_value, type=new_type)

Note how we need to use then new entity name NotNemo to retrieve the parameter value. This makes sense.

Removing data

You know what, let’s just remove the entity entirely. To do this we use the remove() method of PublicItem:

db_map.get_entity_item(entity_class_name="fish", name="NotNemo").remove()

Note that the above call removes items in cascade, meaning that items that depend on "NotNemo" will get removed as well. We have one such item in the database, namely the "color" parameter value which also gets dropped when the above method is called.

Restoring data

TODO

Committing data

Enough messing around. To save the contents of the in-memory mapping into the DB, we use commit_session():

db_map.commit_session("Find Nemo, then lose him again")