losalamos.tools.database#

Database Manager#

A standalone Python tool for creating and managing environmental science databases in GeoPackage (SQLite) format. Designed around two primitive but scalable schemas for spatial and time series data.

Everything runs through a single file — database.py — driven by JSON specs.

pip install geopandas fiona pandas

Usage#

python database.py --command initialize --parameters path/to/specs.json
python database.py --command append     --parameters path/to/specs.json
python database.py --command update     --parameters path/to/specs.json

Add --verbose for debug-level logging.

The specs file orchestrates all parameters. Multiple commands can coexist in the same file; only the one named by --command is executed. Each command holds a list of procedures, allowing batches.

Schemas#

Two primitive schemas cover most environmental science use cases.

In-situ measurements

The canonical case: temperature, precipitation, river level — anything measured continuously at fixed locations. Works for point geometries (stations, gauges) and polygon geometries (census tracts, regular grids, catchments).

sites is the core geospatial layer. Wide table.

field	type	notes
`id`	int PK	auto-generated
`code`	text	sourced from provider or created; must be unique; indexed
`name`	text	short label; sized for composite names
`source`	text	inline citation or URL
`category`	text	e.g. climate station, rain gauge
`abstract`	text	comments on unusual sites, etc
`geometry`	geometry	usually point or polygon

code is the source of identity. Duplicates not allowed on update.

measurements is the core data. Long table. Non-spatial.

field	type	notes
`id`	int PK	auto-generated
`site_id`	int FK	→ sites
`attribute_id`	int FK	→ attributes
`datetime`	text	`YYYY-MM-DD HH:MM:SS` or `YYYY-MM-DD`
`tier`	int	data level flag → tier table; DEFAULT 0
`quality`	int	quality flag → quality table; DEFAULT 0
`value`	real

Each site_id–attribute_id–datetime–tier–quality combination is unique and indexed. Default assimilation skips existing records (append); overwrite is explicit (update).

The time key in specs controls datetime resolution. If absent or null, defaults to true (full datetime). Set to false for date-only storage.

attributes — semantic metadata for each measured variable.

field	type	notes
`code`	text	unique identifier
`alias`	text	short label
`name`	text	full name
`symbol`	text	LaTeX symbol
`units`	text	LaTeX units
`abstract`	text
`subset`	text	math domain: `real`, `positive real`, `probability`, etc
`domain_min` / `domain_max`	real	valid range
`category`	text	system classification: flow, level, ratio, etc
`theme`	text	thematic field: e.g. Hydrology > Hillslope > Isotopes

tier and quality — small lookup tables for documenting flags.

field	type
`value`	int (unique)
`name`	text
`alias`	text
`symbol`	text
`abstract`	text

storage — optional compression metadata per attribute.

field	type	notes
`attribute_id`	int FK	→ attributes
`dtype`	text	e.g. `float32`, `int8`
`scale`	real	default 1.0
`offset`	real	default 0.0

actual_value = (stored_value × scale) + offset

If the table is empty or an attribute has no entry, scale=1 and offset=0 are assumed. This applies to both retrieval and assimilation.

Off-site measurements

For environmental quality assessments: samples collected in the field, analysed later in the lab. Sites produce samples (a bottle, a soil core) at a given datetime. Samples produce measurements at a (possibly different) analysis datetime.

Multiple measurements can come from one sample — e.g. three replicate analyses entering as tier=0 (raw), their average entering as tier=1 (consisted).

The quality flag handles detection limits (value receives the limit, quality maps to “less than”) and super-large values (value as power of 10, quality encodes the exponent).

samples — field collection catalog. Wide table.

field	type	notes
`id`	int PK
`code`	text
`site_id`	int FK	→ sites
`datetime`	text	sampling datetime
`abstract`	text	field comments

Identity is code–site_id–datetime. Multiple samples at the same site and date are allowed if they have different codes.

measurements — same structure as in-situ, but site_id becomes sample_id (FK → samples).

Commands#

initialize

Creates a new GeoPackage database and loads seed data. Fails if the database already exists.

python database.py --command initialize --parameters specs.json

"initialize": [
  {
    "database": "path/to/output.gpkg",
    "category": "in-situ",
    "time": true,
    "sep": ";",

    "tables": {
      "sites":      {"file": "path/to/sites.gpkg", "layer": "sites"},
      "tier":       "path/to/tier.csv",
      "quality":    {"file": "path/to/quality.csv", "sep": ",", "header_lines": 2},
      "attributes": {"file": "path/to/attributes.csv", "sep": "     "},
      "storage":    null,

      "measurements": "path/to/seed_data.csv"
    },

    "extra_sql": ["path/to/extra_fields.sql"]
  }
]

category: "in-situ" or "off-site"
time: true (default) → YYYY-MM-DD HH:MM:SS; false → YYYY-MM-DD
storage: null → table is created empty; scale=1, offset=0 assumed everywhere
measurements (and samples for off-site) at init time are optional
extra_sql: optional list of .sql files for extra columns or extra tables

