diff options
| -rw-r--r-- | uploader/genotypes/models.py | 36 |
1 files changed, 27 insertions, 9 deletions
diff --git a/uploader/genotypes/models.py b/uploader/genotypes/models.py index 34d2cfe..0305a15 100644 --- a/uploader/genotypes/models.py +++ b/uploader/genotypes/models.py @@ -29,30 +29,48 @@ def genotype_markers_count(conn: mdb.Connection, species_id: int) -> int: def genotype_markers( conn: mdb.Connection, species_id: int, + population_id: int, offset: int = 0, - limit: Optional[int] = None -) -> tuple[tuple[dict, ...], int]: - """Retrieve markers from the database.""" + limit: int = -1# no limit if negative, zero returns empty list. +) -> tuple[tuple[dict, ...], int, 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. + """ _query_template = ( - "SELECT %%COLS%% FROM Geno AS gno " - "WHERE gno.SpeciesId=%s " + "SELECT %%COLS%% " + "FROM Species AS spc " + "INNER JOIN InbredSet AS iset " + "ON spc.Id = iset.SpeciesId " + "INNER JOIN GenoFreeze AS gfr " + "ON iset.Id = gfr.InbredSetId " + "INNER JOIN GenoXRef AS gxr " + "ON gfr.Id = gxr.GenoFreezeId " + "INNER JOIN Geno AS gno " + "ON gxr.GenoId = gno.Id " + "WHERE spc.Id=%s " + "AND iset.Id=%s " "%%LIMIT%%") with conn.cursor(cursorclass=DictCursor) as cursor: cursor.execute( _query_template.replace("%%LIMIT%%", "").replace( "%%COLS%%", "COUNT(gno.Id) AS total_records"), - (species_id,)) + (species_id, population_id)) _total_records = cursor.fetchone()["total_records"] cursor.execute( _query_template.replace("%%COLS%%", "gno.*").replace( "%%LIMIT%%", (f"LIMIT {int(limit)} OFFSET {int(offset)}" - if bool(limit) and limit > 0 + if bool(limit) and limit >= 0 else "")), - (species_id,)) + (species_id, population_id)) debug_query(cursor, app.logger) - return tuple(dict(row) for row in cursor.fetchall()), _total_records + _records = tuple(dict(row) for row in cursor.fetchall()) + return _records, _total_records, len(_records) def genotype_dataset( |