diff options
| -rw-r--r-- | uploader/genotypes/models.py | 61 |
1 files changed, 56 insertions, 5 deletions
diff --git a/uploader/genotypes/models.py b/uploader/genotypes/models.py index 0305a15..1e09cd3 100644 --- a/uploader/genotypes/models.py +++ b/uploader/genotypes/models.py @@ -1,5 +1,6 @@ """Functions for handling genotypes.""" from typing import Optional +from functools import reduce from datetime import datetime import MySQLdb as mdb @@ -32,13 +33,12 @@ def genotype_markers( population_id: int, offset: int = 0, limit: int = -1# no limit if negative, zero returns empty list. -) -> tuple[tuple[dict, ...], int, int]: +) -> tuple[tuple[dict, ...], int]: """Retrieve markers from the database. Return: A tuple of: - - Listing of the markers - - The total number of markers found in the system - - The number of markers that were actually fetched. + - Listing of the markers, + - The total number of markers found in the system. """ _query_template = ( "SELECT %%COLS%% " @@ -70,7 +70,58 @@ def genotype_markers( (species_id, population_id)) debug_query(cursor, app.logger) _records = tuple(dict(row) for row in cursor.fetchall()) - return _records, _total_records, len(_records) + return _records, _total_records + + +def genotype_records( + conn: mdb.Connection, + species_id: int, + population_id: int, + offset: int = 0, + limit: int = -1# no limit if negative, zero returns empty list. +) -> tuple[tuple[dict, ...], int]: + """Retrieve the actual genotype records from the database. + + Returns: A tuple of: + - the listing of the genotype data, + - the total number of genotype records for this population. + """ + def __organise_geno_records__(acc, row): + _current_row = acc.get(row["GenoId"], { + "GenoId": row["GenoId"], + "data": {} + }) + _current_row["data"][row["StrainName"]] = row["value"] + return { + **acc, + _current_row["GenoId"]: _current_row + } + + _query_template = ( + "SELECT gxr.GenoId, gxr.DataId, gdt.value, strn.Name AS StrainName " + "FROM GenoXRef AS gxr " + "INNER JOIN GenoData AS gdt ON gxr.DataId = gdt.Id " + "INNER JOIN Strain AS strn ON gdt.StrainId = strn.Id " + "WHERE gxr.GenoId IN (%%PARAMS_STR%%)") + + with conn.cursor(cursorclass=DictCursor) as cursor: + _markers, _num_records = genotype_markers( + conn, species_id, population_id, offset, limit) + _genoids = tuple(_marker["Id"] for _marker in _markers) + cursor.execute( + _query_template.replace( + "%%PARAMS_STR%%", ",".join(["%s"] * len(_genoids))), + _genoids) + _records: dict[str, dict] = reduce( + __organise_geno_records__, cursor.fetchall(), {}) + return ( + tuple({ + **_marker, + "data": _records.get( + _marker["Id"], {} + ).get("data", {}) + } for _marker in _markers), + _num_records) def genotype_dataset( |