append

Adds new rows to any table. Records that already exist (matching the identity index) are silently skipped. Batching is allowed.

python database.py --command append --parameters specs.json

"append": [
  {
    "database":     "path/to/output.gpkg",
    "table":        "measurements",
    "data_file":    "path/to/new_data.csv",
    "sep":          ";",
    "header_lines": 4
  }
]

update

Same as append, but existing records that collide with incoming data are overwritten. Records with no collision are left untouched. New records are inserted normally.

python database.py --command update --parameters specs.json

"update": [
  {
    "database":  "path/to/output.gpkg",
    "table":     "attributes",
    "data_file": "path/to/revised_attributes.csv"
  }
]

CSV Loading#

The default field separator is ;. Two optional keys control how each file is read.

key	type	description
`sep`	string	field separator: `";"`, `","`, `" "`, `"\|"`, etc.
`header_lines`	int	number of preamble lines to skip before the header row

Options can be set at two levels, with the per-table value taking priority:

Per-table — inside the table spec dict in tables
Procedure-level — at the top of the procedure dict, as a fallback for all CSV tables in that step

A plain string path (no dict) always inherits from the procedure level. If neither level specifies a key, the built-in default applies (sep=";", header_lines=0).

Missing columns. Incoming data does not need to supply every column in the target table. The assimilation engine handles absent columns as follows:

Identity columns with a schema default (e.g. tier, quality — both DEFAULT 0 in the measurements table): padded with the schema default value.
Non-identity optional columns (e.g. abstract, symbol, units): padded with NULL.
Identity columns with no default (e.g. site_id, attribute_id, datetime): missing → hard error.

Extra columns in the incoming file that have no matching column in the target table are dropped with a warning.

Foreign Key Resolution#

Incoming CSV data carries human-readable codes, not internal integer IDs. The assimilation engine resolves codes to IDs automatically before inserting.

For measurements (in-situ) the CSV uses a site or site_id column resolved against sites.code, and an attribute or attribute_id column resolved against attributes.code. The engine tries several column name candidates in order:

site_id → site → sites_code → site_code → code

Unresolvable codes raise an error listing the offending values.

Identity#

Each table has a defined set of columns that jointly identify a unique record.

table	identity columns
`sites`	`code`
`attributes`	`code`
`tier`	`value`
`quality`	`value`
`storage`	`attribute_id`
`samples`	`code`, `site_id`, `datetime`
`measurements`	`site_id` (or `sample_id`), `attribute_id`, `datetime`, `tier`, `quality`

Extensibility#

Extra columns — write an .sql file with ALTER TABLE ... ADD COLUMN ... statements and list it under extra_sql in the initialize spec. Useful additions include sites.area_km2 for polygon-based grids or catchment areas.

Extra tables — same mechanism. A CREATE TABLE in the .sql file adds a new table at initialization. Useful for site_attributes populated from spatial joins.

Both are per-database: different databases initialized from different specs can have different extra columns and tables without touching the core schema.

Notes#

The sites table is created and owned by the GeoPackage driver (via geopandas/Fiona), ensuring the file is readable by QGIS, GDAL, and any GeoPackage-compliant library. All other tables are plain SQLite tables inside the same file.

SQLite stores TEXT and DATETIME identically at the storage level. Datetime columns are declared TEXT and formatted as ISO 8601 (YYYY-MM-DD HH:MM:SS), which guarantees correct lexicographic ordering and compatibility with SQLite’s built-in date/time functions (strftime, date, between, etc.).

The tool uses PRAGMA foreign_keys = ON and PRAGMA journal_mode = WAL.

Functions

`align_columns`(df, db_cols, identity_cols[, ...])	Align df to the target table's column set.
`cmd_append`(procedures)
`cmd_initialize`(procedures)
`cmd_update`(procedures)
`detect_category`(con)	Heuristic: off-site if 'samples' table exists, else in-situ.
`detect_has_time`(con)	Infer datetime resolution from an existing measurements row.
`dispatch`(command, specs)
`get_schema`(category)
`gpkg_bootstrap`(con)	Write minimal GeoPackage metadata tables into a fresh database.
`gpkg_code_id_map`(con, table[, code_col])	Return {code: pk_id} for any table with a code column.
`gpkg_connect`(path)	Open (or create) a GeoPackage file.
`gpkg_exec_file`(con, sql_path)	Execute an external SQL file (for extra_sql extensions).
`gpkg_exec_sql`(con, sql)	Execute a block of SQL statements against con.
`gpkg_pk_col`(con, table)	Return the primary key column name — 'fid' for Fiona tables, 'id' otherwise.
`gpkg_table_columns`(con, table)
`gpkg_table_exists`(con, table)
`gpkg_table_info`(con, table)	Return column metadata keyed by column name.
`gpkg_write_spatial`(gdf, gpkg_path, layer[, crs])	Write a GeoDataFrame as a GeoPackage spatial layer via geopandas/Fiona.
`insert_rows`(con, table, df, identity_cols[, ...])	Insert df into table with duplicate handling.
`load_csv`(path[, sep, skiprows])	Load a delimited text file into a DataFrame.
`load_spatial`(path[, layer])
`main`([argv])
`normalise_datetime`(df, has_time)
`resolve_fk`(df, con, fk_rules)	Replace code-based columns with integer FK ids.
`setup_logging`([verbose])

