Quickstart¶
This guide is for read-only users — researchers who want to query the
database, explore projects, and pull data into pandas. It assumes you have
already installed the package and configured ~/.my.cnf.
1. Connecting to the database¶
The CCR database lives inside the LiSC network and is only reachable directly when you are on-site or on the VPN. From outside, you must tunnel through the SSH gateway first.
⚠ Prefer Option B (SSH tunnel) when working remotely.
Option A makes a direct TCP connection to the database host on every
init_pool()call. Opening and closing that connection repeatedly from outside the LiSC network will trigger fail2ban on the SSH gateway and get your IP temporarily banned. Always use Option B and keep the tunnel open for your entire session — it opens once and stays alive.
Option A — On-site / VPN (direct)¶
init_pool() connects straight to the DB host configured in ~/.my.cnf.
Nothing else is needed:
Option B — Remote (SSH tunnel)¶
The database is not directly reachable from outside LiSC. You need to open a
local port-forwarding tunnel through the SSH gateway first, then tell
init_pool() to connect through it.
Step 1 — Add local_port to ~/.my.cnf¶
In the [noxdb-ssh] section, add the local port the tunnel will bind to:
This is how init_pool() knows which port to look for.
Step 2 — Open the tunnel (once per session)¶
Run this in your terminal before starting any Python session:
Replace <host> with the value of host from the [noxdb] section of your
~/.my.cnf. The -f flag backgrounds the process; -N means no remote
command is run — the tunnel just stays open.
Step 3 — Verify the tunnel is alive¶
You should see a line like:
If the output is empty, the tunnel is not running. Re-run the ssh command.
Step 4 — Connect from Python¶
from noxdb import init_pool, close_pool
init_pool() # detects 127.0.0.1:3307 is listening and connects through it
# ... your queries ...
close_pool()
init_pool() checks whether local_port (3307) is already bound. If it is,
it connects through the existing tunnel without opening a new one. If it is
not, it raises an error — which is intentional: you should always start the
tunnel explicitly so you know it is running.
Killing the tunnel when you are done¶
2. Listing all projects¶
from noxdb import projects, transaction
with transaction() as cur:
proj_list = projects.list_all(cur)
| project_id | project_name | description |
|---|---|---|
| 7 | ADMCI_NED | |
| 10 | BAT_BATIOS_Kiefer | BAT (n=62) + BATIOS (n=74), 136 serum samples |
| 13 | BC-Engl | bladder cancer: Cis (n=141), Carbo (n=47), RCE (n=126), ICI (n=79), … |
| 19 | CRC_radiotherapy | diff. timepoints, 320 samples |
| 25 | HCC_MUW | 150 + 30 (TKI therapy) + 78 HCs + 48 TKI-treated |
| … | … | … |
| 58 | input | Control samples (input DNA) |
18 projects total — 17 study projects + the input umbrella project.
The dedicated mockIP / anchor / NC projects were removed in
migration 003; those controls are now linked to the study projects
that share their plate (see §11).
3. Project summary¶
from noxdb import queries
with transaction() as cur:
summary = queries.project_summary(cur, project_id=7)
{
"project_id": 7,
"n_subjects": 174,
"n_visits": 174,
"n_samples": 174,
"n_files": 348,
"files_by_type": {
"counts": 174,
"zigp_norm": 174
},
"n_controls": 64,
"controls_by_type": {
"mockIP": 32,
"anchor": 16,
"NC": 16
}
}
n_samples counts every sample linked to the project via
project_samples, controls included — here 110 study + 64 controls =
174. n_controls / controls_by_type break out just the control
subset (matched onto the project's plates by SQR + SQRP at import /
migration time).
4. Samples for a project¶
The main query for pulling all samples belonging to a project. Real
samples and their plate controls (mockIP, anchor, NC) are all project
members via project_samples, returned in one flat DataFrame. The
project_id column is always the queried project (7 for every row,
controls included) — tell rows apart by sample_type.
| project_id | subject_id | subject_code | visit_id | timepoint | sample_id | sample_name | sample_type | SQR | SQRP | library | antibody_class |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 7 | 649 | R14P02_77_FAU0001_ADMCI_NED_A_T_C2 | 649 | baseline | 649 | R14P02_77_FAU0001_ADMCI_NED_A_T_C2 | sample | 07 | 02 | A_T_C2 | None |
| … | … | … | … | … | … | … | … | … | … | … | … |
| 7 | 14926 | R14P02_81_Mock_1_A_T_C2 | 15838 | baseline | 15838 | R14P02_81_Mock_1_A_T_C2 | mockIP | 07 | 02 | A_T_C2 | None |
| … | … | … | … | … | … | … | … | … | … | … | … |
174 rows total (110 sample + 32 mockIP + 16 anchor + 16 NC).
Real samples only¶
with transaction() as cur:
df = queries.samples_for_project(cur, project_id=7, include_controls=False)
110 rows.
Filtering by file presence¶
with transaction() as cur:
df_with = queries.samples_for_project(cur, project_id=7, has_files=True)
df_without = queries.samples_for_project(cur, project_id=7, has_files=False)
174 with files, 0 without (file filter applies to both real samples and controls).
5. Subjects¶
from noxdb import subjects
with transaction() as cur:
subj_list = subjects.list_for_project(cur, project_id=7)
{
"subject_id": 649,
"subject_code": "R14P02_77_FAU0001_ADMCI_NED_A_T_C2",
"sex": "F",
"origin": "Netherlands",
"created_at": "2026-05-14T12:27:07"
}
174 subjects. subjects no longer carries a project_id (dropped in
migration 003); list_for_project now traverses
project_samples → samples → visits → subjects, so every subject with
a sample in the project — control subjects included — is returned.
6. Visits¶
{
"visit_id": 649,
"subject_id": 649,
"timepoint": "baseline",
"group_test": "Controls",
"age": 83,
"created_at": "2026-05-14T12:27:13"
}
7. Sample detail¶
{
"sample_id": 649,
"visit_id": 649,
"sample_name": "R14P02_77_FAU0001_ADMCI_NED_A_T_C2",
"sample_type": "sample",
"SQR": "07",
"SQRP": "02",
"library": "A_T_C2",
"antibody_class": null,
"created_at": "2026-05-14T12:27:18"
}
8. Samples with metadata¶
Equivalent to samples_for_project but includes all EAV metadata columns
joined in. Includes controls by default.
174 rows × 12 columns.
9. Files for a project¶
Returns every file registered for samples linked to the project —
study samples and controls, since both are project members via
project_samples.
| file_id | sample_id | sample_name | subject_code | timepoint | file_type | file_path | file_size_bytes | checksum_md5 | storage_tier | created_at |
|---|---|---|---|---|---|---|---|---|---|---|
| 226 | 649 | R14P02_77_FAU0001_ADMCI_NED_A_T_C2 | R14P02_77_FAU0001_ADMCI_NED_A_T_C2 | baseline | counts | /lisc/data/work/ccr/counts/R14P02_77_FAU0001_ADMCI_NED_A_T_C2.count.gz | None | None | work | 2026-05-14 12:27:23 |
| 229 | 649 | R14P02_77_FAU0001_ADMCI_NED_A_T_C2 | R14P02_77_FAU0001_ADMCI_NED_A_T_C2 | baseline | zigp_norm | /lisc/data/work/ccr/zigp/R14P02_77_FAU0001_ADMCI_NED_A_T_C2.csv | None | None | work | 2026-05-14 12:27:24 |
| … | … | … | … | … | … | … | … | … | … | … |
348 files total (174 counts + 174 zigp_norm).
10. Project tidy table¶
A single wide DataFrame: the subject → visit → sample lineage joined
to project membership (project_samples) with metadata pivoted into
columns. Includes controls. The standard starting point for downstream
analysis:
Shape: 174 rows × 12 columns.
11. Plate controls for a project¶
Controls (mockIP, anchor, NC) have no project of their own — they are
linked to every study project sharing their plate through
project_samples (done at import / migration time, matched by
SQR + SQRP). Because a plate can span multiple projects the same
control appears for several projects: one underlying row, many
membership links. The project_id column is therefore the queried
project.
| sample_id | sample_name | sample_type | SQR | SQRP | library | project_id |
|---|---|---|---|---|---|---|
| 15838 | R14P02_81_Mock_1_A_T_C2 | mockIP | 07 | 02 | A_T_C2 | 7 |
| 15841 | R14P02_82_Mock_2_A_T_C2 | mockIP | 07 | 02 | A_T_C2 | 7 |
| … | … | … | … | … | … | … |
64 controls total (32 mockIP + 16 anchor + 16 NC).
Filtering by type¶
with transaction() as cur:
mocks = queries.controls_for_project(cur, project_id=7, sample_types=["mockIP"])
anchors = queries.controls_for_project(cur, project_id=7, sample_types=["anchor"])
qc = queries.controls_for_project(cur, project_id=7, sample_types=["anchor", "NC"])
12. Input samples¶
Input DNA samples are not associated with any study project. Use
list_inputs() to retrieve all of them globally:
| sample_id | sample_name | sample_type | SQR | SQRP | library | project_id |
|---|---|---|---|---|---|---|
| 15616 | R01P02_1_input_A_v0_s | input | 01 | A_v0_s | 58 | |
| 15619 | R01P02_2_input_A_v0_s | input | 01 | A_v0_s | 58 | |
| … | … | … | … | … | … | … |
56 input rows total.
13. Shut down¶
Always close the pool when you are done:
In scripts, use a try/finally to guarantee cleanup even if a query fails:
try:
init_pool()
with transaction() as cur:
df = queries.samples_for_project(cur, project_id=7)
finally:
close_pool()
14. Full fetch example¶
The fetch module is the consumer side of noxdb: given a project, it
materialises the project structure, a tidy metadata table, and — when running
on LiSC or through an SFTP jump host — the actual files.
Project structure and file manifest¶
from noxdb import init_pool, close_pool, transaction
from noxdb import projects, queries
try:
init_pool()
with transaction() as cur:
project_row = projects.get(cur, project_id=7)
summary = queries.project_summary(cur, project_id=7)
dff = queries.files_for_project(cur, project_id=7)
finally:
close_pool()
project_row:
{
"project_id": 7,
"project_name": "ADMCI_NED",
"description": null,
"pi_name": "Arno Bourgonje",
"created_at": "2026-05-14T12:27:07"
}
summary:
{
"project_id": 7,
"n_subjects": 174,
"n_visits": 174,
"n_samples": 174,
"n_files": 348,
"files_by_type": {
"counts": 174,
"zigp_norm": 174
},
"n_controls": 64,
"controls_by_type": {
"mockIP": 32,
"anchor": 16,
"NC": 16
}
}
dff — first 6 rows of 348:
file_id sample_name file_type file_path storage_tier
226 R14P02_77_FAU0001_ADMCI_NED_A_T_C2 counts /lisc/data/work/ccr/counts/R14P02_77_FAU0001_ADMCI_NED_A_T_C2.count.gz work
229 R14P02_77_FAU0001_ADMCI_NED_A_T_C2 zigp_norm /lisc/data/work/ccr/zigp/R14P02_77_FAU0001_ADMCI_NED_A_T_C2.csv work
232 R14P02_74_FAU0002_ADMCI_NED_A_T_C2 counts /lisc/data/work/ccr/counts/R14P02_74_FAU0002_ADMCI_NED_A_T_C2.count.gz work
235 R14P02_74_FAU0002_ADMCI_NED_A_T_C2 zigp_norm /lisc/data/work/ccr/zigp/R14P02_74_FAU0002_ADMCI_NED_A_T_C2.csv work
238 R19P04_14_MG0213_ADMCI_NED_A_T_C2 counts /lisc/data/work/ccr/counts/R19P04_14_MG0213_ADMCI_NED_A_T_C2.count.gz work
241 R19P04_14_MG0213_ADMCI_NED_A_T_C2 zigp_norm /lisc/data/work/ccr/zigp/R19P04_14_MG0213_ADMCI_NED_A_T_C2.csv work
Exporting to a local folder¶
fetch.export_project combines metadata, a README, and (optionally) the
actual files into a single output directory. Pass include_files=False to
get just the metadata and README without downloading:
from noxdb import init_pool, close_pool, transaction
from noxdb import fetch
try:
init_pool()
with transaction() as cur:
result = fetch.export_project(
cur,
project_id=7,
output_dir="exports/ADMCI_NED",
include_files=False, # set True on LiSC to also pull files
metadata_formats=("csv",),
)
finally:
close_pool()
Output directory layout:
README.txt:
Project: ADMCI_NED (id=7)
PI: Arno Bourgonje
Description: -
Created: 2026-05-14 12:27:07
Counts:
subjects: 174
visits: 174
samples: 174
files: 348
Files by type:
counts: 174
zigp_norm: 174
result (the return value):
{
"project": { "project_id": 7, "project_name": "ADMCI_NED", ... },
"summary": { "n_subjects": 174, "n_files": 348, ... },
"metadata": { "csv": "exports/ADMCI_NED/metadata.csv" },
"readme": "exports/ADMCI_NED/README.txt",
"output_dir": "exports/ADMCI_NED"
}
To also download the actual files (requires running on LiSC or an active SFTP connection through the SSH gateway):
result = fetch.export_project(
cur,
project_id=7,
output_dir="exports/ADMCI_NED",
include_files=True,
file_types=["counts"], # omit to get all types
layout="by_sample", # or 'by_type' / 'flat'
)
The result["files"] key then contains downloaded, skipped, and failed
lists so you can see exactly what was fetched and what failed.
Where to go next¶
- API reference — every public function.
- Schema — the table layout.
- Install — prerequisites and
~/.my.cnfsetup.