Query and Access

This page provides examples for working with the Convexity database directly using SQL or programmatically through PyConvexity.

SQL Query Examples

Get all generators:

SELECT * FROM components WHERE component_type = 'GENERATOR';

Get a component's static attributes:

SELECT attribute_name, static_value 
FROM component_attributes 
WHERE component_id = ? AND storage_type = 'static' AND scenario_id IS NULL;

Get time series data:

SELECT attribute_name, timeseries_data 
FROM component_attributes 
WHERE component_id = ? AND storage_type = 'timeseries' AND scenario_id IS NULL;

Get scenario overrides:

SELECT s.name, ca.attribute_name, ca.static_value
FROM component_attributes ca
JOIN scenarios s ON ca.scenario_id = s.id
WHERE ca.component_id = ?;

Get all components by type with their carrier:

SELECT c.id, c.name, c.component_type, carr.name as carrier_name
FROM components c
LEFT JOIN carriers carr ON c.carrier_id = carr.id
WHERE c.component_type = 'GENERATOR';

Get components connected to a specific bus:

SELECT id, name, component_type 
FROM components 
WHERE bus_id = ? OR bus0_id = ? OR bus1_id = ?;

Count attributes by storage type:

SELECT storage_type, COUNT(*) as count
FROM component_attributes
WHERE scenario_id IS NULL
GROUP BY storage_type;

Programmatic Access

For programmatic access, use pyconvexity instead of raw SQL. This provides a higher-level API with type safety and convenience methods.

Basic Operations

import pyconvexity as px

# Open database connection
with px.database_context("model.db") as conn:
    # Get all components of a type
    generators = px.get_components(conn, network_id, "GENERATOR")
    
    # Get component by name
    component = px.get_component_by_name(conn, "Coal Plant")
    
    # Get static attribute
    p_nom = px.get_static_attribute(conn, gen_id, "p_nom")
    
    conn.commit()

Time Series Operations

# Get time series data (optimized binary access)
demand = px.get_timeseries("model.db", load_id, "p_set")

# Set time series data
import numpy as np
solar_profile = np.maximum(0, np.sin(np.linspace(0, np.pi, 24)) * 100)
px.set_timeseries("model.db", generator_id, "p_max_pu", solar_profile)

Scenario Operations

with px.database_context("model.db") as conn:
    # Create scenario
    scenario_id = px.create_scenario(conn, "High Fuel Costs", 
                                     description="Gas price +50%")
    
    # Set scenario override
    px.set_static_attribute(conn, gen_id, "marginal_cost", 
                           px.StaticValue(75.0), scenario_id=scenario_id)
    
    conn.commit()

Bulk Operations

with px.database_context("model.db") as conn:
    # Get all generators with their attributes
    generators = px.get_components(conn, network_id, "GENERATOR")
    
    for gen in generators:
        p_nom = px.get_static_attribute(conn, gen.id, "p_nom")
        marginal_cost = px.get_static_attribute(conn, gen.id, "marginal_cost")
        print(f"{gen.name}: {p_nom} MW @ {marginal_cost} EUR/MWh")

Direct SQLite Access

For advanced users who need direct database access:

import sqlite3

# Open database
conn = sqlite3.connect("model.db")
cursor = conn.cursor()

# Execute custom queries
cursor.execute("""
    SELECT c.name, c.component_type, ca.attribute_name, ca.static_value
    FROM components c
    JOIN component_attributes ca ON c.id = ca.component_id
    WHERE c.component_type = 'GENERATOR' 
    AND ca.scenario_id IS NULL
""")

results = cursor.fetchall()
conn.close()

Performance Considerations

Indexed queries: Use indexed columns for fast lookups:

• components.name - Component name lookups
• components.component_type - Filter by type
• component_attributes(component_id, attribute_name, scenario_id) - Attribute access

Time series access: Binary time series data is stored as raw f32 arrays for maximum performance. Use PyConvexity's optimized methods rather than direct BLOB manipulation.

Scenario queries: Use scenario_id IS NULL to query base network, or specific scenario_id values for overrides.

Learn More

• PyConvexity Documentation - Complete API reference
• PyConvexity Examples - Practical tutorials
• Scenarios Guide - Working with scenarios