Skip to content

Reading a PLEXOS Solution with DuckDB

Use the DuckDB-backed PlexosSolution API when you need lazy DuckDB relations and SQL queries over result tables from a PLEXOS solution ZIP file. Import it from plexosdb.db_solution; the SQLite-backed class in plexosdb.solution_reader is a separate implementation.

See the DuckDB solution API reference for the full method and result-type documentation.

Create the solution with from_zip() and convert it with to_duckdb():

from pathlib import Path
from plexosdb.db_solution import PlexosSolution
solution_zip = Path("/path/to/Model Solution.zip")
sol = PlexosSolution.from_zip(solution_zip)
sol.to_duckdb("solution.duckdb", if_exists="reuse")

For temporary analysis, omit the database path. The final connection is in-memory:

sol = PlexosSolution.from_zip(solution_zip)
result = sol.to_duckdb()
assert result.is_in_memory

For large solutions, prefer a file-backed DuckDB database so you can reuse it without converting the ZIP again.

If you only need a subset of result tables, pass a table_name_pattern to to_duckdb():

sol.to_duckdb(
"solution.duckdb",
if_exists="replace",
table_name_pattern="ST__Interval__Generators__Generation",
)

This pattern is useful when a solution ZIP contains many result tables but your analysis only needs one family of outputs.

Use list_result_tables() to find the result tables generated by plexos2duckdb:

from plexosdb.enums import ClassEnum
for table in sol.list_result_tables(class_enum=ClassEnum.Generator):
print(table.name)

To select one table, use the existing PLEXOS enums and the result-table property name:

from plexosdb.enums import PeriodEnum, PhaseEnum
generation = sol.result_table(
phase=PhaseEnum.ST,
period=PeriodEnum.INTERVAL,
class_enum=ClassEnum.Generator,
property_name="Generation",
)
print(generation.name)

Use generation.value_column for the numerical column name because converter versions can name it either Generation or value.

Use get_result() to build a lazy DuckDB relation. Convert to pandas only when you need a DataFrame:

relation = sol.get_result(
generation,
object_names="Coal_Gen",
start="2017-01-01",
end="2017-01-08",
columns=["name", "timestamp", generation.value_column, "unit"],
)
df = relation.df()

You can also pass a result table name directly. String table names use the report schema:

one_day = sol.get_result(
"ST__Interval__Generators__Generation",
object_names=["Coal_Gen"],
start="2017-01-01",
end="2017-01-02",
)
rows = one_day.fetchall()

Use query() for eager row results:

rows = sol.query(
'SELECT COUNT(*) FROM report."ST__Interval__Generators__Generation"'
)

Use query_dicts() when column names are useful:

rows = sol.query_dicts(
"SELECT name FROM processed.objects WHERE class = ? ORDER BY name",
(ClassEnum.Generator.value,),
)

Use sql() for a lazy DuckDB relation that you can keep composing:

summary = sol.sql(
f'''
SELECT name, SUM("{generation.value_column}") AS generation_mwh
FROM report."ST__Interval__Generators__Generation"
WHERE sample_name = 'Mean'
GROUP BY name
ORDER BY name
'''
)
summary_df = summary.df()

Close the DuckDB connection when you are done:

sol.close()

You can also use a context manager after conversion:

sol = PlexosSolution.from_zip(solution_zip)
sol.to_duckdb("solution.duckdb", if_exists="reuse")
with sol:
print(sol.list_tables(schema="report")[:5])

RuntimeError: No active connection

Call to_duckdb() before reading tables or results.

KeyError: No result table matched ...

Run list_result_tables() with fewer filters to see which phase, period, collection, and property combinations exist.

Conversion creates more tables than needed

Use table_name_pattern during to_duckdb() or reuse a file-backed database with if_exists="reuse".