Bulk Operations with PlexosDB
This guide demonstrates how to efficiently perform bulk operations using PlexosDB, which can significantly improve performance when working with large datasets.
Bulk Inserting Properties
Section titled “Bulk Inserting Properties”When you need to add multiple properties to multiple objects, using individual
add_property calls can be inefficient. The add_properties_from_records
method provides a much more efficient approach.
Basic Usage
Section titled “Basic Usage”from plexosdb import PlexosDBfrom plexosdb.enums import ClassEnum, CollectionEnum
# Initialize the databasedb = PlexosDB()db.create_schema(version=10)
# Create the objects firstdb.add_object(ClassEnum.Generator, "Generator1")db.add_object(ClassEnum.Generator, "Generator2")db.add_object(ClassEnum.Generator, "Generator3")
# Prepare the property recordsrecords = [ { "name": "Generator1", "Max Capacity": 100.0, "Min Stable Level": 20.0, "Heat Rate": 10.5 }, { "name": "Generator2", "Max Capacity": 150.0, "Min Stable Level": 30.0, "Heat Rate": 9.8 }, { "name": "Generator3", "Max Capacity": 200.0, "Min Stable Level": 40.0, "Heat Rate": 8.7 }]
# Add all properties in bulkdb.add_properties_from_records( records, object_class=ClassEnum.Generator, collection=CollectionEnum.Generators, scenario="Base Case")Performance Considerations
Section titled “Performance Considerations”The add_properties_from_records method processes records in batches (default
10,000 records per batch) and uses SQLite transactions to maximize performance.
This makes it much faster than individual property insertions, especially for
large datasets.
Key performance features:
- Single transaction for all insertions (atomic operations)
- Batch processing to control memory usage
- Direct SQL execution with prepared statements
- Automatic property enablement (sets
is_dynamicandis_enabledflags)
Bulk Updating Properties
Section titled “Bulk Updating Properties”Use update_properties to update several existing property values in one
transaction. Each update record uses the same selectors as update_property:
object_name, property_name, new_value, and object_class. The optional
scenario, band, collection, parent_class, and parent_object_name
fields narrow the matching property row. For relationship properties, use
parent_object_name with parent_class to select the property belonging to a
specific parent object.
Each bulk selector updates every matching data record. For a multi-band
property, omitting band updates all bands; provide band when only one band
should change. This differs from update_property, which requires its selectors
to identify exactly one data record and raises an error when multiple records
match.
updates = [ { "object_name": "Generator1", "property_name": "Max Capacity", "new_value": 125.0, "object_class": ClassEnum.Generator, }, { "object_name": "Generator2", "property_name": "Heat Rate", "new_value": 9.8, "object_class": ClassEnum.Generator, "band": 2, "scenario": "High Demand", },]
db.update_properties(updates)All updates are committed together. If an update cannot find its object,
property, scenario, or requested band, the transaction fails and earlier updates
in the batch are rolled back. When scenario is omitted, the update targets the
base property rather than a scenario-tagged row.
Handling Different Object Classes
Section titled “Handling Different Object Classes”You can process different types of objects separately:
# Add Generator objectsdb.add_object(ClassEnum.Generator, "Generator1")db.add_object(ClassEnum.Generator, "Generator2")
# Add Region objectsdb.add_object(ClassEnum.Region, "Region1")db.add_object(ClassEnum.Region, "Region2")
# Generator propertiesgenerator_records = [ {"name": "Generator1", "Max Capacity": 100.0}, {"name": "Generator2", "Max Capacity": 150.0}]
# Region propertiesregion_records = [ {"name": "Region1", "Load Scaling Factor": 1.1}, {"name": "Region2", "Load Scaling Factor": 0.9}]
# Process each set with appropriate parametersdb.add_properties_from_records( generator_records, object_class=ClassEnum.Generator, collection=CollectionEnum.Generators, scenario="Base Case")
db.add_properties_from_records( region_records, object_class=ClassEnum.Region, collection=CollectionEnum.Regions, scenario="Base Case")Data Validation
Section titled “Data Validation”The method automatically validates:
- All objects exist before attempting inserts
- All property names are valid for the collection
- All required fields are present
Bulk Inserting Memberships
Section titled “Bulk Inserting Memberships”Creating relationships between many objects can be time-consuming when done
individually. The add_memberships_from_records method allows you to
efficiently create multiple memberships in a single operation.
Basic Usage
Section titled “Basic Usage”from plexosdb import PlexosDBfrom plexosdb.enums import ClassEnum, CollectionEnumfrom plexosdb.utils import create_membership_record
# Initialize the databasedb = PlexosDB()db.create_schema(version=10)
# Create parent and child objectsregion_id = db.add_object(ClassEnum.Region, "MainRegion")
# Create multiple nodesnode_ids = []for i in range(1, 101): # Create 100 nodes node_ids.append(db.add_object(ClassEnum.Node, f"Node{i}"))
# Get necessary IDs for membershipsparent_class_id = db.get_class_id(ClassEnum.Region)child_class_id = db.get_class_id(ClassEnum.Node)collection_id = db.get_collection_id( CollectionEnum.ReferenceNode, parent_class_enum=ClassEnum.Region, child_class_enum=ClassEnum.Node)
# Create membership recordsmembership_records = create_membership_record( node_ids, child_object_class_id=child_class_id, parent_object_class_id=parent_class_id, parent_object_id=region_id, collection_id=collection_id)
# Bulk insert all memberships at oncedb.add_memberships_from_records(membership_records)To identify the correct CollectionEnum for your relationship, use the
list_collections method:
collection_list = db.list_collections(parent_class=ClassEnum.Region, child_class=ClassEnum.Node)print(collection_list) # Shows available collections for Region-Node relationshipsThis ensures you’re using the exact collection name that exists in your database schema.
Performance Benefits
Section titled “Performance Benefits”Using add_memberships_from_records offers several advantages over individual
add_membership calls:
- Significantly reduced execution time for large datasets
- Lower overhead from fewer database operations
- Optional chunking for very large datasets (controlled by the
chunksizeparameter) - Efficient batch SQL execution
Manual Record Creation
Section titled “Manual Record Creation”If you need more control, you can manually create the membership records:
# Create records manuallyrecords = []for node_id in node_ids: records.append({ 'parent_class_id': parent_class_id, 'parent_object_id': region_id, 'collection_id': collection_id, 'child_class_id': child_class_id, 'child_object_id': node_id })
# Bulk insert membershipsdb.add_memberships_from_records(records)Each record must contain these fields:
parent_class_id: ID of the parent classparent_object_id: ID of the parent objectcollection_id: ID of the collectionchild_class_id: ID of the child classchild_object_id: ID of the child object
Combined Bulk Operations
Section titled “Combined Bulk Operations”For complex model creation, you can combine bulk operations to efficiently build your model:
- First create all objects using
add_objects(bulk object creation) - Add memberships between objects with
add_memberships_from_records - Add properties to the objects using
add_properties_from_records
This approach can dramatically improve performance when creating large, complex models.
Bulk Inserting Attributes
Section titled “Bulk Inserting Attributes”For efficiently adding multiple attribute values, use
add_attributes_from_records.
Basic Usage (Wide Format)
Section titled “Basic Usage (Wide Format)”from plexosdb.enums import ClassEnum
records = [ {"name": "Horizon1", "Step Type": 4.0, "Chrono Step Count": 366.0}, {"name": "Horizon2", "Step Type": 4.0, "Chrono Step Count": 365.0},]
db.add_attributes_from_records( records, object_class=ClassEnum.Horizon,)