DB import relationship error

[IngridJSJ]

Dear spinetoolbox team,
I am trying to import a number into a database, but there seem to be a relationship missing

https://github.com/TradeRES/toolbox-amiris-emlab/blob/dev/emlabpy/util/clock.py

WARNING:root:DBAPIError while inserting object items: ('UNIQUE constraint failed: entity.class_id, entity.name',)
WARNING:root:Could not import parameter value for '2050', class 'peakLoad', parameter 'ElectricitySpotMarketNL': Missing object or relationship identifier.

I do it like this.
db_emlab.import_object_classes([classnameem])
db_emlab.import_objects([(classnameem, object_nameem)])
db_emlab.import_data({'object_parameters': [[classnameem, parameterem]]})
db_emlab.import_alternatives([str(0)])
db_emlab.import_object_parameter_values([(classnameem, object_nameem, parameterem, peak_load, '0')])

Thank you very much for your support!

[IngridJSJ]

Actually ideally I would like to modify a value from a Map from the clock file.

Thank you very much for your help

[soininen]

Looks like you are using a homegrown interface to Spine database which I am not very familiar with. Perhaps you could direct questions about it to its author?

In any case, I have a hunch that the parameters in the call to db_emlab.import_object_parameter_values() are in wrong order. Can you check that classnameem actually is 'ElectricitySpotMarkets', object_nameem is 'ElectricitySpotMarketNL', and so on?

For your question about how to modify a value directly in a Map: I do not know how it could be done with db_emlab but I can give an example on how to do it using spinedb_api directly. Hopefully it is helpful.

from spinedb_api import DatabaseMapping, from_database, Map, to_database
import spinedb_api.import_functions as im

# Add data to the database
initial_peak_load = Map([2020.0, 2050.0], [20000.0, 25390.0])
class_name = "ElectricitySpotMarkets"
object_name = "ElectricitySpotMarketNL"
parameter_name = "peakLoadFixed"
alternative_name = str(0)

url = "sqlite://"  # Use in-memory database
db_map = DatabaseMapping(url, create=True)  # Remove create=True if the database exists
im.import_object_classes(db_map, [class_name])
im.import_objects(db_map, [(class_name, object_name)])
im.import_object_parameters(db_map, [(class_name, parameter_name)])
im.import_alternatives(db_map, [alternative_name])
im.import_object_parameter_values(
    db_map,
    [(class_name, object_name, parameter_name, initial_peak_load, alternative_name)],
)
db_map.commit_session("Add initial data.")

# Now we have data in the database.
# Let's find that peak load value and modify it.

# Unfortunately, this is somewhat complicated as we need to do a raw database query.
sq = db_map.object_parameter_value_sq
value_record = db_map.query(sq).filter(sq.c.object_class_name == class_name).filter(
    sq.c.object_name == object_name
).filter(sq.c.parameter_name == parameter_name).filter(
    sq.c.alternative_name == alternative_name
).one()

peak_load = from_database(value_record.value, value_record.type)

# Finally we get to modify the value.
peak_load.set_value(2050.0, 33000.0)

# Store the modified value back to database.
im.import_object_parameter_values(
    db_map,
    [(class_name, object_name, parameter_name, peak_load, alternative_name)],
)
db_map.commit_session("Update peak load value.")