Python + SQL · No extra packages
A connected sample.
Running in minutes.
Load the sample, join its records and reproduce a resident timeline with a short local workflow.
Runs locally · Standard library only · Reproducible output
About five minutes
From download to a working join.
Python 3 only. No account, API key or additional packages.
- Download the connected sample ZIP and extract the entire folder.
- Open a terminal in the extracted
FCTE-Nairobi-Connected-Samplefolder, besidequickstart.py. - Choose your Python command below, then run it. The first line should report Python 3.
python quickstart.pyThe script checks packaged file hashes, loads the CSVs into SQLite and executes the supplied SQL. It requires no network access.
What a successful run creates
connected.sqliteSeven tables ready for SQL queries.resident-coverage.csvAll 32 resident counts, matched against the packaged expected result.timeline.csvThe selected resident's dated records and source pointers.checks.jsonFile integrity, row counts, relationship and date checks.
The files are written beside quickstart.py. A rerun replaces these generated outputs; source files stay unchanged.
Having trouble running the sample?
- Python command not found: use a Python 3 installation already available on your computer, or choose another command above. The workflow uses only Python's standard library.
- Cannot open quickstart.py: extract the full ZIP first, then open the terminal inside
FCTE-Nairobi-Connected-Sample. - Package file changed: extract a fresh copy and run it before editing the supplied CSV or SQL files. The loader verifies their hashes.
- Choose one of: the resident reference is outside this cohort. Use the resident selector below.
- Permission denied when writing: move the extracted folder to a location where you can create files, then run it again.
Know what to expect
32 residents. Reproducible results.
This is the expected summary produced by the supplied loader.
1082 | Mwangi Kinuthia | 5 enrollments | 1 contracts | 16 visits 1100 | Kaari Muriungi | 7 enrollments | 1 contracts | 2 visits 1268 | Kosgei Ngetich | 6 enrollments | 1 contracts | 6 visits
The report counts supplied records, not health, education or employment outcomes. The cohort was selected for records in all four areas.
First three of 32 residents shown, ordered by resident reference. The download includes every result.
Read and adapt the query
Join once. Keep the record counts true.
Count each related table separately before combining results to avoid multiplying enrollments by visits.
-- Query 1: Count each child table separately to avoid multiplying rows across joins.
SELECT r.citizen_id, r.name,
(SELECT COUNT(*) FROM education e WHERE e.citizen_id=r.citizen_id) AS enrollments,
(SELECT COUNT(*) FROM contracts c WHERE c.citizen_id=r.citizen_id) AS contracts,
(SELECT COUNT(*) FROM visits v JOIN patients p ON p.patient_id=v.patient_id
WHERE p.citizen_id=r.citizen_id) AS visits
FROM residents r ORDER BY r.citizen_id;
-- Query 2: A resident's dated records. Bind :citizen to an included citizen_id.
-- Household formation dates identify the household, not the resident's joining date.
SELECT r.name, e.event_date, e.domain, e.event_label, e.event_basis,
e.source_path, e.source_pointer
FROM events e JOIN residents r ON r.citizen_id=e.citizen_id
WHERE r.citizen_id=:citizen ORDER BY e.event_date, e.event_key;
-- Query 3: Chronology exceptions, restricted to person events.
SELECT e.event_key, e.event_date, r.birth_date, r.as_of
FROM events e JOIN residents r ON r.citizen_id=e.citizen_id
WHERE e.domain <> 'household'
AND (e.event_date < r.birth_date OR e.event_date > r.as_of);
The loader binds :citizen to the selected reference. To run the same SQL in another tool, open connected.sqlite and bind an included reference such as 3414569.
A task you can reproduce
Build a resident record-coverage report.
Use Query 1 to count each related table separately. The loader writes resident-coverage.csv and checks it against the packaged expected result.
Then choose a resident, inspect the dated sequence and follow a source pointer. This checks the supplied records; it does not measure complete life histories or real-world outcomes.
Follow a resident in the browser →Change the resident
Reproduce another timeline.
The downloadable sample contains 32 residents. Unknown references produce an explicit error.
python quickstart.py --citizen 832765Understand the package
Trace a row to the supplied profile.
Each exported row carries a source file and JSON pointer. Native IDs are preserved; generated record keys locate export rows.
| File | Purpose |
|---|---|
| residents.csv | Identity and birth dates |
| households.csv | Current household context |
| education.csv | Enrollment records |
| contracts.csv | Employment contracts |
| patients.csv | Health registration context |
| visits.csv | Dated visits linked to patients |
| events.csv | Derived timeline with source pointers |
| schema.sql | SQLite schema and declared foreign keys |
| sample.json | All tables, field names, relationship basis and scope |
| manifest.json | SHA256 values for packaged input files |
| README.md | Instructions and limitations |
If a hash check fails, extract a fresh copy before repeating the example. File hashes detect changes; they do not provide independent certification.