astrodbtoolkit / astrodbtoolkit/astrodb-bot

Empty JSON array ([]) as a lookup-table reference file causes a confusing IntegrityError

Open
#99 1 comment 0 reactions 0 assignees View on GitHub
Dominant language
Python
Stars
1
Forks
4
Avg merge
2d 10h
Merged PRs (30d)
5

Description

**Where:** `astrodbkit.astrodb.Database.load_table()` (in the `astrodbkit` package, not this repo) does `conn.execute(self.metadata.tables[table].insert().values(data))` where `data` comes straight from `json.load()`. If the JSON file's top-level array is empty (`[]`), SQLAlchemy's `insert().values([])` is interpreted as "insert one row using column defaults" (a known SQLAlchemy quirk: an empty list to a multi-row `.values()` isn't a no-op), not "insert nothing."

**What happened:** After emptying `Publications.json`, `SourceTypeList.json`, and `ParameterList.json` to `[]` (clearing stale template-example placeholder rows before real data existed), `build_db_from_json()` failed with `sqlite3.IntegrityError: NOT NULL constraint failed: Publications.reference` — a single default-valued row was attempted, which violated the non-nullable `reference` column. The error message gives no hint that the root cause was an empty-array JSON file rather than a real data problem.

**Workaround:** Deleted the three now-empty JSON files entirely instead of leaving them as `[]` — `load_table()`'s existing `if os.path.exists(filename):` guard cleanly skips a missing file (with an optional verbose "not found" message), which is the actual correct way to represent "no data yet" for a reference table.

**Suggested change:** Either (a) `astrodbkit.astrodb.Database.load_table()` should special-case `if not data: return` before calling `insert().values(data)`, or (b) any astrodb-bot skill/reference doc describing the `data/reference/*.json` convention should explicitly say "represent an empty table by omitting the file, not by writing `[]`" so users don't hit this SQLAlchemy quirk.

---
_Reported from a gotchas.md log filed by a skill user (2026-08-28)._

Contributor guide

No contributing guide indexed for this repository

Research direction

Start with the described Database.load_table() entry point in the astrodbkit package and verify how json.load() data reaches insert().values(data) when the file contains []. Compare that behavior with the existing missing-file path and the astrodb-bot reference documentation. Done means empty reference data no longer produces a misleading IntegrityError, either through the package behavior or an explicit documented convention.

Written by the indexing model from the issue text.

Assessment

Tech stack
python, sqlalchemy, sqlite
Domain
database, documentation
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Active
Clarity
Mostly clear
Newbie friendliness
58/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.