Skip to content

Inspecting a PLEXOS Solution with SQLite

This guide shows how to import a PLEXOS solution ZIP file into SQLite and inspect its table catalog using the show_db_tables helper.

This guide uses the SQLite-backed PlexosSolution imported from plexosdb.solution_reader. It is separate from the DuckDB-backed class in plexosdb.db_solution. See the SQLite solution API reference for the complete API.

Use PlexosSolution to import the ZIP into SQLite. Pass decode_bin_values=False to skip writing BIN payload data — the table catalog only needs the XML metadata tables and is then fast even for large solutions:

from plexosdb.solution_reader import PlexosSolution, show_db_tables
sol = PlexosSolution.from_zip("my_solution.zip")
sol.to_sqlite("output.sqlite", if_exists="replace", decode_bin_values=False)

Call show_db_tables directly on the PlexosSolution instance after to_sqlite() has been called:

show_db_tables(sol)
┌───────────────────────────┬──────────────┬──────────────────────────────────────────────────────────┬────────────┬──────────────────────────────┬──────────────────────┬───────────────────────────┬──────────────────────────┬────────────────────────┬────────────────────┬──────────┬───────────────┬───────────────┐
│ table_catalog │ table_schema │ table_name │ table_type │ self_referencing_column_name │ reference_generation │ user_defined_type_catalog │ user_defined_type_schema │ user_defined_type_name │ is_insertable_into │ is_typed │ commit_action │ TABLE_COMMENT │
│ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │ varchar │
├───────────────────────────┼──────────────┼──────────────────────────────────────────────────────────┼────────────┼──────────────────────────────┼──────────────────────┼───────────────────────────┼──────────────────────────┼────────────────────────┼────────────────────┼──────────┼───────────────┼───────────────┤
│ Model model_2012 Solution │ data │ ST__Interval__Batteries__Generation │ BASE TABLE │ NULL │ NULL │ NULL │ NULL │ NULL │ YES │ NO │ NULL │ NULL │
│ Model model_2012 Solution │ data │ ST__Interval__Batteries__Generation_Capacity │ BASE TABLE │ NULL │ NULL │ NULL │ NULL │ NULL │ YES │ NO │ NULL │ NULL │
│ Model model_2012 Solution │ data │ ST__Interval__Batteries__Installed_Capacity │ BASE TABLE │ NULL │ NULL │ NULL │ NULL │ NULL │ YES │ NO │ NULL │ NULL │
│ Model model_2012 Solution │ data │ ST__Interval__Batteries__Load │ BASE TABLE │ NULL │ NULL │ NULL │ NULL │ NULL │ YES │ NO │ NULL │ NULL │
│ Model model_2012 Solution │ data │ ST__Interval__Batteries__SoC │ BASE TABLE │ NULL │ NULL │ NULL │ NULL │ NULL │ YES │ NO │ NULL │ NULL │
│ Model model_2012 Solution │ data │ ST__Interval__Generators__Available_Capacity │ BASE TABLE │ NULL │ NULL │ NULL │ NULL │ NULL │ YES │ NO │ NULL │ NULL │
│ Model model_2012 Solution │ data │ ST__Interval__Generators__Average_Heat_Rate │ BASE TABLE │ NULL │ NULL │ NULL │ NULL │ NULL │ YES │ NO │ NULL │ NULL │
│ Model model_2012 Solution │ data │ ST__Interval__Generators__Capacity_Curtailed │ BASE TABLE │ NULL │ NULL │ NULL │ NULL │ NULL │ YES │ NO │ NULL │ NULL │
│ Model model_2012 Solution │ data │ ST__Interval__Generators__Capacity_Factor │ BASE TABLE │ NULL │ NULL │ NULL │ NULL │ NULL │ YES │ NO │ NULL │ NULL │
│ Model model_2012 Solution │ data │ ST__Interval__Generators__Emissions_Cost │ BASE TABLE │ NULL │ NULL │ NULL │ NULL │ NULL │ YES │ NO │ NULL │ NULL │
│ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │
│ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │ · │
│ Model model_2012 Solution │ report │ ST__Year__Reserves__Price │ VIEW │ NULL │ NULL │ NULL │ NULL │ NULL │ NO │ NO │ NULL │ NULL │
│ Model model_2012 Solution │ report │ ST__Year__Reserves__Provision │ VIEW │ NULL │ NULL │ NULL │ NULL │ NULL │ NO │ NO │ NULL │ NULL │
│ Model model_2012 Solution │ report │ ST__Year__Reserves__Shortage │ VIEW │ NULL │ NULL │ NULL │ NULL │ NULL │ NO │ NO │ NULL │ NULL │
│ Model model_2012 Solution │ report │ ST__Year__Storages__Generation │ VIEW │ NULL │ NULL │ NULL │ NULL │ NULL │ NO │ NO │ NULL │ NULL │
│ Model model_2012 Solution │ report │ ST__Year__Storages__Inflow │ VIEW │ NULL │ NULL │ NULL │ NULL │ NULL │ NO │ NO │ NULL │ NULL │
│ Model model_2012 Solution │ report │ ST__Year__Storages__Initial_Volume │ VIEW │ NULL │ NULL │ NULL │ NULL │ NULL │ NO │ NO │ NULL │ NULL │
│ Model model_2012 Solution │ report │ ST__Year__Storages__Max_Volume │ VIEW │ NULL │ NULL │ NULL │ NULL │ NULL │ NO │ NO │ NULL │ NULL │
│ Model model_2012 Solution │ report │ ST__Year__Storages__Pump_Load │ VIEW │ NULL │ NULL │ NULL │ NULL │ NULL │ NO │ NO │ NULL │ NULL │
└───────────────────────────┴──────────────┴──────────────────────────────────────────────────────────┴────────────┴──────────────────────────────┴────────────┴───────────────────────────┴──────────────────────────┴────────────────────────┴────────────────────┴──────────┴───────────────┴───────────────┘
267 rows (20 shown) 13 columns

