diff options
Diffstat (limited to 'scripts/insert_data.py')
| -rw-r--r-- | scripts/insert_data.py | 368 |
1 files changed, 0 insertions, 368 deletions
diff --git a/scripts/insert_data.py b/scripts/insert_data.py deleted file mode 100644 index aec0251..0000000 --- a/scripts/insert_data.py +++ /dev/null @@ -1,368 +0,0 @@ -"""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()) |
