Export data faster

[IngridJSJ]

Dear Spine team,
I am using the spine_api (export_functions.py) to import data into a python repository (export from sqlite DB)

I am exporting the data and iterating over all lines as follows:

        self.db = SpineDB(db_urls)
        db_data = self.db.export_data()
        ...
        object_parameter_values = db_data['object_parameter_values']
        ... 
                    db_line = next(i for i in object_parameter_values
                                   if i[0] == object_class_name and i[1] == object_name and i[2] == parameter_name)
                    add_parameter_value_to_repository_based_on_object_class_name(reps, db_line)

I only need to import the object_class_name , object_name , paramenter name, object_parameter_values
Is there a way to feed faster my python repository?

[soininen]

I'm not sure what SpineDB class can do but you could try something like this:

from spinedb_api import DatabaseMapping, export_object_parameter_values
url = r"sqlite:///c:\path\to\database.sqlite"
db_map = DatabaseMapping(url)
try:
    object_parameter_values = export_object_parameter_values(db_map)
finally:
    db_map.connection.close()
# ...rest of the script should work as is

In the above script, export_object_parameter_values() queries object parameter values only which should be faster than export_data() which reads everything from the database.

Another and potentially even faster option is to query the database directly:

from spinedb_api import DatabaseMapping, from_database
url = r"sqlite:///c:\path\to\database.sqlite"
db_map = DatabaseMapping(url)
try:
    object_parameter_values = [
        (r.object_class_name, r.object_name, r.parameter_name, from_database(r.value, r.type), r.alternative_name)
        for r in db_map.query(db_map.object_parameter_value_sq)
    ]
finally:
    db_map.connection.close()
# ...rest of the script should work as is

If you know which object classes, objects or parameters you need, you can add filters to the database query:

from spinedb_api import DatabaseMapping, from_database
url = r"sqlite:///c:\path\to\database.sqlite"
db_map = DatabaseMapping(url)
try:
    subquery = db_map.object_parameter_value_sq
    object_parameter_values = [
        (r.object_class_name, r.object_name, r.parameter_name, from_database(r.value, r.type), r.alternative_name)
        for r in db_map.query(subquery).filter(subquery.c.object_class_name == "my_favorite_class", subquery.c.parameter_name == "my_favorite_parameter")
    ]
finally:
    db_map.connection.close()
# ...rest of the script should work as is

The above script would fill object_parameter_values list with values of "my_favorite_parameter" from object class "my_favorite_class" only.