1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
|
"""
This script dumps phenotype data from MySQL into a LMDB matrix.
Usage (inside a guix shell with the required deps):
: guix shell python-wrapper python-lmdb python-mysqlclient python-numpy python-click
## dump phenotypes
python lmdb_phenotypes_matrix.py dump-all-datasets \
"mysql://webqtlout:webqtlout@localhost/db_webqtl" \
/home/kabui/test_lmdb_data
# to skip if dataset exists pass --skip_existing
python lmdb_phenotypes_matrix.py dump-all-datasets \
"mysql://webqtlout:webqtlout@localhost/db_webqtl" \
/home/kabui/test_lmdb_data --skip-existing
# dumping a single dataset
python lmdb_phenotypes_matrix.py dump-phenotypes \\
"mysql://user:pass@localhost/db_webqtl" \\
/path/to/lmdb \\
BXDPublish
## secondary functionality
# print the stored matrix
python lmdb_phenotypes_matrix.py print-phenotype-matrix \
/home/kabui/test_lmdb_data
# round-trip sanity check (dump → read → compare)
python lmdb_phenotypes_matrix.py sanity-check \
"mysql://webqtlout:webqtlout@localhost/db_webqtl" \
/home/kabui/test_lmdb_data
"""
import json
import os
import logging
from collections import defaultdict
from dataclasses import dataclass
from typing import Optional
from urllib.parse import urlparse
import click
import lmdb
import MySQLdb
import numpy as np
from numpy.testing import assert_array_equal
logging.basicConfig(level=logging.INFO, format="%(levelname)s: %(message)s")
logger = logging.getLogger(__name__)
DTYPE: str = "<f8" # little-endian float64
MISSING_VALUE: str = "X" # placeholder for absent strains
DEFAULT_PORT: int = 3306
LMDB_MAP_SIZE: int = 100 * 1024 * 1024 * 1024 # 100 GB
FETCH_PHENOTYPES_QUERY: str = """
SELECT PublishXRef.Id, Strain.Name, PublishData.Value, PublishSE.error
FROM PublishData
INNER JOIN Strain ON PublishData.StrainId = Strain.Id
INNER JOIN PublishXRef ON PublishData.Id = PublishXRef.DataId
INNER JOIN PublishFreeze ON PublishXRef.InbredSetId = PublishFreeze.InbredSetId
LEFT JOIN PublishSE ON PublishSE.DataId = PublishData.Id
AND PublishSE.StrainId = PublishData.StrainId
LEFT JOIN NStrain ON NStrain.DataId = PublishData.Id
AND NStrain.StrainId = PublishData.StrainId
WHERE PublishFreeze.Name = %s
AND PublishFreeze.public > 0
AND PublishData.value IS NOT NULL
AND PublishFreeze.confidentiality < 1
ORDER BY LENGTH(Strain.Name), Strain.Name
"""
FETCH_ALL_DATASETS_QUERY: str = """
SELECT DISTINCT PublishFreeze.Name, PublishFreeze.FullName, PublishFreeze.ShortName
FROM PublishFreeze
WHERE PublishFreeze.public > 0
AND PublishFreeze.confidentiality < 1
ORDER BY PublishFreeze.Name
"""
@dataclass(frozen=True)
class PhenoMatrix:
"""Immutable container for the phenotype matrix and its labels.
Rows = traits, columns = strains (row-major / C-order in memory).
"""
matrix: np.ndarray # shape (n_traits, n_strains), dtype <f8
traits: list[str]
strains: list[str]
se_matrix: Optional[np.ndarray] = None # optional SE values, same shape
@dataclass(frozen=True)
class SerializedPhenoMatrix:
"""Byte-level representation ready to be written to LMDB."""
matrix_bytes: bytes
metadata_bytes: bytes
se_matrix_bytes: Optional[bytes] = None
DEFAULT_PORT = 3306
@dataclass(frozen=True)
class DBConnectionParams:
"""Parsed connection parameters — never printed in full."""
host: str
user: str
password: str
database: str
port: int
def parse_connection_params(sql_uri: str) -> DBConnectionParams:
"""Parse a mysql:// URI into connection parameters."""
parsed = urlparse(sql_uri)
host = parsed.hostname or "127.0.0.1"
# Force TCP if localhost check unix socket connection
if host == "localhost":
host = "127.0.0.1"
return DBConnectionParams(
host=host,
user=parsed.username or "",
password=parsed.password or "",
database=parsed.path.lstrip("/"),
port=parsed.port or DEFAULT_PORT,
)
def open_db_connection(params: DBConnectionParams):
"""Open a MySQL connection from parsed parameters."""
logger.info(
"Connecting to %s@%s:%s/%s",
params.user,
params.host,
params.port,
params.database,
)
return MySQLdb.connect(
host=params.host,
user=params.user,
passwd=params.password,
db=params.database,
port=params.port
)
def open_lmdb(db_path: str, *, create: bool = False) -> lmdb.Environment:
"""Open (or create) an LMDB environment."""
return lmdb.open(db_path, map_size=LMDB_MAP_SIZE, create=create)
def fetch_all_datasets(sql_uri: str) -> list[dict[str, str]]:
"""Query MySQL for all available public phenotype datasets.
Returns:
List of dicts with 'name', 'full_name', and 'short_name' for each dataset.
"""
params = parse_connection_params(sql_uri)
datasets = []
with open_db_connection(params) as conn:
cursor = conn.cursor()
cursor.execute(FETCH_ALL_DATASETS_QUERY)
for name, full_name, short_name in cursor.fetchall():
datasets.append(
{
"name": name,
"full_name": full_name or name,
"short_name": short_name or name,
}
)
return datasets
def fetch_phenotypes(
dataset_name: str, sql_uri: str
) -> tuple[set[str], dict[int, dict[str, float]], dict[int, dict[str, float]]]:
"""Query MySQL for all phenotype values and SE belonging to *dataset_name*.
Returns:
strains – set of all strain names encountered.
datasets – {trait_id: {strain_name: value}}.
se_data – {trait_id: {strain_name: se_value}}.
"""
params = parse_connection_params(sql_uri)
datasets: dict[int, dict[str, float]] = defaultdict(dict)
se_data: dict[int, dict[str, float]] = defaultdict(dict)
strains: set[str] = set()
with open_db_connection(params) as conn:
cursor = conn.cursor()
cursor.execute(FETCH_PHENOTYPES_QUERY, (dataset_name,))
for trait_id, strain_name, value, se_error in cursor.fetchall():
datasets[trait_id][strain_name] = value
if se_error is not None:
se_data[trait_id][strain_name] = se_error
strains.add(strain_name)
return strains, dict(datasets), dict(se_data)
def _to_float(value) -> float:
"""Cast a single value to float64, defaulting to NaN on failure."""
try:
return float(value)
except (ValueError, TypeError):
return float("nan")
def _fill_missing(
trait_data: dict[str, float],
strains: list[str],
default: str = MISSING_VALUE,
) -> list[float]:
"""Return one row (all strains) for a trait, filling gaps with *default*.
The default is passed through _to_float so missing values become NaN.
"""
return [_to_float(trait_data.get(s, default)) for s in strains]
def build_pheno_matrix(
datasets: dict[int, dict[str, float]],
se_data: dict[int, dict[str, float]],
strains: list[str],
) -> PhenoMatrix:
"""Convert the raw dataset dict into a PhenoMatrix with optional SE values."""
sorted_strains = sorted(strains) # deterministic column order
traits = list(datasets.keys())
# TODO; check if traits are missing
value_rows = [_fill_missing(datasets[t], sorted_strains) for t in traits]
# Build SE matrix if we have SE data
se_matrix = None
if se_data:
se_rows = [_fill_missing(se_data.get(t, {}), sorted_strains) for t in traits]
se_matrix = np.array(se_rows, dtype=DTYPE)
return PhenoMatrix(
matrix=np.array(value_rows, dtype=DTYPE),
traits=[str(t) for t in traits],
strains=sorted_strains,
se_matrix=se_matrix,
)
def build_metadata(pheno: PhenoMatrix, dataset_name: str) -> dict:
"""Derive metadata purely from the PhenoMatrix.
dtype is read from the array itself so it can never drift out of sync.
"""
rows, columns = pheno.matrix.shape
return {
"rows": rows,
"columns": columns,
"dtype": str(pheno.matrix.dtype),
"order": "C",
"endian": "little",
"traits": pheno.traits,
"strains": pheno.strains,
"transposed": False,
"has_se": pheno.se_matrix is not None,
"dataset": dataset_name,
}
def serialize(pheno: PhenoMatrix, dataset_name: str) -> SerializedPhenoMatrix:
"""Serialize a PhenoMatrix into the byte-blobs LMDB will store."""
se_bytes = None
if pheno.se_matrix is not None:
se_bytes = pheno.se_matrix.tobytes(order="C")
return SerializedPhenoMatrix(
matrix_bytes=pheno.matrix.tobytes(order="C"),
metadata_bytes=json.dumps(build_metadata(pheno, dataset_name)).encode("utf-8"),
se_matrix_bytes=se_bytes,
)
def read_metadata(txn: lmdb.Transaction) -> dict:
"""Read and decode the metadata JSON from an open transaction."""
raw = txn.get(b"pheno_metadata")
if raw is None:
raise KeyError("Key 'pheno_metadata' not found in LMDB")
return json.loads(raw.decode("utf-8"))
def reconstruct_matrix(txn: lmdb.Transaction) -> PhenoMatrix:
"""Reconstruct a PhenoMatrix from an open LMDB transaction."""
metadata = read_metadata(txn)
raw_bytes = txn.get(b"pheno_matrix")
if raw_bytes is None:
raise KeyError("Key 'pheno_matrix' not found in LMDB")
matrix = np.frombuffer(raw_bytes, dtype=metadata["dtype"]).reshape(
metadata["rows"], metadata["columns"]
)
# Try to load SE matrix if it exists
se_matrix = None
if metadata.get("has_se", False):
se_raw = txn.get(b"pheno_se_matrix")
if se_raw is not None:
se_matrix = (
np.frombuffer(se_raw, dtype=metadata["dtype"])
.reshape(metadata["rows"], metadata["columns"])
.copy()
)
return PhenoMatrix(
matrix=matrix.copy(), # frombuffer is read-only; copy to own the memory
traits=metadata["traits"],
strains=metadata["strains"],
se_matrix=se_matrix,
)
def fetch_single_trait(lmdb_path: str, trait_name: str) -> dict[str, float]:
"""Fetch one trait's values for all strains without rebuilding the full matrix.
Slices the raw byte buffer at the exact row offset — only the bytes
for the requested trait are decoded.
"""
with open_lmdb(lmdb_path) as env:
with env.begin() as txn:
metadata = read_metadata(txn)
traits = metadata["traits"]
if trait_name not in traits:
raise KeyError(f"Trait '{trait_name}' not found in stored metadata")
row_idx = traits.index(trait_name)
dtype = np.dtype(metadata["dtype"])
bytes_per_row = metadata["columns"] * dtype.itemsize
start = row_idx * bytes_per_row
raw = txn.get(b"pheno_matrix")
row_values = np.frombuffer(
raw, dtype=dtype, count=metadata["columns"], offset=start
)
return dict(zip(metadata["strains"], row_values.tolist()))
def write_to_lmdb(lmdb_path: str, serialized: SerializedPhenoMatrix) -> None:
"""Persist the serialized matrix + metadata into LMDB.
Isolated so everything above can be tested without a database.
"""
with open_lmdb(lmdb_path, create=True) as env:
with env.begin(write=True) as txn:
txn.put(b"pheno_matrix", serialized.matrix_bytes)
txn.put(b"pheno_metadata", serialized.metadata_bytes)
if serialized.se_matrix_bytes is not None:
txn.put(b"pheno_se_matrix", serialized.se_matrix_bytes)
logger.info("Matrix written to %s", lmdb_path)
def prepare_and_dump(dataset_name: str, sql_uri: str, lmdb_path: str) -> PhenoMatrix:
"""Orchestrate fetch → build → serialize → write.
Returns the PhenoMatrix so callers (e.g. sanity_check) can reuse it.
"""
strains, datasets, se_data = fetch_phenotypes(dataset_name, sql_uri)
pheno = build_pheno_matrix(datasets, se_data, list(strains))
write_to_lmdb(lmdb_path, serialize(pheno, dataset_name))
return pheno
def load_from_lmdb(lmdb_path: str) -> PhenoMatrix:
"""Read and reconstruct the full PhenoMatrix from LMDB."""
with open_lmdb(lmdb_path) as env:
with env.begin() as txn:
return reconstruct_matrix(txn)
def verify_roundtrip(dumped: PhenoMatrix, loaded: PhenoMatrix) -> None:
"""Assert that a dumped matrix round-trips perfectly through LMDB."""
assert_array_equal(dumped.matrix, loaded.matrix)
assert dumped.traits == loaded.traits, "traits mismatch after round-trip"
assert dumped.strains == loaded.strains, "strains mismatch after round-trip"
logger.info("Round-trip sanity check passed.")
@click.group()
def cli():
"""Phenotype LMDB toolbox."""
@cli.command("list-datasets")
@click.argument("sql_uri")
def list_datasets_cmd(sql_uri: str) -> None:
"""List all available public phenotype datasets in the database."""
datasets = fetch_all_datasets(sql_uri)
if not datasets:
logger.warning("No public datasets found")
return
logger.info("Found %d public dataset(s):", len(datasets))
# Print in a table format
max_name_len = max(len(d["name"]) for d in datasets)
max_short_len = max(len(d["short_name"]) for d in datasets)
header = f"{'Name':<{max_name_len}} {'Short Name':<{max_short_len}} Full Name"
print(header)
print("-" * len(header))
for dataset in datasets:
print(
f"{dataset['name']:<{max_name_len}} "
f"{dataset['short_name']:<{max_short_len}} "
f"{dataset['full_name']}"
)
@cli.command("dump-phenotypes")
@click.argument("sql_uri")
@click.argument(
"lmdb_path",
type=click.Path(file_okay=False, path_type=str),
)
@click.argument("dataset", default="BXDPublish")
def dump_phenotypes_cmd(sql_uri: str, lmdb_path: str, dataset: str) -> None:
"""Fetch phenotypes from MySQL and write the matrix to LMDB.
Arguments:
sql_uri: MySQL connection URI
lmdb_path: Path to LMDB directory
dataset: Dataset name (default: BXDPublish)
Example:
python lmdb_phenotypes_matrix.py dump-phenotypes \\
"mysql://user:pass@localhost/db_webqtl" \\
/path/to/lmdb \\
BXDPublish
"""
prepare_and_dump(dataset, sql_uri, lmdb_path)
@cli.command("dump-all-datasets")
@click.argument("sql_uri")
@click.argument(
"lmdb_base_path",
type=click.Path(file_okay=False, path_type=str),
)
@click.option(
"--skip-existing/--no-skip-existing",
default=False,
help="Skip datasets that already have LMDB directories (default: True)",
)
def dump_all_datasets_cmd(
sql_uri: str, lmdb_base_path: str, skip_existing: bool
) -> None:
"""Fetch all public datasets and dump each to its own LMDB directory.
Each dataset will be stored in: <lmdb_base_path>/<dataset_name>/
Example:
python lmdb_phenotypes_matrix.py dump-all-datasets \\
"mysql://user:pass@localhost/db_webqtl" \\
/data/phenotypes
This creates:
/data/phenotypes/BXDPublish/
/data/phenotypes/LXSPublish/
etc.
"""
datasets = fetch_all_datasets(sql_uri)
if not datasets:
logger.warning("No public datasets found")
return
logger.info("Found %d dataset(s) to dump", len(datasets))
success_count = 0
skip_count = 0
fail_count = 0
for idx, dataset_info in enumerate(datasets, 1):
dataset_name = dataset_info["name"]
lmdb_path = os.path.join(lmdb_base_path, dataset_name)
logger.info("[%d/%d] Processing dataset: %s", idx, len(datasets), dataset_name)
# Check if already exists
if skip_existing and os.path.exists(lmdb_path):
logger.info(" → Skipping (directory already exists): %s", lmdb_path)
skip_count += 1
continue
try:
# Create the directory if needed
os.makedirs(lmdb_path, exist_ok=True)
# Dump this dataset
prepare_and_dump(dataset_name, sql_uri, lmdb_path)
success_count += 1
except Exception as e: # pylint: disable=broad-except
logger.error(" → Failed to dump %s: %s", dataset_name, e, exc_info=True)
fail_count += 1
# Summary
logger.info("=" * 70)
logger.info("Dump Summary:")
logger.info(" Success: %d", success_count)
logger.info(" Skipped: %d", skip_count)
logger.info(" Failed: %d", fail_count)
logger.info("=" * 70)
@cli.command("print-phenotype-matrix")
@click.argument(
"lmdb_path",
type=click.Path(exists=True, file_okay=False, readable=True, path_type=str),
)
def print_phenotype_matrix_cmd(lmdb_path: str) -> None:
"""Read and print the phenotype matrix stored in LMDB."""
pheno = load_from_lmdb(lmdb_path)
rows, cols = pheno.matrix.shape
logger.info("PhenoMatrix rows(traits)=%d cols(strains)=%d", rows, cols)
print(pheno.matrix)
@cli.command("list-traits")
@click.argument(
"lmdb_path",
type=click.Path(exists=True, file_okay=False, readable=True, path_type=str),
)
def list_traits_cmd(lmdb_path: str) -> None:
"""Print every trait name stored in the LMDB metadata, one per line."""
with open_lmdb(lmdb_path) as env:
with env.begin() as txn:
metadata = read_metadata(txn)
for trait in metadata["traits"]:
click.echo(trait)
@cli.command("fetch-trait")
@click.argument(
"lmdb_path",
type=click.Path(exists=True, file_okay=False, readable=True, path_type=str),
)
@click.argument("trait_name")
@click.option(
"--json/--no-json",
"as_json",
default=False,
help="Output as JSON instead of a plain table.",
)
def fetch_trait_cmd(lmdb_path: str, trait_name: str, as_json: bool) -> None:
"""Fetch and print values for a single trait across all strains.
Only the bytes for the requested row are decoded — the full matrix is
never reconstructed.
Example:
python lmdb_phenotypes_matrix.py fetch-trait /path/to/lmdb/dataset_name 1232_at
"""
result = fetch_single_trait(lmdb_path, trait_name)
if as_json:
click.echo(json.dumps(result, indent=2))
else:
# column-aligned table
max_strain_len = max(len(s) for s in result)
click.echo(f"{'Strain':<{max_strain_len}} Value")
click.echo(f"{'-' * max_strain_len} -----")
for strain, value in result.items():
click.echo(f"{strain:<{max_strain_len}} {value}")
@cli.command("sanity-check")
@click.argument("sql_uri")
@click.argument(
"lmdb_path",
type=click.Path(file_okay=False, path_type=str),
)
@click.argument("dataset", default="BXDPublish")
def sanity_check_cmd(sql_uri: str, lmdb_path: str, dataset: str) -> None:
"""Dump, reload, and compare — fails loudly on any mismatch.
Arguments:
sql_uri: MySQL connection URI
lmdb_path: Path to LMDB directory
dataset: Dataset name (default: BXDPublish)
"""
logger.info("Making sanity check for ===== %s dataset =====", logger)
dumped = prepare_and_dump(dataset, sql_uri, lmdb_path)
loaded = load_from_lmdb(lmdb_path)
verify_roundtrip(dumped, loaded)
if __name__ == "__main__":
cli()
|