about summary refs log tree commit diff
path: root/uploader/scripts/insert_data.py
diff options
context:
space:
mode:
Diffstat (limited to 'uploader/scripts/insert_data.py')
-rw-r--r--uploader/scripts/insert_data.py368
1 files changed, 368 insertions, 0 deletions
diff --git a/uploader/scripts/insert_data.py b/uploader/scripts/insert_data.py
new file mode 100644
index 0000000..aec0251
--- /dev/null
+++ b/uploader/scripts/insert_data.py
@@ -0,0 +1,368 @@
+"""Insert means/averages or standard-error data into the database."""
+import sys
+import string
+import random
+import logging
+import argparse
+from functools import reduce
+from typing import Tuple, Iterator
+
+import MySQLdb as mdb
+from redis import Redis
+from MySQLdb.cursors import DictCursor
+from gn_libs.mysqldb import database_connection
+
+from functional_tools import take
+
+from quality_control.file_utils import open_file
+from uploader.check_connections import check_db, check_redis
+
+# Set up logging
+stderr_handler = logging.StreamHandler(stream=sys.stderr)
+root_logger = logging.getLogger()
+root_logger.addHandler(stderr_handler)
+root_logger.setLevel("WARNING")
+
+def random_string(count: int = 10) -> str:
+    """Generate a random, alphanumeric string."""
+    return "".join(random.choices(
+        string.digits + string.ascii_letters, k=count))
+
+def translate_alias(heading):
+    "Translate strain aliases into canonical names"
+    translations = {"B6": "C57BL/6J", "D2": "DBA/2J"}
+    return translations.get(heading, heading)
+
+def read_file_headings(filepath) -> Tuple[str, ...]:
+    "Get the file headings"
+    with open_file(filepath) as input_file:
+        for line_contents in input_file:
+            headings = tuple(
+                translate_alias(heading.strip())
+                for heading in line_contents.split("\t"))
+            break
+
+    return headings
+
+def read_file_contents(filepath):
+    "Get the file contents"
+    with open_file(filepath) as input_file:
+        for line_number, line_contents in enumerate(input_file):
+            if line_number == 0:
+                continue
+            if line_number > 0:
+                yield tuple(
+                    field.strip() for field in line_contents.split("\t"))
+
+def strains_info(
+        dbconn: mdb.Connection, strain_names: Tuple[str, ...],
+        speciesid: int) -> dict:
+    "Retrieve information for the strains"
+    with dbconn.cursor(cursorclass=DictCursor) as cursor:
+        query = (
+            "SELECT * FROM Strain WHERE Name IN "
+            f"({', '.join(['%s']*len(strain_names))}) "
+            "AND SpeciesId = %s")
+        cursor.execute(query, tuple(strain_names) + (speciesid,))
+        return {strain["Name"]: strain for strain in cursor.fetchall()}
+
+def read_datavalues(filepath, headings, strain_info):
+    """Read numerical, data values from the file."""
+    id_key = headings[0]
+    return {
+        str(row[id_key]): tuple({
+            "ProbeSetName": str(row[id_key]),
+            "StrainId": strain_info[sname]["Id"],
+            "DataValue": float(row[sname])
+        } for sname in headings[1:])
+        for row in
+        (dict(zip(headings, line)) for line in read_file_contents(filepath))
+    }
+
+def read_probesets(filepath, headings):
+    """Read the ProbeSet names."""
+    id_key = headings[0]
+    for row in (dict(zip(headings, line))
+                for line in read_file_contents(filepath)):
+        yield {"Name": str(row[id_key])}
+
+def last_data_id(dbconn: mdb.Connection) -> int:
+    "Get the last id from the database"
+    with dbconn.cursor() as cursor:
+        cursor.execute("SELECT MAX(Id) FROM ProbeSetData")
+        return int(cursor.fetchone()[0])
+
+def check_strains(headings_strains, db_strains):
+    "Check strains in headings exist in database"
+    from_db = tuple(db_strains.keys())
+    not_in_db = tuple(
+        strain for strain in headings_strains if strain not in from_db)
+    if len(not_in_db) == 0:
+        return True
+
+    str_not_in_db = "', '".join(not_in_db)
+    print(
+        (f"ERROR: The strain(s) '{str_not_in_db}' w(as|ere) not found in the "
+         "database."),
+        file=sys.stderr)
+    sys.exit(1)
+
+
+def annotationinfo(
+        dbconn: mdb.Connection,
+        platformid: int,
+        datasetid: int
+) -> dict[str, dict]:
+    "Get annotation information from the database."
+    # This is somewhat slow. Look into optimising the behaviour
+    def __organise_annotations__(accm, item):
+        names_dict = (
+            {**accm[0], item["Name"]: item} if bool(item["Name"]) else accm[0])
+        targs_dict = (
+            {**accm[1], item["TargetId"]: item}
+            if bool(item["TargetId"]) else accm[1])
+        return (names_dict, targs_dict)
+
+    query = (
+        "SELECT ProbeSet.Name, ProbeSet.ChipId, ProbeSet.TargetId, "
+        "ProbeSetXRef.DataId, ProbeSetXRef.ProbeSetFreezeId "
+        "FROM ProbeSet INNER JOIN ProbeSetXRef "
+        "ON ProbeSet.Id=ProbeSetXRef.ProbeSetId "
+        "WHERE ProbeSet.ChipId=%s AND ProbeSetXRef.ProbeSetFreezeId=%s")
+    with dbconn.cursor(cursorclass=DictCursor) as cursor:
+        cursor.execute(query, (platformid, datasetid))
+        annot_dicts = reduce(# type: ignore[var-annotated]
+            __organise_annotations__, cursor.fetchall(), ({}, {}))
+        return {**annot_dicts[0], **annot_dicts[1]}
+    return {}
+
+def __format_query__(query, params):
+    "Format the query for output"
+    def __param_str__(param):
+        return "', '".join(str(elt) for elt in param)
+    idx = query.find("VALUES")
+    idx = query.find("%")
+    fields = tuple(
+        elt.replace("%(", "").replace(")s", "").replace(")", "").strip()
+        for elt in query[idx:-1].split(","))
+    values = (tuple(param[field] for field in fields) for param in params)
+    values_str = ", ".join(
+        f"('{__param_str__(value_tup)}')" for value_tup in values)
+    insert_str = query[:idx].replace(
+        "INSERT INTO ", "INSERT INTO\n\t")
+    return f"{insert_str}\nVALUES\n\t{values_str};"
+
+def insert_probesets(filepath: str,
+                     dbconn: mdb.Connection,
+                     platform_id: int,
+                     headings: tuple[str, ...],
+                     session_rand_str: str) -> tuple[str, ...]:
+    """Save new ProbeSets into the database."""
+    probeset_query = (
+        "INSERT INTO ProbeSet(ChipId, Name) "
+        "VALUES (%(ChipId)s, %(Name)s) ")
+    the_probesets = ({
+        **row,
+        "Name": f"{row['Name']}{session_rand_str}",
+        "ChipId": platform_id
+    } for row in read_probesets(filepath, headings))
+    probeset_names: tuple[str, ...] = tuple()
+    with dbconn.cursor(cursorclass=DictCursor) as cursor:
+        while True:
+            probeset_params = tuple(take(the_probesets, 10000))
+            if not bool(probeset_params):
+                break
+            print(__format_query__(probeset_query, probeset_params))
+            print()
+            cursor.executemany(probeset_query, probeset_params)
+            probeset_names = probeset_names + tuple(
+                row["Name"] for row in probeset_params)
+
+    return probeset_names
+
+def probeset_ids(dbconn: mdb.Connection,
+                 chip_id: int,
+                 probeset_names: tuple[str, ...]) -> Iterator[tuple[str, int]]:
+    """Fetch the IDs of the probesets with the given names."""
+    with dbconn.cursor() as cursor:
+        params_str = ", ".join(["%s"] * len(probeset_names))
+        cursor.execute(
+            "SELECT Name, Id FROM ProbeSet "
+            "WHERE ChipId=%s "
+            f"AND Name IN ({params_str})",
+            (chip_id,) + probeset_names)
+        while True:
+            row = cursor.fetchone()
+            if not bool(row):
+                break
+            yield row
+
+def insert_means(# pylint: disable=[too-many-locals, too-many-arguments, too-many-positional-arguments]
+        filepath: str, speciesid: int, platform_id: int, datasetid: int,
+        dbconn: mdb.Connection, rconn: Redis) -> int: # pylint: disable=[unused-argument]
+    "Insert the means/averages data into the database"
+    headings = read_file_headings(filepath)
+    strains = strains_info(dbconn, headings[1:], speciesid)
+    check_strains(headings[1:], strains)
+    means_query = (
+        "INSERT INTO ProbeSetData "
+        "VALUES(%(ProbeSetDataId)s, %(StrainId)s, %(DataValue)s)")
+    xref_query = (
+        "INSERT INTO ProbeSetXRef(ProbeSetFreezeId, ProbeSetId, DataId) "
+        "VALUES(%(ProbeSetFreezeId)s, %(ProbeSetId)s, %(ProbeSetDataId)s)")
+
+    # A random string to avoid over-write chances.
+    #   This is needed because the `ProbeSet` table is defined with
+    #   UNIQUE KEY `ProbeSetId` (`ChipId`,`Name`)
+    #   which means that we cannot have 2 (or more) ProbeSets which share both
+    #   the name and chip_id (platform) at the same time.
+    rand_str = f"::RAND_{random_string()}"
+    pset_ids = {
+        name[0:name.index("::RAND_")]: pset_id
+        for name, pset_id in probeset_ids(
+                dbconn,
+                platform_id,
+                insert_probesets(
+                    filepath, dbconn, platform_id, headings, rand_str))
+    }
+    the_means = ({
+        **mean, "ProbeSetFreezeId": datasetid, "ProbeSetDataId": data_id,
+        "ChipId": platform_id, "ProbeSetId": pset_ids[mean["ProbeSetName"]]
+    } for data_id, mean in enumerate((
+        item for sublist in
+        read_datavalues(filepath, headings, strains).values()
+        for item in sublist),
+                                     start=last_data_id(dbconn)+1))
+    with dbconn.cursor(cursorclass=DictCursor) as cursor:
+        while True:
+            means = tuple(take(the_means, 10000))
+            if not bool(means):
+                break
+            print(__format_query__(means_query, means))
+            print()
+            print(__format_query__(xref_query, means))
+            cursor.executemany(means_query, means)
+            cursor.executemany(xref_query, means)
+    return 0
+
+def insert_se(# pylint: disable = [too-many-arguments,too-many-locals, too-many-positional-arguments]
+        filepath: str, speciesid: int, platformid: int, datasetid: int,
+        dbconn: mdb.Connection, rconn: Redis) -> int: # pylint: disable=[unused-argument]
+    "Insert the standard-error data into the database"
+    headings = read_file_headings(filepath)
+    strains = strains_info(dbconn, headings[1:], speciesid)
+    check_strains(headings[1:], strains)
+    se_query = (
+        "INSERT INTO ProbeSetSE "
+        "VALUES(%(DataId)s, %(StrainId)s, %(DataValue)s)")
+    annotations = annotationinfo(dbconn, platformid, datasetid)
+    if not bool(annotations):
+        with dbconn.cursor(cursorclass=DictCursor) as cursor:
+            cursor.execute(
+                ("SELECT "
+                 "gc.GeneChipName AS platformname, pf.Name AS studyname, "
+                 "psf.FullName AS datasetname "
+                 "FROM GeneChip AS gc INNER JOIN ProbeFreeze AS pf "
+                 "ON gc.Id=pf.ChipId INNER JOIN ProbeSetFreeze AS psf "
+                 "ON pf.Id=psf.ProbeFreezeId "
+                 "WHERE gc.Id=%s AND psf.Id=%s"),
+                (platformid, datasetid))
+
+            errorinfo = cursor.fetchone()
+            print(("ERROR: No annotations found for the "
+                   f"'{errorinfo['datasetname']}' dataset (Id: {datasetid}) "
+                   f"under the '{errorinfo['studyname']}' study linked to the "
+                   f"'{errorinfo['platformname']}' platform (Id: {platformid})."
+                   "\n\n"
+                   "       Please verify you selected the correct platform, "
+                   "study and dataset for the standard-error file(s) you were "
+                   "trying to upload."),
+                  file=sys.stderr)
+        return 1
+
+    namemappings = {
+        _key[0:_key.find("::RAND_")]: _key for _key in annotations.keys()
+    }
+    se_values = (
+        {"DataId": annotations[namemappings[str(item["ProbeSetName"])]]["DataId"], **item}
+        for item in (
+                row for psrows in
+                read_datavalues(filepath, headings, strains).values()
+                for row in psrows))
+    with dbconn.cursor(cursorclass=DictCursor) as cursor:
+        try:
+            while True:
+                serrors = tuple(take(se_values, 1000))
+                if not bool(serrors):
+                    break
+                print(__format_query__(se_query, serrors))
+                cursor.executemany(se_query, serrors)
+        except KeyError as kerr:
+            print(
+                (
+                    f"The following name(s) or identifier(s) do(es) not "
+                    "exist in the database and did not exist in the original "
+                    f"values file: {', '.join(kerr.args)}"),
+                  file=sys.stderr)
+            return 1
+    return 0
+
+if __name__ == "__main__":
+    def cli_args():
+        "Compute the CLI arguments"
+        parser = argparse.ArgumentParser(
+            prog="InsertData", description=(
+                "Script to insert data from an 'averages' file into the "
+                "database."))
+        parser.add_argument(
+            "filetype", help="type of data to insert.",
+            choices=("average", "standard-error"))
+        parser.add_argument(
+            "filepath", help="path to the file with the 'averages' data.")
+        parser.add_argument(
+            "speciesid", help="Identifier for the species in the database.",
+            type=int)
+        parser.add_argument(
+            "platformid", help="Identifier for the platform in the database.",
+            type=int)
+        parser.add_argument(
+            "datasetid", help="Identifier for the dataset in the database.",
+            type=int)
+        parser.add_argument(
+            "database_uri",
+            help="URL to be used to initialise the connection to the database")
+        parser.add_argument(
+            "redisuri",
+            help="URL to initialise connection to redis",
+            default="redis:///")
+
+        args = parser.parse_args()
+        check_db(args.database_uri)
+        check_redis(args.redisuri)
+        return args
+
+    insert_fns = {
+        "average": insert_means,
+        "standard-error": insert_se
+    }
+
+    extract_args = {
+        "average": lambda args, dbconn, rconn: (
+            args.filepath, args.speciesid, args.platformid, args.datasetid, dbconn,
+            rconn),
+        "standard-error": lambda args, dbconn, rconn: (
+            args.filepath, args.speciesid, args.platformid, args.datasetid,
+            dbconn, rconn),
+    }
+
+    def main():
+        "Main entry point"
+        args = cli_args()
+        with Redis.from_url(args.redisuri, decode_responses=True) as rconn:
+            with database_connection(args.database_uri) as dbconn:
+                return insert_fns[args.filetype](
+                    *extract_args[args.filetype](args, dbconn, rconn))
+
+        return 2
+
+    sys.exit(main())