"""This contains all the necessary functions that are required to add traits to the published database""" from dataclasses import dataclass from typing import Any, Dict, Optional @dataclass(frozen=True) class Riset: """Class for keeping track of riset. A riset is a group e.g. rat HSNIH-Palmer, BXD """ name: Optional[str] r_id: Optional[int] @dataclass(frozen=True) class WebqtlCaseData: """Class for keeping track of one case data in one trait""" value: Optional[float] = None variance: Optional[float] = None count: Optional[int] = None # Number of Individuals def __str__(self): _str = "" if self.value: _str += f"value={self.value:.3f}" if self.variance: _str += f" variance={self.variance:.3f}" if self.count: _str += " n_data={self.count}" return _str def lookup_webqtldataset_name(riset_name: str, conn: Any): """Given a group name(riset), return it's name e.g. BXDPublish, HLCPublish.""" with conn.cursor() as cursor: cursor.execute( "SELECT PublishFreeze.Name FROM " "PublishFreeze, InbredSet WHERE " "PublishFreeze.InbredSetId = InbredSet.Id " "AND InbredSet.Name = '%s'" % riset_name) _result, *_ = cursor.fetchone() return _result def get_riset(data_type: str, name: str, conn: Any): """Get the groups given the data type and it's PublishFreeze or GenoFreeze name """ query, _name, _id = None, None, None if data_type == "Publish": query = ("SELECT InbredSet.Name, InbredSet.Id FROM InbredSet, " "PublishFreeze WHERE PublishFreeze.InbredSetId = " "InbredSet.Id AND PublishFreeze.Name = '%s'" % name) elif data_type == "Geno": query = ("SELECT InbredSet.Name, InbredSet.Id FROM InbredSet, " "GenoFreeze WHERE GenoFreeze.InbredSetId = " "InbredSet.Id AND GenoFreeze.Name = '%s'" % name) elif data_type == "ProbeSet": query = ("SELECT InbredSet.Name, InbredSet.Id FROM " "InbredSet, ProbeSetFreeze, ProbeFreeze WHERE " "ProbeFreeze.InbredSetId = InbredSet.Id AND " "ProbeFreeze.Id = ProbeSetFreeze.ProbeFreezeId AND " "ProbeSetFreeze.Name = '%s'" % name) if query: with conn.cursor() as cursor: _name, _id = cursor.fetchone() if _name == "BXD300": _name = "BXD" return Riset(_name, _id) def insert_publication(pubmed_id: int, publication: Optional[Dict], conn: Any): """Creates a new publication record if it's not available""" sql = ("SELECT Id FROM Publication where " "PubMed_ID = %d" % pubmed_id) _id = None with conn.cursor() as cursor: cursor.execute(sql) _id = cursor.fetchone() if not _id and publication: # The Publication contains the fields: 'authors', 'title', 'abstract', # 'journal','volume','pages','month','year' insert_query = ("INSERT into Publication (%s) Values (%s)" % (", ".join(publication.keys()), ", ".join(['%s'] * len(publication)))) with conn.cursor() as cursor: cursor.execute(insert_query, tuple(publication.values())) def retrieve_trait_dataset_name(trait_type, threshold, name, connection): """ 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, conn): """Retrieve trait information for type `Publish` traits. https://github.com/genenetwork/genenetwork1/blob/master/web/webqtl/base/webqtlTrait.py#L399-L421""" with conn.cursor() as cursor: cursor.execute( PUBLISH_TRAIT_INFO_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") def retrieve_probeset_trait_info(trait_data_source, conn): """Retrieve trait information for type `ProbeSet` traits. https://github.com/genenetwork/genenetwork1/blob/master/web/webqtl/base/webqtlTrait.py#L424-L435""" with conn.cursor() as cursor: cursor.execute( PROBESET_TRAIT_INFO_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") def retrieve_geno_trait_info(trait_data_source, conn): """Retrieve trait information for type `Geno` traits. https://github.com/genenetwork/genenetwork1/blob/master/web/webqtl/base/webqtlTrait.py#L438-L449""" with conn.cursor() as cursor: cursor.execute( GENO_TRAIT_INFO_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") def retrieve_temp_trait_info(trait_data_source, conn): """Retrieve trait information for type `Temp` traits. https://github.com/genenetwork/genenetwork1/blob/master/web/webqtl/base/webqtlTrait.py#L450-452""" with conn.cursor() as cursor: cursor.execute( TEMP_TRAIT_INFO_QUERY, { k:v for k, v in trait_data_source.items() if k in ["trait_name"] }) return cursor.fetchone() def retrieve_trait_info( trait_type, trait_name, trait_dataset_id, trait_dataset_name, conn): """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_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]( { "trait_name": trait_name, "trait_dataset_id": trait_dataset_id, "trait_dataset_name":trait_dataset_name }, conn)