diff options
Diffstat (limited to 'wqflask')
| -rw-r--r-- | wqflask/utility/authentication_tools.py | 1 | ||||
| -rw-r--r-- | wqflask/wqflask/docs.py | 19 | ||||
| -rw-r--r-- | wqflask/wqflask/marker_regression/display_mapping_results.py | 47 | ||||
| -rw-r--r-- | wqflask/wqflask/marker_regression/rqtl_mapping.py | 9 | ||||
| -rw-r--r-- | wqflask/wqflask/search_results.py | 12 | ||||
| -rw-r--r-- | wqflask/wqflask/update_search_results.py | 108 |
6 files changed, 88 insertions, 108 deletions
diff --git a/wqflask/utility/authentication_tools.py b/wqflask/utility/authentication_tools.py index afea69e1..96cc0b00 100644 --- a/wqflask/utility/authentication_tools.py +++ b/wqflask/utility/authentication_tools.py @@ -2,6 +2,7 @@ import json import requests from flask import g +from wqflask.database import database_connection from base import webqtlConfig from utility.redis_tools import (get_redis_conn, diff --git a/wqflask/wqflask/docs.py b/wqflask/wqflask/docs.py index 9d58162e..9661f768 100644 --- a/wqflask/wqflask/docs.py +++ b/wqflask/wqflask/docs.py @@ -1,17 +1,16 @@ import codecs from flask import g - +from wqflask.database import database_connection class Docs: def __init__(self, entry, start_vars={}): - sql = """ - SELECT Docs.title, CAST(Docs.content AS BINARY) - FROM Docs - WHERE Docs.entry LIKE %s - """ - result = g.db.execute(sql, str(entry)).fetchone() + results = None + with database_connection() as conn, conn.cursor() as cursor: + cursor.execute("SELECT Docs.title, CAST(Docs.content AS BINARY) " + "FROM Docs WHERE Docs.entry LIKE %s", (str(entry),)) + result = cursor.fetchone() self.entry = entry if result == None: self.title = self.entry.capitalize() @@ -37,8 +36,8 @@ def update_text(start_vars): try: if g.user_session.record['user_email_address'] == "zachary.a.sloan@gmail.com" or g.user_session.record['user_email_address'] == "labwilliams@gmail.com": - sql = "UPDATE Docs SET content='{0}' WHERE entry='{1}';".format( - content, start_vars['entry_type']) - g.db.execute(sql) + with database_connection() as conn, conn.cursor() as cursor: + cursor.execute("UPDATE Docs SET content=%s WHERE entry=%s", + (content, start_vars.get("entry_type"),)) except: pass diff --git a/wqflask/wqflask/marker_regression/display_mapping_results.py b/wqflask/wqflask/marker_regression/display_mapping_results.py index ef0c357e..bb0caab1 100644 --- a/wqflask/wqflask/marker_regression/display_mapping_results.py +++ b/wqflask/wqflask/marker_regression/display_mapping_results.py @@ -34,8 +34,6 @@ from PIL import ImageColor import os import json -from flask import Flask, g - import htmlgen as HT from base import webqtlConfig @@ -45,7 +43,7 @@ from utility import Plot from wqflask.interval_analyst import GeneUtil from base.webqtlConfig import GENERATED_IMAGE_DIR from utility.pillow_utils import draw_rotated_text, draw_open_polygon - +from wqflask.database import database_connection try: # Only import this for Python3 from functools import reduce @@ -461,17 +459,15 @@ class DisplayMappingResults: elif self.dataset.group.species == "rat" and indChr.name == "21": self.ChrList.append(("X", i)) self.ChrList.append((indChr.name, i)) - - self.ChrLengthMbList = g.db.execute(""" - Select - Length from Chr_Length, InbredSet - where - Chr_Length.SpeciesId = InbredSet.SpeciesId AND - InbredSet.Name = '%s' AND - Chr_Length.Name in (%s) - Order by - Chr_Length.OrderId - """ % (self.dataset.group.name, ", ".join(["'%s'" % X[0] for X in self.ChrList[1:]]))) + with database_connection() as conn, conn.cursor() as cursor: + cursor.execute("SELECT Length FROM Chr_Length, InbredSet " + "WHERE Chr_Length.SpeciesId = InbredSet.SpeciesId " + "AND InbredSet.Name = %s AND Chr_Length.Name IN " + f"({', '.join(['%s' for x in self.ChrList[1:]])}) " + "ORDER BY Chr_Length.OrderId", + (self.dataset.group.name, + [x[0] for x in self.ChrList[1:]],)) + self.ChrLengthMbList = cursor.fetchall() self.ChrLengthMbList = [x[0] / 1000000.0 for x in self.ChrLengthMbList] self.ChrLengthMbSum = reduce( @@ -536,8 +532,11 @@ class DisplayMappingResults: self.diffCol = [] for i, strain in enumerate(self.diffCol): - self.diffCol[i] = g.db.execute( - "select Id from Strain where Symbol = %s", strain).fetchone()[0] + with database_connection() as conn, conn.cursor() as cursor: + cursor.execute("SELECT Id FROM Strain WHERE Symbol = %s", + (strain,)) + if result := cursor.fetchone(): + self.diffCol[i] = result[0] ################################################################ # GeneCollection goes here @@ -1010,14 +1009,14 @@ class DisplayMappingResults: SNPCounts = [] while startMb < endMb: - snp_count = g.db.execute(""" - select - count(*) from BXDSnpPosition - where - Chr = '%s' AND Mb >= %2.6f AND Mb < %2.6f AND - StrainId1 = %d AND StrainId2 = %d - """ % (chrName, startMb, startMb + stepMb, strainId1, strainId2)).fetchone()[0] - SNPCounts.append(snp_count) + with database_connection() as conn, conn.cursor() as cursor: + # snp count + cursor.execute("SELECT COUNT(*) FROM BXDSnpPosition " + "WHERE Chr = %s AND Mb >= %s AND Mb < %s AND " + "StrainId1 = %s AND StrainId2 = %s", + (chrName, f"{startMb:2.6f}", + f"{startMb + stepMb:2.6f}", strainId1, strainId2,)) + SNPCounts.append(cursor.fetchone()[0]) startMb += stepMb if (len(SNPCounts) > 0): diff --git a/wqflask/wqflask/marker_regression/rqtl_mapping.py b/wqflask/wqflask/marker_regression/rqtl_mapping.py index 7d112c68..1fca5468 100644 --- a/wqflask/wqflask/marker_regression/rqtl_mapping.py +++ b/wqflask/wqflask/marker_regression/rqtl_mapping.py @@ -11,11 +11,10 @@ from typing import TextIO import numpy as np -from flask import g - from base.webqtlConfig import TMPDIR from base.trait import create_trait from utility.tools import locate, GN3_LOCAL_URL +from wqflask.database import database_connection def run_rqtl(trait_name, vals, samples, dataset, pair_scan, mapping_scale, model, method, num_perm, perm_strata_list, do_control, control_marker, manhattan_plot, cofactors): @@ -77,8 +76,10 @@ def write_covarstruct_file(cofactors: str) -> str: a comma-delimited file where the first column consists of cofactor names and the second column indicates whether they're numerical or categorical """ - datatype_query = "SELECT value FROM TraitMetadata WHERE type='trait_data_type'" - trait_datatype_json = json.loads(g.db.execute(datatype_query).fetchone()[0]) + trait_datatype_json = None + with database_connection() as conn, conn.cursor() as cursor: + cursor.execute("SELECT value FROM TraitMetadata WHERE type='trait_data_type'") + trait_datatype_json = json.loads(cursor.fetchone()[0]) covar_struct_file = io.StringIO() writer = csv.writer(covar_struct_file, delimiter="\t", quoting = csv.QUOTE_NONE) diff --git a/wqflask/wqflask/search_results.py b/wqflask/wqflask/search_results.py index a835f631..061fe5b3 100644 --- a/wqflask/wqflask/search_results.py +++ b/wqflask/wqflask/search_results.py @@ -18,7 +18,7 @@ from wqflask import parser from wqflask import do_search from db import webqtlDatabaseFunction -from flask import Flask, g +from wqflask.database import database_connection from utility import hmac, helper_functions from utility.authentication_tools import check_resource_availability @@ -380,11 +380,11 @@ def trait_info_str(trait, dataset_type): __location(trait), __mean(trait), __lrs(trait), __lrs_location(trait)) def get_GO_symbols(a_search): - query = """SELECT genes - FROM GORef - WHERE goterm='{0}:{1}'""".format(a_search['key'], a_search['search_term'][0]) - - gene_list = g.db.execute(query).fetchone()[0].strip().split() + gene_list = None + with database_connection() as conn, conn.cursor() as cursor: + cursor.execute("SELECT genes FROM GORef WHERE goterm=%s", + (f"{a_search['key']}:{a_search['search_term'][0]}",)) + gene_list = cursor.fetchone()[0].strip().split() new_terms = [] for gene in gene_list: diff --git a/wqflask/wqflask/update_search_results.py b/wqflask/wqflask/update_search_results.py index f132e2c6..dcccbf3b 100644 --- a/wqflask/wqflask/update_search_results.py +++ b/wqflask/wqflask/update_search_results.py @@ -1,9 +1,9 @@ import json -from flask import Flask, g from base.data_set import create_dataset from base.trait import GeneralTrait from db import webqtlDatabaseFunction +from wqflask.database import database_connection class GSearch: @@ -13,37 +13,27 @@ class GSearch: self.terms = kw['terms'] #self.row_range = kw['row_range'] if self.type == "gene": - sql = """ - SELECT - Species.`Name` AS species_name, - InbredSet.`Name` AS inbredset_name, - Tissue.`Name` AS tissue_name, - ProbeSetFreeze.Name AS probesetfreeze_name, - ProbeSet.Name AS probeset_name, - ProbeSet.Symbol AS probeset_symbol, - ProbeSet.`description` AS probeset_description, - ProbeSet.Chr AS chr, - ProbeSet.Mb AS mb, - ProbeSetXRef.Mean AS mean, - ProbeSetXRef.LRS AS lrs, - ProbeSetXRef.`Locus` AS locus, - ProbeSetXRef.`pValue` AS pvalue, - ProbeSetXRef.`additive` AS additive - FROM Species, InbredSet, ProbeSetXRef, ProbeSet, ProbeFreeze, ProbeSetFreeze, Tissue - WHERE InbredSet.`SpeciesId`=Species.`Id` - AND ProbeFreeze.InbredSetId=InbredSet.`Id` - AND ProbeFreeze.`TissueId`=Tissue.`Id` - AND ProbeSetFreeze.ProbeFreezeId=ProbeFreeze.Id - AND ( MATCH (ProbeSet.Name,ProbeSet.description,ProbeSet.symbol,alias,GenbankId, UniGeneId, Probe_Target_Description) AGAINST ('%s' IN BOOLEAN MODE) ) - AND ProbeSet.Id = ProbeSetXRef.ProbeSetId - AND ProbeSetXRef.ProbeSetFreezeId=ProbeSetFreeze.Id - AND ProbeSetFreeze.public > 0 - ORDER BY species_name, inbredset_name, tissue_name, probesetfreeze_name, probeset_name - LIMIT 6000 - """ % (self.terms) - re = g.db.execute(sql).fetchall() + results = None + with database_connection() as conn, conn.cursor() as cursor: + cursor.execute(""" +SELECT Species.`Name` AS species_name, InbredSet.`Name` AS inbredset_name, +Tissue.`Name` AS tissue_name, ProbeSetFreeze.Name AS probesetfreeze_name, +ProbeSet.Name AS probeset_name, ProbeSet.Symbol AS probeset_symbol, +ProbeSet.`description` AS probeset_description, ProbeSet.Chr AS chr, ProbeSet.Mb AS mb, +ProbeSetXRef.Mean AS mean, ProbeSetXRef.LRS AS lrs, ProbeSetXRef.`Locus` AS locus, +ProbeSetXRef.`pValue` AS pvalue, ProbeSetXRef.`additive` AS additive +FROM Species, InbredSet, ProbeSetXRef, ProbeSet, ProbeFreeze, ProbeSetFreeze, Tissue +WHERE InbredSet.`SpeciesId`=Species.`Id` AND ProbeFreeze.InbredSetId=InbredSet.`Id` +AND ProbeFreeze.`TissueId`=Tissue.`Id` AND ProbeSetFreeze.ProbeFreezeId=ProbeFreeze.Id +AND ( MATCH (ProbeSet.Name,ProbeSet.description,ProbeSet.symbol,alias,GenbankId, UniGeneId, +Probe_Target_Description) AGAINST (%s IN BOOLEAN MODE) ) +AND ProbeSet.Id = ProbeSetXRef.ProbeSetId AND ProbeSetXRef.ProbeSetFreezeId=ProbeSetFreeze.Id +AND ProbeSetFreeze.public > 0 ORDER BY species_name, inbredset_name, tissue_name, +probesetfreeze_name, probeset_name LIMIT 6000""", + (self.terms,)) + results = cursor.fetchall() self.trait_list = [] - for line in re: + for line in results: dataset = create_dataset( line[3], "ProbeSet", get_samplelist=False) trait_id = line[4] @@ -52,40 +42,30 @@ class GSearch: self.trait_list.append(this_trait) elif self.type == "phenotype": - sql = """ - SELECT - Species.`Name`, - InbredSet.`Name`, - PublishFreeze.`Name`, - PublishXRef.`Id`, - Phenotype.`Post_publication_description`, - Publication.`Authors`, - Publication.`Year`, - PublishXRef.`LRS`, - PublishXRef.`Locus`, - PublishXRef.`additive` - FROM Species,InbredSet,PublishFreeze,PublishXRef,Phenotype,Publication - WHERE PublishXRef.`InbredSetId`=InbredSet.`Id` - AND PublishFreeze.`InbredSetId`=InbredSet.`Id` - AND InbredSet.`SpeciesId`=Species.`Id` - AND PublishXRef.`PhenotypeId`=Phenotype.`Id` - AND PublishXRef.`PublicationId`=Publication.`Id` - AND (Phenotype.Post_publication_description REGEXP "[[:<:]]%s[[:>:]]" - OR Phenotype.Pre_publication_description REGEXP "[[:<:]]%s[[:>:]]" - OR Phenotype.Pre_publication_abbreviation REGEXP "[[:<:]]%s[[:>:]]" - OR Phenotype.Post_publication_abbreviation REGEXP "[[:<:]]%s[[:>:]]" - OR Phenotype.Lab_code REGEXP "[[:<:]]%s[[:>:]]" - OR Publication.PubMed_ID REGEXP "[[:<:]]%s[[:>:]]" - OR Publication.Abstract REGEXP "[[:<:]]%s[[:>:]]" - OR Publication.Title REGEXP "[[:<:]]%s[[:>:]]" - OR Publication.Authors REGEXP "[[:<:]]%s[[:>:]]" - OR PublishXRef.Id REGEXP "[[:<:]]%s[[:>:]]") - ORDER BY Species.`Name`, InbredSet.`Name`, PublishXRef.`Id` - LIMIT 6000 - """ % (self.terms, self.terms, self.terms, self.terms, self.terms, self.terms, self.terms, self.terms, self.terms, self.terms) - re = g.db.execute(sql).fetchall() + with database_connection() as conn, conn.cursor() as cursor: + results = None + cursor.execute(""" +SELECT Species.`Name`, InbredSet.`Name`, PublishFreeze.`Name`, PublishXRef.`Id`, +Phenotype.`Post_publication_description`, Publication.`Authors`, Publication.`Year`, +PublishXRef.`LRS`, PublishXRef.`Locus`, PublishXRef.`additive` FROM Species, InbredSet, +PublishFreeze, PublishXRef, Phenotype, Publication WHERE PublishXRef.`InbredSetId`=InbredSet.`Id` +AND PublishFreeze.`InbredSetId`=InbredSet.`Id` AND InbredSet.`SpeciesId`=Species.`Id` +AND PublishXRef.`PhenotypeId`=Phenotype.`Id` AND PublishXRef.`PublicationId`=Publication.`Id` +AND (Phenotype.Post_publication_description REGEXP "[[:<:]]%s[[:>:]]" +OR Phenotype.Pre_publication_description REGEXP "[[:<:]]%s[[:>:]]" +OR Phenotype.Pre_publication_abbreviation REGEXP "[[:<:]]%s[[:>:]]" +OR Phenotype.Post_publication_abbreviation REGEXP "[[:<:]]%s[[:>:]]" +OR Phenotype.Lab_code REGEXP "[[:<:]]%s[[:>:]]" +OR Publication.PubMed_ID REGEXP "[[:<:]]%s[[:>:]]" +OR Publication.Abstract REGEXP "[[:<:]]%s[[:>:]]" +OR Publication.Title REGEXP "[[:<:]]%s[[:>:]]" +OR Publication.Authors REGEXP "[[:<:]]%s[[:>:]]" +OR PublishXRef.Id REGEXP "[[:<:]]%s[[:>:]]") +ORDER BY Species.`Name`, InbredSet.`Name`, PublishXRef.`Id` LIMIT 6000""", + ((self.terms, ) * 10)) + results = cursor.fetchall() self.trait_list = [] - for line in re: + for line in results: dataset = create_dataset(line[2], "Publish") trait_id = line[3] this_trait = GeneralTrait( |