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.
Converting a solution
Section titled “Converting a solution”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_memoryFor large solutions, prefer a file-backed DuckDB database so you can reuse it without converting the ZIP again.
Limiting result tables during conversion
Section titled “Limiting result tables during conversion”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.
Listing available result tables
Section titled “Listing available result tables”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)Reading filtered result rows
Section titled “Reading filtered result rows”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()Querying with DuckDB SQL
Section titled “Querying with DuckDB SQL”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()Closing the connection
Section titled “Closing the connection”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])Troubleshooting
Section titled “Troubleshooting”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".