diff options
Diffstat (limited to 'gn3/db/traits.py')
| -rw-r--r-- | gn3/db/traits.py | 329 |
1 files changed, 233 insertions, 96 deletions
diff --git a/gn3/db/traits.py b/gn3/db/traits.py index ae1939a..85cccfa 100644 --- a/gn3/db/traits.py +++ b/gn3/db/traits.py @@ -1,5 +1,7 @@ """This class contains functions relating to trait data manipulation""" from typing import Any, Dict, Union +from gn3.function_helpers import compose +from gn3.db.datasets import retrieve_trait_dataset def get_trait_csv_sample_data(conn: Any, @@ -76,159 +78,294 @@ def update_sample_data(conn: Any, return (updated_strains, updated_published_data, updated_se_data, updated_n_strains) - -def retrieve_trait_dataset_name( - trait_type: str, threshold: int, name: str, connection: Any): - """ - Retrieve the name of a trait given the trait's name - - This is extracted from the `webqtlDataset.retrieveName` function as is - implemented at - https://github.com/genenetwork/genenetwork1/blob/master/web/webqtl/base/webqtlDataset.py#L140-L169 - """ - columns = "Id, Name, FullName, ShortName{}".format( - ", DataScale" if trait_type == "ProbeSet" else "") - query = ( - "SELECT {columns} " - "FROM {trait_type}Freeze " - "WHERE " - "public > %(threshold)s " - "AND " - "(Name = %(name)s OR FullName = %(name)s OR ShortName = %(name)s)").format( - columns=columns, trait_type=trait_type) - with connection.cursor() as cursor: - cursor.execute(query, {"threshold": threshold, "name": name}) - return cursor.fetchone() - -PUBLISH_TRAIT_INFO_QUERY = ( - "SELECT " - "PublishXRef.Id, Publication.PubMed_ID, " - "Phenotype.Pre_publication_description, " - "Phenotype.Post_publication_description, " - "Phenotype.Original_description, " - "Phenotype.Pre_publication_abbreviation, " - "Phenotype.Post_publication_abbreviation, " - "Phenotype.Lab_code, Phenotype.Submitter, Phenotype.Owner, " - "Phenotype.Authorized_Users, CAST(Publication.Authors AS BINARY), " - "Publication.Title, Publication.Abstract, Publication.Journal, " - "Publication.Volume, Publication.Pages, Publication.Month, " - "Publication.Year, PublishXRef.Sequence, Phenotype.Units, " - "PublishXRef.comments " - "FROM " - "PublishXRef, Publication, Phenotype, PublishFreeze " - "WHERE " - "PublishXRef.Id = %(trait_name)s AND " - "Phenotype.Id = PublishXRef.PhenotypeId AND " - "Publication.Id = PublishXRef.PublicationId AND " - "PublishXRef.InbredSetId = PublishFreeze.InbredSetId AND " - "PublishFreeze.Id =%(trait_dataset_id)s") - - def retrieve_publish_trait_info(trait_data_source: Dict[str, Any], conn: Any): """Retrieve trait information for type `Publish` traits. https://github.com/genenetwork/genenetwork1/blob/master/web/webqtl/base/webqtlTrait.py#L399-L421""" + keys = ( + "Id", "PubMed_ID", "Pre_publication_description", + "Post_publication_description", "Original_description", + "Pre_publication_abbreviation", "Post_publication_abbreviation", + "Lab_code", "Submitter", "Owner", "Authorized_Users", "Authors", + "Title", "Abstract", "Journal", "Volume", "Pages", "Month", "Year", + "Sequence", "Units", "comments") + columns = ( + "PublishXRef.Id, Publication.PubMed_ID, " + "Phenotype.Pre_publication_description, " + "Phenotype.Post_publication_description, " + "Phenotype.Original_description, " + "Phenotype.Pre_publication_abbreviation, " + "Phenotype.Post_publication_abbreviation, " + "Phenotype.Lab_code, Phenotype.Submitter, Phenotype.Owner, " + "Phenotype.Authorized_Users, CAST(Publication.Authors AS BINARY), " + "Publication.Title, Publication.Abstract, Publication.Journal, " + "Publication.Volume, Publication.Pages, Publication.Month, " + "Publication.Year, PublishXRef.Sequence, Phenotype.Units, " + "PublishXRef.comments") + query = ( + "SELECT " + "{columns} " + "FROM " + "PublishXRef, Publication, Phenotype, PublishFreeze " + "WHERE " + "PublishXRef.Id = %(trait_name)s AND " + "Phenotype.Id = PublishXRef.PhenotypeId AND " + "Publication.Id = PublishXRef.PublicationId AND " + "PublishXRef.InbredSetId = PublishFreeze.InbredSetId AND " + "PublishFreeze.Id =%(trait_dataset_id)s").format(columns=columns) with conn.cursor() as cursor: cursor.execute( - PUBLISH_TRAIT_INFO_QUERY, + query, { k:v for k, v in trait_data_source.items() if k in ["trait_name", "trait_dataset_id"] }) - return cursor.fetchone() - -PROBESET_TRAIT_INFO_QUERY = ( - "SELECT " - "ProbeSet.name, ProbeSet.symbol, ProbeSet.description, " - "ProbeSet.probe_target_description, ProbeSet.chr, ProbeSet.mb, " - "ProbeSet.alias, ProbeSet.geneid, ProbeSet.genbankid, ProbeSet.unigeneid, " - "ProbeSet.omim, ProbeSet.refseq_transcriptid, ProbeSet.blatseq, " - "ProbeSet.targetseq, ProbeSet.chipid, ProbeSet.comments, " - "ProbeSet.strand_probe, ProbeSet.strand_gene, " - "ProbeSet.probe_set_target_region, ProbeSet.proteinid, " - "ProbeSet.probe_set_specificity, ProbeSet.probe_set_blat_score, " - "ProbeSet.probe_set_blat_mb_start, ProbeSet.probe_set_blat_mb_end, " - "ProbeSet.probe_set_strand, ProbeSet.probe_set_note_by_rw, " - "ProbeSet.flag " - "FROM " - "ProbeSet, ProbeSetFreeze, ProbeSetXRef " - "WHERE " - "ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id AND " - "ProbeSetXRef.ProbeSetId = ProbeSet.Id AND " - "ProbeSetFreeze.Name = %(trait_dataset_name)s AND " - "ProbeSet.Name = %(trait_name)s") + return dict(zip([k.lower() for k in keys], cursor.fetchone())) + +def set_confidential_field(trait_type, trait_info): + """Post processing function for 'Publish' trait types. + + It sets the value for the 'confidential' key.""" + if trait_type == "Publish": + return { + **trait_info, + "confidential": 1 if ( + trait_info.get("pre_publication_description", None) + and not trait_info.get("pubmed_id", None)) else 0} + return trait_info def retrieve_probeset_trait_info(trait_data_source: Dict[str, Any], conn: Any): """Retrieve trait information for type `ProbeSet` traits. https://github.com/genenetwork/genenetwork1/blob/master/web/webqtl/base/webqtlTrait.py#L424-L435""" + keys = ( + "name", "symbol", "description", "probe_target_description", "chr", + "mb", "alias", "geneid", "genbankid", "unigeneid", "omim", + "refseq_transcriptid", "blatseq", "targetseq", "chipid", "comments", + "strand_probe", "strand_gene", "probe_set_target_region", "proteinid", + "probe_set_specificity", "probe_set_blat_score", + "probe_set_blat_mb_start", "probe_set_blat_mb_end", "probe_set_strand", + "probe_set_note_by_rw", "flag") + query = ( + "SELECT " + "{columns} " + "FROM " + "ProbeSet, ProbeSetFreeze, ProbeSetXRef " + "WHERE " + "ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id AND " + "ProbeSetXRef.ProbeSetId = ProbeSet.Id AND " + "ProbeSetFreeze.Name = %(trait_dataset_name)s AND " + "ProbeSet.Name = %(trait_name)s").format( + columns=", ".join(["ProbeSet.{}".format(x) for x in keys])) with conn.cursor() as cursor: cursor.execute( - PROBESET_TRAIT_INFO_QUERY, + query, { k:v for k, v in trait_data_source.items() if k in ["trait_name", "trait_dataset_name"] }) - return cursor.fetchone() - -GENO_TRAIT_INFO_QUERY = ( - "SELECT " - "Geno.name, Geno.chr, Geno.mb, Geno.source2, Geno.sequence " - "FROM " - "Geno, GenoFreeze, GenoXRef " - "WHERE " - "GenoXRef.GenoFreezeId = GenoFreeze.Id AND GenoXRef.GenoId = Geno.Id AND " - "GenoFreeze.Name = %(trait_dataset_name)s AND Geno.Name = %(trait_name)s") + return dict(zip(keys, cursor.fetchone())) def retrieve_geno_trait_info(trait_data_source: Dict[str, Any], conn: Any): """Retrieve trait information for type `Geno` traits. https://github.com/genenetwork/genenetwork1/blob/master/web/webqtl/base/webqtlTrait.py#L438-L449""" + keys = ("name", "chr", "mb", "source2", "sequence") + query = ( + "SELECT " + "{columns} " + "FROM " + "Geno, GenoFreeze, GenoXRef " + "WHERE " + "GenoXRef.GenoFreezeId = GenoFreeze.Id AND GenoXRef.GenoId = Geno.Id AND " + "GenoFreeze.Name = %(trait_dataset_name)s AND " + "Geno.Name = %(trait_name)s").format( + columns=", ".join(["Geno.{}".format(x) for x in keys])) with conn.cursor() as cursor: cursor.execute( - GENO_TRAIT_INFO_QUERY, + query, { k:v for k, v in trait_data_source.items() if k in ["trait_name", "trait_dataset_name"] }) - return cursor.fetchone() - -TEMP_TRAIT_INFO_QUERY = ( - "SELECT name, description FROM Temp " - "WHERE Name = %(trait_name)s") + return dict(zip(keys, cursor.fetchone())) def retrieve_temp_trait_info(trait_data_source: Dict[str, Any], conn: Any): """Retrieve trait information for type `Temp` traits. https://github.com/genenetwork/genenetwork1/blob/master/web/webqtl/base/webqtlTrait.py#L450-452""" + keys = ("name", "description") + query = ( + "SELECT {columns} FROM Temp " + "WHERE Name = %(trait_name)s").format(columns=", ".join(keys)) with conn.cursor() as cursor: cursor.execute( - TEMP_TRAIT_INFO_QUERY, + query, { k:v for k, v in trait_data_source.items() if k in ["trait_name"] }) - return cursor.fetchone() + return dict(zip(keys, cursor.fetchone())) + +def set_haveinfo_field(trait_info): + """ + Common postprocessing function for all trait types. + + Sets the value for the 'haveinfo' field.""" + return {**trait_info, "haveinfo": 1 if trait_info else 0} + +def set_homologene_id_field_probeset(trait_info, conn): + """ + Postprocessing function for 'ProbeSet' traits. + + Sets the value for the 'homologene' key. + """ + query = ( + "SELECT HomologeneId FROM Homologene, Species, InbredSet" + " WHERE Homologene.GeneId = %(geneid)s AND InbredSet.Name = %(riset)s" + " AND InbredSet.SpeciesId = Species.Id AND" + " Species.TaxonomyId = Homologene.TaxonomyId") + with conn.cursor() as cursor: + cursor.execute( + query, + { + k:v for k, v in trait_info.items() + if k in ["geneid", "riset"] + }) + res = cursor.fetchone() + if res: + return {**trait_info, "homologeneid": res[0]} + return {**trait_info, "homologeneid": None} + +def set_homologene_id_field(trait_type, trait_info, conn): + """ + Common postprocessing function for all trait types. + + Sets the value for the 'homologene' key.""" + set_to_null = lambda ti: {**ti, "homologeneid": None} + functions_table = { + "Temp": set_to_null, + "Geno": set_to_null, + "Publish": set_to_null, + "ProbeSet": lambda ti: set_homologene_id_field_probeset(ti, conn) + } + return functions_table[trait_type](trait_info) + +def load_publish_qtl_info(trait_info, conn): + query = ( + "SELECT PublishXRef.Locus, PublishXRef.LRS, PublishXRef.additive " + "FROM PublishXRef, PublishFreeze " + "WHERE PublishXRef.Id = %(trait_name)s " + "AND PublishXRef.InbredSetId = PublishFreeze.InbredSetId " + "AND PublishFreeze.Id = %(dataset_id)s") + with conn.cursor() as cursor: + cursor.execute() + return dict(zip(["locus", "lrs", "additive"], cursor.fetchone())) + return {"locus": "", "lrs": "", "additive": ""} + +def load_probeset_qtl_info(trait_info, conn): + query = ( + "SELECT ProbeSetXRef.Locus, ProbeSetXRef.LRS, ProbeSetXRef.pValue, " + "ProbeSetXRef.mean, ProbeSetXRef.additive " + "FROM ProbeSetXRef, ProbeSet " + "WHERE ProbeSetXRef.ProbeSetId = ProbeSet.Id " + " AND ProbeSet.Name = %(trait_name)s " + "AND ProbeSetXRef.ProbeSetFreezeId = %(dataset_id)s") + with conn.cursor() as cursor: + cursor.execute() + return dict(zip( + ["locus", "lrs", "pvalue", "mean", "additive"], cursor.fetchone())) + return {"locus": "", "lrs": "", "pvalue": "", "mean": "", "additive": ""} + +def load_qtl_info(qtl, trait_type, trait_info, conn): + if not qtl: + return trait_info + qtl_info_functions = { + "Publish": load_publish_qtl_info, + "ProbeSet": load_probeset_qtl_info + } + if trait_inf["name"] not in qtl_info_functions.keys(): + return trait_info + + return qtl_info_functions[trait_type](trait_info, conn) + +def build_trait_name(trait_fullname): + name_parts = trait_fullname.split("::") + assert len(name_parts) >= 2, "Name format error" + return { + "db": {"dataset_name": name_parts[0]}, + "trait_fullname": trait_fullname, + "trait_name": name_parts[1], + "cellid": name_parts[2] if len(name_parts) == 3 else "" + } + +def retrieve_probeset_sequence(trait, conn): + query = ( + "SELECT ProbeSet.BlatSeq " + "FROM ProbeSet, ProbeSetFreeze, ProbeSetXRef " + "WHERE ProbeSet.Id=ProbeSetXRef.ProbeSetId " + "AND ProbeSetFreeze.Id = ProbeSetXRef.ProbeSetFreezeId " + "AND ProbeSet.Name = %(trait_name)s " + "AND ProbeSetFreeze.Name = %(dataset_name)s") + with conn.cursor() as cursor: + cursor.execute( + query, + { + "trait_name": trait["trait_name"], + "dataset_name": trait["db"]["dataset_name"] + }) + seq = cursor.fetchone() + return {**trait, "sequence": seq[0] if seq else ""} def retrieve_trait_info( - trait_type: str, trait_name: str, trait_dataset_id: int, - trait_dataset_name: str, conn: Any): + trait_type: str, threshold: int, trait_full_name: str, conn: Any, + qtl=None): """Retrieves the trait information. https://github.com/genenetwork/genenetwork1/blob/master/web/webqtl/base/webqtlTrait.py#L397-L456 This function, or the dependent functions, might be incomplete as they are currently.""" + trait = build_trait_name(trait_full_name) trait_info_function_table = { "Publish": retrieve_publish_trait_info, "ProbeSet": retrieve_probeset_trait_info, "Geno": retrieve_geno_trait_info, "Temp": retrieve_temp_trait_info } - return trait_info_function_table[trait_type]( + + common_post_processing_fn = compose( + lambda ti: load_qtl_info(qtl, trait_type, ti, conn), + lambda ti: set_homologene_id_field(trait_type, ti, conn), + lambda ti: {"trait_type": trait_type, **ti}, + lambda ti: {**trait, **ti}, + set_haveinfo_field) + + trait_post_processing_functions_table = { + "Publish": compose( + lambda ti: set_confidential_field(trait_type, ti), + common_post_processing_fn), + "ProbeSet": compose( + lambda ti: retrieve_probeset_sequence(ti, conn), + common_post_processing_fn), + "Geno": common_post_processing_fn, + "Temp": common_post_processing_fn + } + + retrieve_info = compose( + trait_post_processing_functions_table[trait_type], + trait_info_function_table[trait_type]) + + trait_dataset = retrieve_trait_dataset(trait_type, trait, threshold, conn) + trait_info = retrieve_info( { - "trait_name": trait_name, - "trait_dataset_id": trait_dataset_id, - "trait_dataset_name":trait_dataset_name + "trait_name": trait["trait_name"], + "trait_dataset_id": trait_dataset["dataset_id"], + "trait_dataset_name": trait_dataset["dataset_name"] }, conn) + return { + **trait_info, + "db": {**trait["db"], **trait_dataset}, + "riset": trait_dataset["riset"] + } |