diff options
Diffstat (limited to 'uploader/publications')
| -rw-r--r-- | uploader/publications/__init__.py | 1 | ||||
| -rw-r--r-- | uploader/publications/datatables.py | 52 | ||||
| -rw-r--r-- | uploader/publications/misc.py | 19 | ||||
| -rw-r--r-- | uploader/publications/models.py | 106 | ||||
| -rw-r--r-- | uploader/publications/pubmed.py | 15 | ||||
| -rw-r--r-- | uploader/publications/views.py | 185 |
6 files changed, 370 insertions, 8 deletions
diff --git a/uploader/publications/__init__.py b/uploader/publications/__init__.py index 57c0cbb..7efcabb 100644 --- a/uploader/publications/__init__.py +++ b/uploader/publications/__init__.py @@ -1 +1,2 @@ """Package for handling publications.""" +from .views import pubbp diff --git a/uploader/publications/datatables.py b/uploader/publications/datatables.py new file mode 100644 index 0000000..e07fafd --- /dev/null +++ b/uploader/publications/datatables.py @@ -0,0 +1,52 @@ +"""Fetch data for datatables.""" +import logging +from typing import Optional + +from MySQLdb.cursors import DictCursor + +from gn_libs.mysqldb import Connection, debug_query + +logger = logging.getLogger(__name__) + +def fetch_publications( + conn: Connection, + search: Optional[str] = None, + offset: int = 0, + limit: int = -1 +) -> tuple[dict, int, int, int]: + """Fetch publications from the database.""" + _query = "SELECT * FROM Publication" + _count_query = "SELECT COUNT(*) FROM Publication" + _params = None + _where_clause = "" + _limit_clause = "" + if search is not None and bool(search): + _where_clause = ("WHERE PubMed_ID LIKE %s " + "OR Authors LIKE %s " + "OR Title LIKE %s") + _params = (f"%{search}%",) * 3 + + if limit > 0: + _limit_clause = f"LIMIT {limit} OFFSET {offset}" + + with conn.cursor(cursorclass=DictCursor) as cursor: + cursor.execute("SELECT COUNT(*) FROM Publication") + _total_rows = int(cursor.fetchone()["COUNT(*)"]) + + cursor.execute(f"{_count_query} {_where_clause}", _params) + debug_query(cursor, logger) + _result = cursor.fetchone() + _total_filtered = int(_result["COUNT(*)"] if bool(_result) else 0) + + cursor.execute(f"{_query} {_where_clause} {_limit_clause}", _params) + debug_query(cursor, logger) + _current_filtered = tuple( + {**dict(row), "index": idx} + for idx, row + in enumerate(cursor.fetchall(), start=offset+1)) + + return ( + _current_filtered, + len(_current_filtered), + _total_filtered, + _total_rows) diff --git a/uploader/publications/misc.py b/uploader/publications/misc.py index d93ecdd..fca6f71 100644 --- a/uploader/publications/misc.py +++ b/uploader/publications/misc.py @@ -4,7 +4,22 @@ def publications_differences( filedata: tuple[dict, ...], dbdata: tuple[dict, ...], - pubmedid2pubidmap: dict[str, int] + pubmedid2pubidmap: tuple[dict, ...] ) -> tuple[dict, ...]: """Compute the differences between file data and db data""" - return tuple() + diff = tuple() + for filerow, dbrow in zip( + sorted(filedata, key=lambda item: ( + item["phenotype_id"], item["xref_id"])), + sorted(dbdata, key=lambda item: ( + item["PhenotypeId"], item["xref_id"]))): + if filerow["PubMed_ID"] == dbrow["PubMed_ID"]: + continue + + newpubmed = filerow["PubMed_ID"] + diff = diff + ({ + **dbrow, + "PubMed_ID": newpubmed, + "PublicationId": pubmedid2pubidmap.get(newpubmed)},) + + return diff diff --git a/uploader/publications/models.py b/uploader/publications/models.py index 89da06c..dcfa02b 100644 --- a/uploader/publications/models.py +++ b/uploader/publications/models.py @@ -1,7 +1,18 @@ """Module to handle persistence and retrieval of publication to/from MariaDB""" +import logging +from typing import Iterable + +from MySQLdb.cursors import DictCursor + +from gn_libs.mysqldb import Connection, debug_query + +logger = logging.getLogger(__name__) + def fetch_phenotype_publications( - conn, ids: tuple[tuple[int, int], ...]) -> tuple[dict, ...]: + conn: Connection, + ids: tuple[tuple[int, int], ...] +) -> tuple[dict, ...]: """Fetch publication from database by ID.""" paramstr = ",".join(["(%s, %s)"] * len(ids)) query = ( @@ -13,3 +24,96 @@ def fetch_phenotype_publications( with conn.cursor(cursorclass=DictCursor) as cursor: cursor.execute(query, tuple(item for row in ids for item in row)) return tuple(dict(row) for row in cursor.fetchall()) + + +def create_new_publications( + conn: Connection, + publications: tuple[dict, ...] +) -> tuple[dict, ...]: + """Create new publications in the database.""" + if len(publications) > 0: + with conn.cursor(cursorclass=DictCursor) as cursor: + cursor.executemany( + ("INSERT INTO " + "Publication( " + "PubMed_ID, Abstract, Authors, Title, Journal, Volume, Pages, " + "Month, Year" + ") " + "VALUES(" + "%(pubmed_id)s, %(abstract)s, %(authors)s, %(title)s, " + "%(journal)s, %(volume)s, %(pages)s, %(month)s, %(year)s" + ") " + "RETURNING *"), + publications) + return tuple({ + **row, "publication_id": row["Id"] + } for row in cursor.fetchall()) + + return tuple() + + +def update_publications(conn: Connection , publications: tuple[dict, ...]) -> tuple[dict, ...]: + """Update details for multiple publications""" + if len(publications) > 0: + with conn.cursor(cursorclass=DictCursor) as cursor: + logger.debug("UPDATING PUBLICATIONS: %s", publications) + cursor.executemany( + ("UPDATE Publication SET " + "PubMed_ID=%(pubmed_id)s, Abstract=%(abstract)s, " + "Authors=%(authors)s, Title=%(title)s, Journal=%(journal)s, " + "Volume=%(volume)s, Pages=%(pages)s, Month=%(month)s, " + "Year=%(year)s " + "WHERE Id=%(publication_id)s"), + publications) + debug_query(cursor, logger) + return publications + return tuple() + return tuple() + + +def delete_publications(conn: Connection , publications: tuple[dict, ...]): + """Delete multiple publications""" + publications = tuple(pub for pub in publications if bool(pub)) + if len(publications) > 0: + _pub_ids = tuple(pub["Id"] for pub in publications) + _paramstr = ", ".join(["%s"] * len(_pub_ids)) + _phenos_query = ( + "SELECT PublicationId, COUNT(PhenotypeId) FROM PublishXRef " + f"WHERE PublicationId IN ({_paramstr}) GROUP BY PublicationId;") + + with conn.cursor(cursorclass=DictCursor) as cursor: + cursor.execute(_phenos_query, _pub_ids) + _linked_phenos = cursor.fetchall() + if len(_linked_phenos) > 0: + raise Exception(# pylint: disable=[broad-exception-raised] + "Cannot delete publications with linked phenotypes.") + + cursor.execute( + f"DELETE FROM Publication WHERE Id IN ({_paramstr})", _pub_ids) + + +def fetch_publication_by_id(conn: Connection, publication_id: int) -> dict: + """Fetch a specific publication from the database.""" + with conn.cursor(cursorclass=DictCursor) as cursor: + cursor.execute("SELECT * FROM Publication WHERE Id=%s", + (publication_id,)) + _res = cursor.fetchone() + return dict(_res) if _res else {} + + +def fetch_publication_phenotypes( + conn: Connection, publication_id: int) -> Iterable[dict]: + """Fetch all phenotypes linked to this publication.""" + with conn.cursor(cursorclass=DictCursor) as cursor: + cursor.execute( + "SELECT pxr.Id AS xref_id, pxr.PublicationId, phe.* " + "FROM PublishXRef AS pxr INNER JOIN Phenotype AS phe " + "ON pxr.PhenotypeId=phe.Id " + "WHERE pxr.PublicationId=%s", + (publication_id,)) + while True: + row = cursor.fetchone() + if row: + yield row + else: + break diff --git a/uploader/publications/pubmed.py b/uploader/publications/pubmed.py index d984d99..2531c4a 100644 --- a/uploader/publications/pubmed.py +++ b/uploader/publications/pubmed.py @@ -1,4 +1,10 @@ """Module to interact with NCBI's PubMed""" +import logging + +import requests +from lxml import etree + +logger = logging.getLogger(__name__) def __pub_date__(pubdate: etree.Element): @@ -23,9 +29,7 @@ def __journal__(journal: etree.Element) -> dict: } def __author__(author: etree.Element) -> str: - return "%s %s" % ( - author.find("LastName").text, - author.find("Initials").text) + return f'{author.find("LastName").text} {author.find("Initials").text}' def __pages__(pagination: etree.Element) -> str: @@ -44,7 +48,7 @@ def __abstract__(article: etree.Element) -> str: def __article__(pubmed_article: etree.Element) -> dict: article = pubmed_article.find("MedlineCitation/Article") return { - "pubmed_id": pubmed_article.find("MedlineCitation/PMID").text, + "pubmed_id": int(pubmed_article.find("MedlineCitation/PMID").text), "title": article.find("ArticleTitle").text, **__journal__(article.find("Journal")), "abstract": __abstract__(article), @@ -82,7 +86,8 @@ def fetch_publications(pubmed_ids: tuple[int, ...]) -> tuple[dict, ...]: "db": "pubmed", "retmode": "xml", "id": ",".join(str(item) for item in pubmed_ids) - }) + }, + timeout=(9.13, 20)) if response.status_code == 200: return __process_pubmed_publication_data__(response.text) diff --git a/uploader/publications/views.py b/uploader/publications/views.py new file mode 100644 index 0000000..805d6f0 --- /dev/null +++ b/uploader/publications/views.py @@ -0,0 +1,185 @@ +"""Endpoints for publications""" +import json + +from gn_libs.mysqldb import database_connection +from flask import ( + flash, + request, + url_for, + redirect, + Blueprint, + render_template, + current_app as app) + +from uploader.authorisation import require_login +from uploader.route_utils import redirect_to_next + +from .models import ( + delete_publications, + update_publications, + fetch_publication_by_id, + create_new_publications, + fetch_publication_phenotypes) + +from .datatables import fetch_publications + +pubbp = Blueprint("publications", __name__) + + +@pubbp.route("/", methods=["GET"]) +@require_login +def index(): + """Index page for publications.""" + return render_template("publications/index.html") + + +@pubbp.route("/list", methods=["GET"]) +@require_login +def list_publications(): + """Fetch publications that fulfill a specific search, or all of them, if + there is no search term.""" + # request breakdown: + # https://datatables.net/manual/server-side + _page = int(request.args.get("draw")) + _length = int(request.args.get("length") or '-1') + _start = int(request.args.get("start") or '0') + _search = request.args["search[value]"] + with database_connection(app.config["SQL_URI"]) as conn: + _publications, _current_rows, _totalfiltered, _totalrows = fetch_publications( + conn, + _search, + offset=_start, + limit=_length) + + return json.dumps({ + "draw": _page, + "recordsTotal": _totalrows, + "recordsFiltered": _totalfiltered, + "publications": _publications, + "status": "success" + }) + + +@pubbp.route("/view/<int:publication_id>", methods=["GET"]) +@require_login +def view_publication(publication_id: int): + """View more details on a particular publication.""" + with database_connection(app.config["SQL_URI"]) as conn: + publication = fetch_publication_by_id(conn, publication_id) + + if not bool(publication): + flash("Requested publication was not found!", "alert-warning") + return redirect(url_for('publications.index')) + + return render_template( + "publications/view-publication.html", + publication=publication, + linked_phenotypes=tuple(fetch_publication_phenotypes( + conn, publication_id))) + + +@pubbp.route("/create", methods=["GET", "POST"]) +@require_login +def create_publication(): + """Create a new publication.""" + if request.method == "GET": + return render_template("publications/create-publication.html") + form = request.form + authors = form.get("publication-authors").encode("utf8") + if authors is None or authors == "": + flash("The publication's author(s) MUST be provided!", "alert alert-danger") + return redirect(url_for("publications.create", **request.args)) + + with database_connection(app.config["SQL_URI"]) as conn: + publications = create_new_publications(conn, ({ + "pubmed_id": form.get("pubmed-id") or None, + "abstract": form.get("publication-abstract").encode("utf8") or None, + "authors": authors, + "title": form.get("publication-title").encode("utf8") or None, + "journal": form.get("publication-journal").encode("utf8") or None, + "volume": form.get("publication-volume").encode("utf8") or None, + "pages": form.get("publication-pages").encode("utf8") or None, + "month": (form.get("publication-month") or "").encode("utf8").capitalize() or None, + "year": form.get("publication-year").encode("utf8") or None + },)) + flash("New publication created!", "alert alert-success") + return redirect(url_for( + request.args.get("return_to") or "publications.view_publication", + publication_id=publications[0]["publication_id"], + **request.args)) + + flash("Publication creation failed!", "alert alert-danger") + app.logger.debug("Failed to create the new publication.", exc_info=True) + return redirect(url_for("publications.create_publication")) + + +@pubbp.route("/edit/<int:publication_id>", methods=["GET", "POST"]) +@require_login +def edit_publication(publication_id: int): + """Edit a publication's details.""" + with database_connection(app.config["SQL_URI"]) as conn: + if request.method == "GET": + return render_template( + "publications/edit-publication.html", + publication=fetch_publication_by_id(conn, publication_id), + linked_phenotypes=tuple(fetch_publication_phenotypes( + conn, publication_id)), + publication_id=publication_id) + + form = request.form + _pub = update_publications(conn, ({ + "publication_id": publication_id, + "pubmed_id": form.get("pubmed-id") or None, + "abstract": form.get("publication-abstract").encode("utf8") or None, + "authors": form.get("publication-authors").encode("utf8"), + "title": form.get("publication-title").encode("utf8") or None, + "journal": form.get("publication-journal").encode("utf8") or None, + "volume": form.get("publication-volume").encode("utf8") or None, + "pages": form.get("publication-pages").encode("utf8") or None, + "month": (form.get("publication-month") or "").encode("utf8").capitalize() or None, + "year": form.get("publication-year").encode("utf8") or None + },)) + + if not _pub: + flash("There was an error updating the publication details.", + "alert-danger") + return redirect(url_for( + "publications.edit_publication", publication_id=publication_id)) + + flash("Successfully updated the publication details.", + "alert-success") + return redirect_to_next({ + "uri": "publications.view_publication", + "publication_id": publication_id + }) + + +@pubbp.route("/delete/<int:publication_id>", methods=["GET", "POST"]) +@require_login +def delete_publication(publication_id: int): + """Delete a particular publication.""" + with database_connection(app.config["SQL_URI"]) as conn: + publication = fetch_publication_by_id(conn, publication_id) + linked_phenotypes=tuple(fetch_publication_phenotypes( + conn, publication_id)) + + if not bool(publication): + flash("Requested publication was not found!", "alert-warning") + return redirect(url_for('publications.index')) + + if len(linked_phenotypes) > 0: + flash("Cannot delete publication with linked phenotypes!", + "alert-warning") + return redirect(url_for( + "publications.view_publication", publication_id=publication_id)) + + if request.method == "GET": + return render_template( + "publications/delete-publication.html", + publication=publication, + linked_phenotypes=linked_phenotypes, + publication_id=publication_id) + + delete_publications(conn, (publication,)) + flash("Deleted the publication successfully.", "alert-success") + return render_template("publications/delete-publication-success.html") |