cbdb-local

# CBDB Local (SQLite) Query the China Biographical Database locally — 656K+ historical Chinese figures, 7th c. BCE to 19th c. CE. No network calls after setup. ## When to Use - **Use cbdb-local** (this skill) for: offline access, complex queries, batch analysis, full kinship/social network traversal, raw SQL queries - **Use cbdb-api** for: quick lookups when database isn't downloaded, or when disk space is limited ## Setup Download and extract the SQLite database (~556 MB uncompressed): ```bash python3 scripts/cbdb_query.py setup ``` The script resolves the **current download URL** from the project's [`latest.json`](https://github.com/cbdb-project/cbdb_sqlite/blob/master/latest.json) (CBDB now hosts the database on Hugging Face, not GitHub LFS), downloads the `.zip`, verifies its SHA-256, extracts it, creates search indexes, and records the release version. No external tools are required for the default download. Setup is one-time. To check whether a newer release is available, or to update: ```bash python3 scripts/cbdb_query.py check # Compare local vs. latest release python3 scripts/cbdb_query.py setup --force # Re-download the latest release ``` ### ZZZ denormalized tables (optional) The default database uses standard tables. The deprecated `ZZZ_*` denormalized tables (see raw-SQL examples below) ship in a separate archive: ```bash python3 scripts/cbdb_query.py setup --zzz ``` This variant is a `.7z` archive and **requires 7z**: - macOS: `brew install 7zip` - Windows: `winget install 7zip.7zip` or download from https://www.7-zip.org/ - Linux: `sudo apt install p7zip-full` ## Quick Start ### Search by Name ```bash # Chinese name (exact match, then alt names, then fuzzy) python3 scripts/cbdb_query.py person "蘇軾" # Pinyin name python3 scripts/cbdb_query.py person "Su Shi" # By person ID (returns full profile) python3 scripts/cbdb_query.py person --id 3767 ``` Single match returns a **full profile** (bio + alt names + kinship + offices + associations + addresses + entries + status + texts + institutions + posting addresses). Multiple matches return a list of candidates. ### Specific Data ```bash python3 scripts/cbdb_query.py kinship 3767 # Family tree python3 scripts/cbdb_query.py offices 3767 # Official postings python3 scripts/cbdb_query.py associations 3767 # Social network python3 scripts/cbdb_query.py addresses 3767 # Places/addresses python3 scripts/cbdb_query.py entries 3767 # Exam/entry records python3 scripts/cbdb_query.py altnames 3767 # Alternative names python3 scripts/cbdb_query.py status 3767 # Social status python3 scripts/cbdb_query.py texts 3767 # Writings/texts python3 scripts/cbdb_query.py institutions 3767 # Academies, temples, etc. python3 scripts/cbdb_query.py postaddr 3767 # Places of official service ``` ### Raw SQL ```bash python3 scripts/cbdb_query.py sql "SELECT c_personid, c_name_chn, c_birthyear FROM BIOG_MAIN WHERE c_dy = 15 AND c_birthyear > 1000 LIMIT 10" ``` Only SELECT queries are allowed. For complex queries, the `ZZZ_` denormalized tables (e.g., `ZZZ_ENTRY_DATA`, `ZZZ_KIN_BIOG_ADDR`) pre-join names and descriptions — much simpler than multi-table joins. These tables are **only present if you ran `setup --zzz`** (they are deprecated upstream in favor of SQL views). See `references/database_schema.md` for the full list. ## Output Format All commands output JSON. Example for `person "蘇軾"`: ```json { "basic_info": { "person_id": 3767, "name_chn": "蘇軾", "name_pinyin": "Su Shi", "dynasty_chn": "宋", "birth_year": 1036, "death_year": 1101 }, "alt_names": [ { "name_chn": "子瞻", "type_chn": "字", "type": "Courtesy name" }, { "name_chn": "東坡居士", "type_chn": "室名、別號", "type": "Studio name, Style name" } ], "kinship": [ { "name_chn": "蘇洵", "relation_chn": "父", "relation": "F" }, { "name_chn": "蘇轍", "relation_chn": "弟", "relation": "B-" } ], "offices": [...], "associations": [...], "addresses": [...], "entries": [...], "status": [...], "texts": [ { "title_chn": "東坡集", "role_chn": "撰著者", "role": "Author" } ], "institutions": [...], "posted_addresses": [...] } ``` ## Kinship Tree Visualization Generate family tree diagrams from CBDB kinship data. ### Tree Commands ```bash # Graphviz DOT format (recommended for quality) python3 scripts/cbdb_query.py tree 3767 -d 2 -f dot -o su_shi.dot # Render DOT to image (requires: brew install graphviz) dot -Tsvg su_shi.dot -o su_shi.svg dot -Tpng su_shi.dot -o su_shi.png # Mermaid format (paste into GitHub, Obsidian, etc.) python3 scripts/cbdb_query.py tree 3767 -d 2 -f mermaid -o su_shi.md # Direct SVG (requires graphviz, falls back to DOT if missing) python3 scripts/cbdb_query.py tree 3767 -d 2 -f svg -o su_shi.svg ``` ### Visual Conventions - **Gold** node = ego (the queried person) - **Blue** nodes = male, **Pink** nodes = female - **Solid arrows** = parent → child - **Dashed lines** = marriage / affinal relations - **Dotted lines** = siblings - Nodes show name and birth–death years when known - Generations are laid out top-to-bottom (ancestors above, descendants below) ### Options | Flag | Description | Default | |---|---|---| | `--depth` / `-d` | BFS expansion depth from ego | `2` | | `--format` / `-f` | `dot`, `mermaid`, or `svg` | `dot` | | `--output` / `-o` | Output file path | Auto-named | Depth 1 = ego's direct kin only. Depth 2 = kin of kin (recommended). Depth 3+ can produce very large trees. ## Network Export (for Gephi / Graph Analysis) Export CBDB relationships as network graphs in GEXF (Gephi native), CSV (nodes + edges), or JSON. ### Export Commands ```bash # Kinship network from a person, expanding outward python3 scripts/cbdb_query.py export-kinship 3767 -d 2 -o su_shi_kin.gexf # Social association network python3 scripts/cbdb_query.py export-associations 3767 -d 1 -o su_shi_assoc.gexf # Combined kinship + associations python3 scripts/cbdb_query.py export-network 3767 -d 1 -o su_shi_full.gexf # Co-office network: people who held the same office python3 scripts/cbdb_query.py export-office "翰林學士" --dynasty 15 -o hanlin.gexf # Person-place bipartite network python3 scripts/cbdb_query.py export-place "眉山" -o meishan.gexf ``` ### Options | Flag | Description | Default | |---|---|---| | `--format` / `-f` | `gexf`, `csv`, or `json` | `gexf` | | `--depth` / `-d` | Expansion depth (1 = direct, 2 = friends-of-friends) | `1` | | `--output` / `-o` | Output file path | Auto-named | | `--dynasty` | Filter by dynasty code (e.g., 15 = Song) | All | | `--year-from` | Filter postings from this year | None | | `--year-to` | Filter postings up to this year | None | | `--addr-type` | Filter place network by address type (1 = 籍貫) | All | ### Network Types | Command | Nodes | Edges | Use Case | |---|---|---|---| | `export-kinship` | Persons | Kin relations (labeled F, M, S, B, W, etc.) | Family tree / clan analysis | | `export-associations` | Persons | Social ties (friend, teacher, political ally) | Social network analysis | | `export-network` | Persons | Both kin + social | Combined prosopography | | `export-office` | Persons | Co-officeholding | Political elite networks | | `export-place` | Persons + Places | Affiliations | Spatial analysis, migration | ### Multiple Seeds All person-based exports accept multiple person IDs: ```bash python3 scripts/cbdb_query.py export-kinship 3767 1762 -d 1 -o su_wang.gexf ``` ### Node Attributes (available in Gephi) `name_chn`, `name_pinyin`, `gender`, `dynasty_chn`, `dynasty`, `birth_year`, `death_year`, `index_year`. Place nodes add `longitude`, `latitude`, `node_type`. ### Edge Attributes `type` (kinship/association/co-office/person-place), `relation_chn`, `relation`. ### Tips - **Start with depth=1** — depth=2 can produce very large networks (thousands of nodes) - **CSV format** produces two files: `*_nodes.csv` and `*_edges.csv`, directly importable into Gephi via "Import Spreadsheet" - **GEXF** is recommended — preserves all attributes and is Gephi's native format - **Office codes are dynasty-specific** — use `--dynasty` to narrow results, or omit for cross-dynasty comparison ## Important Caveats - **Index year is artificial**: Derived estimate of birth year, not a real date. Many people have 0 (unknown) and are excluded by year filters. - **Song dynasty = one code (15)**: Does not distinguish Northern from Southern Song. - **Office codes are dynasty-specific**: A Song office code won't match in a Ming query. Search by Chinese office name for cross-dynasty comparison. - **Association dates are usually empty**: Filter by index year of individuals instead. - **Extended kinship searches can be huge**: Start with conservative distance parameters. ## Using in Python ```python import sys sys.path.insert(0, "scripts") from cbdb_query import ( get_connection, search_person, get_full_profile, get_kinship, get_offices, get_associations, get_texts, get_institutions, get_posted_addresses, run_sql, build_kinship_network, build_association_network, build_combined_network, build_office_network, build_place_network, export_network, ) conn = get_connection() # Search results = search_person(conn, "王安石") print(f"Found {len(results)} results") # Full profile profile = get_full_profile(conn, 3767) # Su Shi for kin in profile["kinship"]: print(f"{kin['relation_chn']}: {kin['name_chn']}") # Raw SQL for advanced queries rows = run_sql(conn, """ SELECT b.c_name_chn, COUNT(k.c_kin_id) as kin_count FROM BIOG_MAIN b JOIN KIN_DATA k ON k.c_personid = b.c_personid WHERE b.c_dy = 15 GROUP BY b.c_personid ORDER BY kin_count DESC LIMIT 10 """) # Export network for Gephi network = build_kinship_network(conn, [3767], depth=2) export_network(network, "su_shi_kin.gexf", "gexf") ``` ## Database Coverage | Table | Records | Description | |---|---|---| | BIOG_MAIN | 656,436 | Core biographical records | | POSTING_DATA | 601,663 | Career posting records | | POSTED_TO_OFFICE_DATA | 601,002 | Office assignments with titles | | KIN_DATA | 553,330 | Kinship/family relationships | | BIOG_ADDR_DATA | 455,705 | Person-place associations | | ENTRY_DATA | 263,267 | Exam/entry records | | ALTNAME_DATA | 206,132 | Alternative names (zi, hao, etc.) | | ASSOC_DATA | 186,876 | Social associations | | STATUS_DATA | 69,344 | Social status records | | BIOG_TEXT_DATA | ~50,000 | Person-text relations | | BIOG_INST_DATA | ~10,000 | Person-institution relations | | OFFICE_CODES | 34,052 | Office title lookup | | ADDR_CODES | 30,099 | Place name lookup (with coordinates) | | DYNASTIES | 85 | Dynasty lookup | ## Key Person IDs for Testing | Person | ID | Dynasty | |---|---|---| | 蘇軾 Su Shi | 3767 | 宋 | | 王安石 Wang Anshi | 1762 | 宋 | | 歐陽修 Ouyang Xiu | 4766 | 宋 | | 司馬光 Sima Guang | 3692 | 宋 | | 李白 Li Bai | 21646 | 唐 | | 杜甫 Du Fu | 21831 | 唐 | ## Comparison with cbdb-api | Feature | cbdb-local | cbdb-api | |---|---|---| | Setup | Download ~556 MB | None | | Speed | Instant (local SQLite) | Network latency + rate limits | | Offline | Yes | No | | Raw SQL | Yes | No | | Batch queries | Fast | Rate-limited | | Data freshness | Download date | Always current | ## Resources - `scripts/cbdb_query.py` — Standalone query tool (Python 3.9+, zero dependencies) - `references/database_schema.md` — Full SQLite schema and query guide - Source: [cbdb-project/cbdb_sqlite](https://github.com/cbdb-project/cbdb_sqlite) - CBDB Project: https://projects.iq.harvard.edu/cbdb