database_utils

SQLite storage for processed CFS forecast data.

Wraps a single SQLite database/table behind CFSDatabase, providing schema creation, row- and DataFrame-level inserts, point lookups, and helpers for figuring out which forecast run to download next. The schema keys each value by (cfs_run, year, month, lake, surface_type, component).

The forecast notebooks use this module to persist processed CFS output and to resume incremental downloads where the previous run left off.

class src.database_utils.CFSDatabase(database, table)

Bases: object

Manage a SQLite database of processed CFS forecast values.

Each instance is bound to one database file and one table. The table stores one value per (cfs_run, year, month, lake, surface_type, component) key. Methods cover schema creation, inserting individual records or whole DataFrames, point lookups, and determining the next CFS run to download.

Parameters:

• database (str) – Path to the SQLite database file (created if it does not exist).

• table (str) – Name of the table to read from and write to.

__init__(database, table)

Initialize database connection and ensure table exists.

create_cfs_table()

Create the standard CFS table schema if it does not exist.

load()

Load the entire table into a DataFrame.

Returns:

All rows of the configured table.

Return type:

pandas.DataFrame

pull(cfs_run, year, month, lake, surface_type, component)

Look up a single stored value by its full primary key.

Detects whether the table uses a value or value [mm] column and queries accordingly.

Parameters:

• cfs_run (str) – CFS run identifier (YYYYMMDDHH).

• year (int) – Forecast year.

• month (int) – Forecast month (1-12).

• lake (str) – Lake name.

• surface_type (str) – Surface type (‘lake’ or ‘land’).

• component (str) – NBS component (‘precipitation’, ‘evaporation’, ‘runoff’, ‘nbs’).

Returns:

The stored value, or None if no matching row exists or a database error occurs.

Return type:

float or None

add(cfs_run, year, month, lake, surface_type, component, value)

Safely adds or replaces a record in the database table using SQLite.

Parameters:

cfs_run (str): The CFS run identifier (YYYYMMDDHH). year (int): Forecast year (e.g., 2024, 2025). month (int): Forecast month (1–12). lake (str): Lake name. surface_type (str): Surface type (‘lake’ or ‘land’). component (str): NBS Component (‘precipitation’, ‘evaporation’, ‘runoff’, ‘cnbs’). value (float): Data value.

Raises:

ValueError: For invalid parameter types or ranges. sqlite3.DatabaseError: For database interaction errors.

add_df(df, if_exists='append')

Add an entire pandas DataFrame to the database table.

Parameters:

• df (pandas.DataFrame) – The DataFrame to insert. Must contain the columns ['cfs_run', 'forecast_month', 'model', 'lake', 'precipitation', 'evaporation', 'runoff', 'nbs'].

• if_exists (str, default "append") – How to behave if the table already exists. One of 'fail', 'replace', or 'append'.

get_next_run()

Determine the next CFS run date to download.

Reads the most recent cfs_run in the table and returns the date from which downloading should resume: the same date at midnight if the last run was the 00/06/12 cycle, or the following day if it was the 18 cycle. If the table is missing or empty, falls back to the first of the month nine months ago.

Returns:

The next run date, with the time component zeroed.

Return type:

datetime.datetime

get_date_range(auto, start_date, end_date)

Determine the start and end dates for CFS CSV downloads, and return the date range.

Parameters:

• auto (str,) – Whether to automatically fetch the next run date from the database (‘yes’ or ‘no’).

• start_date (str,) – Manual start date in format ‘MM-DD-YYYY’. Required if auto=’no’.

• end_date (str,) – Manual end date in format ‘MM-DD-YYYY’. Required if auto=’no’.

Returns:

(start_date: datetime, end_date: datetime, date_array: pd.DatetimeIndex)

Return type:

tuple

print_columns()

Print the name and declared type of each column in the table.

Intended as an interactive/debugging helper; prints a message if the table does not exist or has no columns.