diff options
| -rw-r--r-- | uploader/genotypes/models.py | 41 | ||||
| -rw-r--r-- | uploader/genotypes/views.py | 39 | ||||
| -rw-r--r-- | uploader/templates/genotypes/index.html | 8 | ||||
| -rw-r--r-- | uploader/templates/genotypes/list-genotypes.html | 142 |
4 files changed, 220 insertions, 10 deletions
diff --git a/uploader/genotypes/models.py b/uploader/genotypes/models.py new file mode 100644 index 0000000..53c5fb8 --- /dev/null +++ b/uploader/genotypes/models.py @@ -0,0 +1,41 @@ +"""Functions for handling genotypes.""" +from typing import Optional + +import MySQLdb as mdb +from MySQLdb.cursors import DictCursor + +from uploader.db_utils import debug_query + +def genocode_by_population( + conn: mdb.Connection, population_id: int) -> tuple[dict, ...]: + """Get the allele/genotype codes.""" + with conn.cursor(cursorclass=DictCursor) as cursor: + cursor.execute("SELECT * FROM GenoCode WHERE InbredSetId=%s", + (population_id,)) + return tuple(dict(item) for item in cursor.fetchall()) + + +def genotype_markers_count(conn: mdb.Connection, species_id: int) -> int: + """Find the total count of the genotype markers for a species.""" + with conn.cursor(cursorclass=DictCursor) as cursor: + cursor.execute( + "SELECT COUNT(Name) AS markers_count FROM Geno WHERE SpeciesId=%s", + (species_id,)) + return int(cursor.fetchone()["markers_count"]) + + +def genotype_markers( + conn: mdb.Connection, + species_id: int, + offset: int = 0, + limit: Optional[int] = None +) -> tuple[dict, ...]: + """Retrieve markers from the database.""" + _query = "SELECT * FROM Geno WHERE SpeciesId=%s" + if bool(limit) and limit > 0: + _query = _query + f" LIMIT {limit} OFFSET {offset}" + + with conn.cursor(cursorclass=DictCursor) as cursor: + cursor.execute(_query, (species_id,)) + debug_query(cursor) + return tuple(dict(row) for row in cursor.fetchall()) diff --git a/uploader/genotypes/views.py b/uploader/genotypes/views.py index 885e008..0618949 100644 --- a/uploader/genotypes/views.py +++ b/uploader/genotypes/views.py @@ -7,13 +7,17 @@ from flask import (flash, render_template, current_app as app) -from uploader.datautils import order_by_family from uploader.authorisation import require_login from uploader.db_utils import database_connection from uploader.species.models import all_species, species_by_id +from uploader.datautils import safe_int, order_by_family, enumerate_sequence from uploader.population.models import (populations_by_species, population_by_species_and_id) +from .models import (genotype_markers, + genotype_markers_count, + genocode_by_population) + genotypesbp = Blueprint("genotypes", __name__) @genotypesbp.route("populations/genotypes", methods=["GET"]) @@ -72,4 +76,35 @@ def select_population(species_id: int): @require_login def list_genotypes(species_id: int, population_id: int): """List genotype details for species and population.""" - return f"Would list geno info for population {population_id} from species {species_id}" + with database_connection(app.config["SQL_URI"]) as conn: + species = species_by_id(conn, species_id) + if not bool(species): + flash("Invalid species provided!", "alert-danger") + return redirect(url_for("species.populations.genotypes.index")) + + population = population_by_species_and_id( + conn, species_id, population_id) + if not bool(population): + flash("Invalid population selected!", "alert-danger") + return redirect(url_for( + "species.populations.genotypes.select_population", + species_id=species_id)) + + start_from = safe_int(request.args.get("start_from") or 0) + if start_from < 0: + start_from = 0 + count = safe_int(request.args.get("count") or 20) + markers = enumerate_sequence( + genotype_markers(conn, species_id, offset=start_from, limit=count), + start=start_from+1) + return render_template("genotypes/list-genotypes.html", + species=species, + population=population, + genocode=genocode_by_population( + conn, population_id), + total_markers=genotype_markers_count( + conn, species_id), + start_from=start_from, + count=count, + markers=markers, + activelink="list-genotypes") diff --git a/uploader/templates/genotypes/index.html b/uploader/templates/genotypes/index.html index 9ffea73..e749f5a 100644 --- a/uploader/templates/genotypes/index.html +++ b/uploader/templates/genotypes/index.html @@ -25,12 +25,4 @@ {{select_species_form(url_for("species.populations.genotypes.index"), species)}} </div> - -<div class="row"> - <h3>Some Important Concepts to Consider/Remember</h3> - <ul> - <li>Reference vs. Non-reference alleles</li> - <li>In <em>GenoCode</em> table, items are ordered by <strong>InbredSet</strong></li> - </ul> -</div> {%endblock%} diff --git a/uploader/templates/genotypes/list-genotypes.html b/uploader/templates/genotypes/list-genotypes.html new file mode 100644 index 0000000..9b27540 --- /dev/null +++ b/uploader/templates/genotypes/list-genotypes.html @@ -0,0 +1,142 @@ +{%extends "genotypes/base.html"%} +{%from "flash_messages.html" import flash_all_messages%} +{%from "populations/macro-display-population-card.html" import display_population_card%} + +{%block title%}Genotypes{%endblock%} + +{%block pagetitle%}Genotypes{%endblock%} + +{%block lvl4_breadcrumbs%} +<li {%if activelink=="list-genotypes"%} + class="breadcrumb-item active" + {%else%} + class="breadcrumb-item" + {%endif%}> + <a href="{{url_for('species.populations.genotypes.list_genotypes', + species_id=species.SpeciesId, + population_id=population.Id)}}">List genotypes</a> +</li> +{%endblock%} + +{%block contents%} +{{flash_all_messages()}} + +<div class="row"> + <p> + The genotype encoding used for the "{{population.FullName}}" population from + the "{{species.FullName}}" species is as shown in the table below. + </p> + <table class="table"> + <legend>Genotype Encoding</legend> + + <thead> + <tr> + <th>Allele Type</th> + <th>Allele Symbol</th> + <th>Allele Value</th> + </tr> + </thead> + + <tbody> + {%for row in genocode%} + <tr> + <td>{{row.AlleleType}}</td> + <td>{{row.AlleleSymbol}}</td> + <td>{{row.DatabaseValue if row.DatabaseValue is not none else "NULL"}}</td> + </tr> + {%else%} + <tr> + <td colspan="7" class="text-info"> + <span class="glyphicon glyphicon-exclamation-sign"></span> + There is no explicit genotype encoding defined for this population. + </td> + </tr> + {%endfor%} + </tbody> + </table> +</div> + +<div class="row text-danger"> + <h3>Some Important Concepts to Consider/Remember</h3> + <ul> + <li>Reference vs. Non-reference alleles</li> + <li>In <em>GenoCode</em> table, items are ordered by <strong>InbredSet</strong></li> + </ul> + <h3>Possible references</h3> + <ul> + <li>https://mr-dictionary.mrcieu.ac.uk/term/genotype/</li> + </ul> +</div> + +<div class="row"> + <p> + There are a total of {{total_markers}} genotype markers for this species. + </p> + <div class="row"> + <div class="col-md-2" style="text-align: start;"> + {%if start_from > 0%} + <a href="{{url_for('species.populations.genotypes.list_genotypes', + species_id=species.SpeciesId, + population_id=population.Id, + start_from=start_from-count, + count=count)}}"> + <span class="glyphicon glyphicon-backward"></span> + Previous + </a> + {%endif%} + </div> + <div class="col-md-8" style="text-align: center;"> + Displaying markers {{start_from+1}} to {{start_from + count}} of + {{total_markers}} + </div> + <div class="col-md-2" style="text-align: end;"> + {%if start_from + count < total_markers%} + <a href="{{url_for('species.populations.genotypes.list_genotypes', + species_id=species.SpeciesId, + population_id=population.Id, + start_from=start_from+count, + count=count)}}"> + Next + <span class="glyphicon glyphicon-forward"></span> + </a> + {%endif%} + </div> + </div> + <table class="table"> + <thead> + <tr> + <th title="">#</th> + <th title="">Marker Name</th> + <th title="Chromosome">Chr</th> + <th title="Physical location of the marker in megabasepairs"> + Location (Mb)</th> + <th title="">Source</th> + <th title="">Source2</th> + </thead> + + <tbody> + {%for marker in markers%} + <tr> + <td>{{marker.sequence_number}}</td> + <td>{{marker.Marker_Name}}</td> + <td>{{marker.Chr}}</td> + <td>{{marker.Mb}}</td> + <td>{{marker.Source}}</td> + <td>{{marker.Source2}}</td> + </tr> + {%else%} + <tr> + <td colspan="7" class="text-info"> + <span class="glyphicon glyphicon-exclamation-sign"></span> + No markers to display! + </td> + </tr> + {%endfor%} + </tbody> + </table> +</div> +{%endblock%} + +{%block sidebarcontents%} +{{display_population_card(species, population)}} +{%endblock%} |