losalamos.tools.database.get_schema(category: str) → dict[source]#

losalamos.tools.database.gpkg_connect(path: Path) → Connection[source]#: Open (or create) a GeoPackage file. Bootstraps metadata if new.

losalamos.tools.database.gpkg_bootstrap(con: Connection) → None[source]#: Write minimal GeoPackage metadata tables into a fresh database.

losalamos.tools.database.gpkg_exec_sql(con: Connection, sql: str) → None[source]#: Execute a block of SQL statements against con. Writes to a temp file in the module folder, executes, deletes. The file stays on disk if execution crashes — useful for debugging.

losalamos.tools.database.gpkg_exec_file(con: Connection, sql_path: Path) → None[source]#: Execute an external SQL file (for extra_sql extensions).

losalamos.tools.database.gpkg_write_spatial(gdf: geopandas.GeoDataFrame, gpkg_path: Path, layer: str, crs: str = 'EPSG:4326') → None[source]#: Write a GeoDataFrame as a GeoPackage spatial layer via geopandas/Fiona.

losalamos.tools.database.gpkg_table_exists(con: Connection, table: str) → bool[source]#

losalamos.tools.database.gpkg_table_columns(con: Connection, table: str) → list[str][source]#

losalamos.tools.database.gpkg_table_info(con: Connection, table: str) → dict[str, dict][source]#: Return column metadata keyed by column name. Each value is a dict with keys: type, notnull, dflt_value.

losalamos.tools.database.gpkg_pk_col(con: Connection, table: str) → str[source]#: Return the primary key column name — ‘fid’ for Fiona tables, ‘id’ otherwise.

losalamos.tools.database.gpkg_code_id_map(con: Connection, table: str, code_col: str = 'code') → dict[str, int][source]#: Return {code: pk_id} for any table with a code column.

losalamos.tools.database.detect_category(con: Connection) → str[source]#: Heuristic: off-site if ‘samples’ table exists, else in-situ.

losalamos.tools.database.detect_has_time(con: Connection) → bool[source]#: Infer datetime resolution from an existing measurements row.

losalamos.tools.database.load_csv(path: str | Path, sep: str = ';', skiprows: int = 0) → pandas.DataFrame[source]#: Load a delimited text file into a DataFrame.

Parameters#

sep : field separator (default “;”) skiprows : number of preamble lines to skip before the header row

losalamos.tools.database.load_spatial(path: str | Path, layer: str | None = None) → geopandas.GeoDataFrame[source]#

losalamos.tools.database.resolve_fk(df: pandas.DataFrame, con: Connection, fk_rules: dict[str, dict]) → pandas.DataFrame[source]#

Replace code-based columns with integer FK ids.

Incoming CSV may use the FK column name (e.g. ‘site_id’) containing codes, OR alternative names like ‘sites_code’ or ‘site_code’. All candidates are tried.

losalamos.tools.database.normalise_datetime(df: pandas.DataFrame, has_time: bool) → pandas.DataFrame[source]#

losalamos.tools.database.align_columns(df: pandas.DataFrame, db_cols: list[str], identity_cols: list[str], col_info: dict[str, dict] | None = None) → pandas.DataFrame[source]#

Align df to the target table’s column set.

Columns in df that don’t exist in the table are dropped (with a warning).
Missing identity columns are a hard error UNLESS the schema declares a DEFAULT value for that column — in which case they are padded with it. This covers tier/quality (DEFAULT 0) arriving without those columns.
Missing non-identity columns are padded with their schema DEFAULT if one exists, otherwise NULL. A debug message notes each padded column.
The auto-generated pk columns (id, fid) are always excluded from the result.

col_info is the output of gpkg_table_info(). If None, missing columns are always padded with NULL (no default awareness).

losalamos.tools.database.insert_rows(con: Connection, table: str, df: pandas.DataFrame, identity_cols: list[str], mode: Literal['append', 'update'] = 'append') → dict[str, int][source]#

Insert df into table with duplicate handling.

append : INSERT OR IGNORE — duplicates skipped update : INSERT OR IGNORE + targeted UPDATE for collisions (never DELETEs rows, so FK children are safe)

losalamos.tools.database.cmd_initialize(procedures: list[dict[str, Any]]) → None[source]#

losalamos.tools.database.cmd_append(procedures: list[dict[str, Any]]) → None[source]#

losalamos.tools.database.cmd_update(procedures: list[dict[str, Any]]) → None[source]#

losalamos.tools.database.dispatch(command: str, specs: dict) → None[source]#

losalamos.tools.database.setup_logging(verbose: bool = False) → None[source]#

losalamos.tools.database.main(argv=None) → int[source]#