This is a minor patch of the package.

New additions:

• a live demo for the API has been deployed on Render
• example notebook with demo requests from the Render app

Fixes:

• minor bug corrected in CLI: queens serve method now correctly allost passing --host
• response shape for empty dataframe aligned to default schema (including the table_description)

QUEENS v0.1.1 — First public release

QUEryable Energy National Statistics (QUEENS) — an ETL + API toolkit that ingests UK National Statistics Excel tables (DUKES & related) into SQLite, stages a versioned snapshot, exposes a FastAPI service, and ships a simple CLI + Python facade.

Highlights

• ETL pipeline for DUKES-style Excel workbooks into a normalized SQLite schema.
• Versioned staging: promote a consistent snapshot of each table to _prod.
• FastAPI service for querying staged data with validated filters and cursor pagination.
• CLI for ingesting, staging, exploring info/metadata, exporting, and serving the API.
• Python library facade for programmatic access: ingest, stage, query, metadata, versions, info, export.
• User-configurable paths (DB, exports, templates) stored under a user data directory via platformdirs.

What’s included

• Package: queens (requires Python ≥ 3.9).
• Configs: default etl_config.json, schema.json, templates.json, urls.json are copied to a user data dir on first import.
• Mapping templates: Excel files for column/index mapping (bundled and copied to the user dir).
• SQLite schema: auto-generated raw/prod tables + metadata and ingest logs.

CLI (quick taste)

# show / set paths
queens config --show-current
queens config --db-path ~/queens/queens.db --export-path ~/queens/exports

# ingest specific table(s) or all
queens ingest dukes --table 1.1 --table 6.1
queens ingest dukes

# stage latest snapshot (or as-of date)
queens stage dukes --as_of_date 2025-05-26

# explore
queens info dukes                # table stats (staged)
queens info dukes --meta         # queryable columns per table
queens info dukes --vers         # ingested versions

# export
queens export dukes --table 1.1 --file-type parquet
queens export dukes --bulk --file-type xlsx

# serve API
queens serve --host 127.0.0.1 --port 8000

API

• GET /metadata/{collection}?table_name=... → list queryable columns & dtypes for a specific table.
• GET /data/{collection}?table_name=...&filters=...&limit=...&cursor=...
  • filters: JSON (flat or nested). Examples:
    {"year": 2022} or {"year": {"gte": 2010}, "fuel": {"like": "%gas%"}} or {"$or": [{"fuel":"Gas"},{"fuel":"Coal"}]}
  • pagination: pass next_cursor from the previous response to fetch next page.

Python facade

import queens as q

q.setup_logging(level="info")     # optional
q.ingest("dukes", tables=["5.1", "5.2"])
q.stage("dukes")

# query staged data
df = q.query(
    "dukes", "6.1",
    filters={"year": {"gt": 2010}, "$or": [{"fuel": "Wood"}, {"fuel": {"like": "%gas%"}}]}
)

meta = q.metadata("dukes", "6.1")
vers = q.versions("dukes")
info = q.info("dukes")

Data model & validation

• Schema defined in schema.json (copied to user dir). All ingested frames are validated:
  • uniqueness on meaningful index columns (ignoring working columns like row/label).
  • dtypes and nullability enforced per column.
• Metadata (_metadata) auto-refreshed on stage; used to validate API/library filters.

Documentation

Full docs live in docs/:

• docs/architecture.md, docs/configuration.md, docs/versioning.md, docs/filters.md, docs/cli.md, docs/api.md, docs/library.md, docs/troubleshooting.md

Install

pip install queens

Requires: pandas, openpyxl, requests, fastapi, uvicorn, typer, beautifulsoup4, platformdirs, pyarrow.

Known limitations

• Current ETL config targets a subset of DUKES tables; coverage will expand over time.
• API returns 404/422 for unknown tables / invalid filters; 500 for DB errors.
• This is a 0.x release: public API may evolve.

Upgrading

• Minor/patch releases will not break CLI commands lightly; check release notes for changes to ETL configs or schema.

Acknowledgements

The design is informed by hands‑on experience producing DUKES/Energy Trends tables and repeatedly fielding requests for “flat”, machine‑readable extracts for analysis and modelling.

If you find issues or have suggestions, please open an issue or PR on the repository. Thank you for trying QUEENS!

Releasing a pre-made database with all tables from DUKES 2025 ingested and staged. Ready to use in a demo API service.