diff options
| -rw-r--r-- | gn3/api/metadata_api/wiki.py | 25 | ||||
| -rw-r--r-- | gn3/db/wiki.py | 44 |
2 files changed, 65 insertions, 4 deletions
diff --git a/gn3/api/metadata_api/wiki.py b/gn3/api/metadata_api/wiki.py index 1e566cd..32872f3 100644 --- a/gn3/api/metadata_api/wiki.py +++ b/gn3/api/metadata_api/wiki.py @@ -85,3 +85,28 @@ def get_wiki_entries(symbol: str): payload.headers["Content-Type"] = "application/ld+json" return payload, status_code return jsonify(data), status_code + + +@wiki_blueprint.route("/<int:comment_id>", methods=["GET"]) +def get_wiki(comment_id: int): + with db_utils.database_connection(current_app.config["SQL_URI"]) as conn: + return jsonify(wiki.get_latest_comment(conn, comment_id)) + return jsonify(error="Error editting wiki entry, most likely due to DB error!"), 500 + + +@wiki_blueprint.route("/categories", methods=["GET"]) +def get_categories(): + with db_utils.database_connection(current_app.config["SQL_URI"]) as conn: + cursor = conn.cursor() + categories_dict = wiki.get_categories(cursor) + return jsonify(categories_dict) + return jsonify(error="Error editting wiki entry, most likely due to DB error!"), 500 + + +@wiki_blueprint.route("/species", methods=["GET"]) +def get_species(): + with db_utils.database_connection(current_app.config["SQL_URI"]) as conn: + cursor = conn.cursor() + species_dict = wiki.get_species(cursor) + return jsonify(species_dict) + return jsonify(error="Error editting wiki entry, most likely due to DB error!"), 500 diff --git a/gn3/db/wiki.py b/gn3/db/wiki.py index c3cfee0..f2984af 100644 --- a/gn3/db/wiki.py +++ b/gn3/db/wiki.py @@ -1,12 +1,38 @@ """Helper functions to access wiki entries""" -from typing import List +from typing import Dict, List + +from MySQLdb.cursors import DictCursor class MissingDBDataException(Exception): """Error due to DB missing some data""" +def get_latest_comment(connection, comment_id: str) -> int: + cursor = connection.cursor(DictCursor) + query = """ SELECT versionId AS version, symbol, PubMed_ID AS pubmed_ids, sp.Name AS species, + comment, email, weburl, initial, reason + FROM `GeneRIF` gr + INNER JOIN Species sp USING(SpeciesId) + WHERE gr.Id = %s + ORDER BY versionId DESC LIMIT 1; + """ + cursor.execute(query, (comment_id,)) + result = cursor.fetchone() + result["pubmed_ids"] = [x.strip() for x in result["pubmed_ids"].split()] + categories_query = """ + SELECT grx.GeneRIFId, grx.versionId, gc.Name FROM GeneRIFXRef grx + INNER JOIN GeneCategory gc ON grx.GeneCategoryId=gc.Id + WHERE GeneRIFId = %s AND versionId=%s; + """ + + cursor.execute(categories_query, (comment_id, result["version"])) + categories = cursor.fetchall() + result["categories"] = [x["Name"] for x in categories] + return result + + def get_species_id(cursor, species_name: str) -> int: """Find species id given species `Name`""" cursor.execute("SELECT SpeciesID from Species WHERE Name = %s", (species_name,)) @@ -31,9 +57,7 @@ def get_next_comment_version(cursor, comment_id: int) -> int: def get_categories_ids(cursor, categories: List[str]) -> List[int]: """Get the categories_ids from a list of category strings""" - cursor.execute("SELECT Name, Id from GeneCategory") - raw_categories = cursor.fetchall() - dict_cats = dict(raw_categories) + dict_cats = get_categories(cursor) category_ids = [] for category in set(categories): cat_id = dict_cats.get(category.strip()) @@ -41,3 +65,15 @@ def get_categories_ids(cursor, categories: List[str]) -> List[int]: raise MissingDBDataException(f"Category with Name={category} not found") category_ids.append(cat_id) return category_ids + +def get_categories(cursor) -> Dict[str, int]: + cursor.execute("SELECT Name, Id from GeneCategory") + raw_categories = cursor.fetchall() + dict_cats = dict(raw_categories) + return dict_cats + +def get_species(cursor) -> Dict[str, str]: + cursor.execute("SELECT Name, SpeciesName from Species") + raw_species = cursor.fetchall() + dict_cats = dict(raw_species) + return dict_cats |