Rows that do not fit within max_rows are replaced by three · rows. The default limit is 20; pass a different value to show more:

with sol as db:
show_db_tables(db, max_rows=50)
ColumnDescription
table_catalogStem of the source ZIP file (used as the catalog name).
table_schemaSQLite schema name: main, data, report, or any other attached schema.
table_nameName of the table or view.
table_typeBASE TABLE for tables; VIEW for views.
is_insertable_intoYES for writable tables, NO for views.
is_typedAlways NO.
remaining columnsNULL — present for compatibility with information_schema.tables.
  • main — raw tables imported directly from the PLEXOS solution XML (e.g. t_class, t_object, t_membership).
  • data — derived result tables that can be materialized from BIN files (e.g. ST__Interval__Generators__Generation).
  • report — same derived tables exposed as views with enriched metadata joins.

Every derived table name encodes four pieces of information separated by __:

{Phase}__{Period}__{Collection}__{Property}
SegmentExample valuesMeaning
PhaseST, MT, LT, PASAPLEXOS simulation phase
PeriodInterval, Day, Week, Month, YearTime resolution of the result
CollectionGenerators, Batteries, RegionsPLEXOS object collection
PropertyGeneration, Load, PriceReported property name

For example, ST__Interval__Generators__Generation holds interval-level generator generation values from the ST (Short-Term) phase.

The report and data schemas expose slightly different columns.

report schema (use this for analysis):

ColumnTypeDescription
bandINTEGERBand number (1 for single-band properties).
sample_nameTEXTStochastic sample label; 'Mean' for deterministic runs.
nameTEXTObject name (e.g. generator or region name).
categoryTEXTObject category.
timestampTEXTDatetime string matching the period resolution.
interval_lengthREALDuration of the period in hours (NULL for non-interval data).
{Property}REALNumeric result value — named after the property (last segment of the table name, e.g. Generation or Build_Cost).
unitTEXTUnit string (e.g. 'MW', 'MWh').

data schema (raw materialized):

ColumnTypeDescription
nameTEXTObject name.
sample_nameTEXTStochastic sample label.
band_idINTEGERBand number.
block_idINTEGERInternal period block identifier.
datetimeTEXTRaw datetime string from the period table.
valueREALNumeric result value.

PlexosSolution.connection is a standard sqlite3.Connection. You can query report tables directly after materializing them.

In the report schema the value column is named after the property (last segment of the table name). Extract it from the table name:

from plexosdb.solution_reader import PlexosSolution
sol = PlexosSolution.from_zip("my_solution.zip")
sol.to_sqlite("output.sqlite", if_exists="replace")
table = "ST__Interval__Generators__Generation"\
# splits into ['ST', 'Interval', 'Generators', 'Generation']
property_col = table.split("__")[-1] # "Generation"
sol.materialize_table(table, schema="report")
rows = sol.connection.execute(
f'SELECT name, timestamp, "{property_col}" FROM report."{table}" LIMIT 5'
).fetchall()
for name, ts, val in rows:
print(name, ts, val)

Pass sol.connection directly to pandas.read_sql_query:

import pandas as pd
table = "ST__Interval__Generators__Generation"
property_col = table.split("__")[-1] # "Generation"
df = pd.read_sql_query(
f'SELECT name, timestamp, "{property_col}" FROM report."{table}"',
sol.connection,
)

Filter by object name and date range inside the SQL to avoid pulling large result sets into memory:

df = pd.read_sql_query(
f'''
SELECT name, timestamp, "{property_col}"
FROM report."{table}"
WHERE name = ? AND timestamp BETWEEN ? AND ?
''',
sol.connection,
params=("Coal_Gen", "2017-01-01 00:00:00", "2017-01-08 00:00:00"),
)

If you already ran to_sqlite() once, use from_sqlite() to skip the re-import and go straight to querying:

from plexosdb.solution_reader import PlexosSolution
sol = PlexosSolution.from_sqlite("output.sqlite")
table = "ST__Interval__Generators__Generation"
property_col = table.split("__")[-1] # "Generation"
sol.materialize_table(table, schema="report")
df = pd.read_sql_query(
f'SELECT name, timestamp, "{property_col}" FROM report."{table}"',
sol.connection,
)
sol.close()

Use list_tables() to discover which derived tables exist before deciding what to materialize:

# All derived tables (data schema naming, but both data and report are the same set)
for t in sol.list_tables(schema="data"):
print(t.name)
# Filter by keyword
generators = [t for t in sol.list_tables(schema="data") if "Generator" in t.name]