diff options
| -rw-r--r-- | wqflask/base/data_set.py | 115 | ||||
| -rw-r--r-- | wqflask/db/webqtlDatabaseFunction.py | 16 |
2 files changed, 55 insertions, 76 deletions
diff --git a/wqflask/base/data_set.py b/wqflask/base/data_set.py index 33e6f353..57bd6be3 100644 --- a/wqflask/base/data_set.py +++ b/wqflask/base/data_set.py @@ -21,7 +21,6 @@ from dataclasses import dataclass from dataclasses import field from dataclasses import InitVar from typing import Optional, Dict, List -from db.call import fetchall, fetchone, fetch1 from utility.tools import USE_GN_SERVER, USE_REDIS, flat_files, flat_file_exists, GN2_BASE_URL from db.gn_server import menu_main from pprint import pformat as pf @@ -40,6 +39,7 @@ from flask import Flask, g from base.webqtlConfig import TMPDIR from urllib.parse import urlparse from utility.tools import SQL_URI +from wqflask.database import database_connection import os import math import string @@ -205,11 +205,14 @@ def create_datasets_list(): 'Geno': 'GenoFreeze'} for dataset_type in type_dict: - query = "SELECT Name FROM {}".format(type_dict[dataset_type]) - for result in fetchall(query): - dataset = create_dataset(result.Name, dataset_type) - datasets.append(dataset) - + with database_connection() as conn, conn.cursor() as cursor: + cursor.execute("SELECT Name FROM %s", + (type_dict[dataset_type],)) + results = cursor.fetchall(query) + if results: + for result in results: + datasets.append( + create_dataset(result.Name, dataset_type)) if USE_REDIS: r.set(key, pickle.dumps(datasets, pickle.HIGHEST_PROTOCOL)) r.expire(key, 60 * 60) @@ -319,12 +322,16 @@ class DatasetGroup: def __init__(self, dataset, name=None): """This sets self.group and self.group_id""" - if name == None: - self.name, self.id, self.genetic_type, self.code = fetchone( - dataset.query_for_group) - else: - self.name, self.id, self.genetic_type, self.code = fetchone( - "SELECT InbredSet.Name, InbredSet.Id, InbredSet.GeneticType, InbredSet.InbredSetCode FROM InbredSet where Name='%s'" % name) + query = """SELECT InbredSet.Name, InbredSet.Id, InbredSet.GeneticType, + InbredSet.InbredSetCode FROM InbredSet where Name=%s + """ + if not name: + query, name = dataset.query_for_group, self.name + with database_connection() as conn, conn.cursor() as cursor: + cursor.execute(query, (name,)) + results = cursor.fetchone() + if results: + self.name, self.id, self.genetic_type, self.code = results if self.name == 'BXD300': self.name = "BXD" @@ -620,36 +627,25 @@ class DataSet: """ try: + query = "" + _vars = None if self.type == "ProbeSet": - query_args = tuple(escape(x) for x in ( - self.name, - self.name, - self.name)) - - self.id, self.name, self.fullname, self.shortname, self.data_scale, self.tissue = fetch1(""" - SELECT ProbeSetFreeze.Id, ProbeSetFreeze.Name, ProbeSetFreeze.FullName, ProbeSetFreeze.ShortName, ProbeSetFreeze.DataScale, Tissue.Name - FROM ProbeSetFreeze, ProbeFreeze, Tissue - WHERE ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id - AND ProbeFreeze.TissueId = Tissue.Id - AND (ProbeSetFreeze.Name = '%s' OR ProbeSetFreeze.FullName = '%s' OR ProbeSetFreeze.ShortName = '%s') - """ % (query_args), "/dataset/" + self.name + ".json", - lambda r: (r["id"], r["name"], r["full_name"], - r["short_name"], r["data_scale"], r["tissue"]) - ) + query = """ +SELECT ProbeSetFreeze.Id, ProbeSetFreeze.Name, ProbeSetFreeze.FullName, ProbeSetFreeze.ShortName, ProbeSetFreeze.DataScale, Tissue.Name +FROM ProbeSetFreeze, ProbeFreeze, Tissue +WHERE ProbeSetFreeze.ProbeFreezeId = ProbeFreeze.Id +AND ProbeFreeze.TissueId = Tissue.Id +AND (ProbeSetFreeze.Name = %s OR ProbeSetFreeze.FullName = %s OR ProbeSetFreeze.ShortName = %s""" + _vars = (self.id, self.name, self.fullname, self.shortname, self.data_scale, self.tissue,) else: - query_args = tuple(escape(x) for x in ( - (self.type + "Freeze"), - self.name, - self.name, - self.name)) - + query = """ +SELECT Id, Name, FullName, ShortName FROM %s +WHERE (Name = %s OR FullName = '%s' OR ShortName = %s)""" self.tissue = "N/A" - self.id, self.name, self.fullname, self.shortname = fetchone(""" - SELECT Id, Name, FullName, ShortName - FROM %s - WHERE (Name = '%s' OR FullName = '%s' OR ShortName = '%s') - """ % (query_args)) - + _vars = (self.id, self.name, self.fullname, self.shortname,) + with database_connection() as conn, conn.cursor() as cursor: + cursor.execute(query, (query_args,)) + _vars = cursor.fetchone() except TypeError: pass @@ -848,16 +844,8 @@ class PhenotypeDataSet(DataSet): 'Additive Effect'] self.type = 'Publish' - - self.query_for_group = ''' - SELECT - InbredSet.Name, InbredSet.Id, InbredSet.GeneticType, InbredSet.InbredSetCode - FROM - InbredSet, PublishFreeze - WHERE - PublishFreeze.InbredSetId = InbredSet.Id AND - PublishFreeze.Name = "%s" - ''' % escape(self.name) + self.query_for_group = """ +SELECT InbredSet.Name, InbredSet.Id, InbredSet.GeneticType, InbredSet.InbredSetCode FROM InbredSet, PublishFreeze WHERE PublishFreeze.InbredSetId = InbredSet.Id AND PublishFreeze.Name = %s""" def check_confidentiality(self): # (Urgently?) Need to write this @@ -964,16 +952,10 @@ class GenotypeDataSet(DataSet): # Todo: Obsolete or rename this field self.type = 'Geno' - - self.query_for_group = ''' - SELECT - InbredSet.Name, InbredSet.Id, InbredSet.GeneticType, InbredSet.InbredSetCode - FROM - InbredSet, GenoFreeze - WHERE - GenoFreeze.InbredSetId = InbredSet.Id AND - GenoFreeze.Name = "%s" - ''' % escape(self.name) + self.query_for_group = """ +SELECT InbredSet.Name, InbredSet.Id, InbredSet.GeneticType, InbredSet.InbredSetCode +FROM InbredSet, GenoFreeze WHERE GenoFreeze.InbredSetId = InbredSet.Id AND +GenoFreeze.Name = %s""" def check_confidentiality(self): return geno_mrna_confidentiality(self) @@ -1072,17 +1054,10 @@ class MrnaAssayDataSet(DataSet): # Todo: Obsolete or rename this field self.type = 'ProbeSet' - - self.query_for_group = ''' - SELECT - InbredSet.Name, InbredSet.Id, InbredSet.GeneticType, InbredSet.InbredSetCode - FROM - InbredSet, ProbeSetFreeze, ProbeFreeze - WHERE - ProbeFreeze.InbredSetId = InbredSet.Id AND - ProbeFreeze.Id = ProbeSetFreeze.ProbeFreezeId AND - ProbeSetFreeze.Name = "%s" - ''' % escape(self.name) + self.query_for_group = """ +SELECT InbredSet.Name, InbredSet.Id, InbredSet.GeneticType, InbredSet.InbredSetCode +FROM InbredSet, ProbeSetFreeze, ProbeFreeze WHERE ProbeFreeze.InbredSetId = InbredSet.Id AND +ProbeFreeze.Id = ProbeSetFreeze.ProbeFreezeId AND ProbeSetFreeze.Name = %s""" def check_confidentiality(self): return geno_mrna_confidentiality(self) diff --git a/wqflask/db/webqtlDatabaseFunction.py b/wqflask/db/webqtlDatabaseFunction.py index 9ec650a4..122c546f 100644 --- a/wqflask/db/webqtlDatabaseFunction.py +++ b/wqflask/db/webqtlDatabaseFunction.py @@ -20,19 +20,23 @@ # # This module is used by GeneNetwork project (www.genenetwork.org) -from db.call import fetch1 +from wqflask.database import database_connection def retrieve_species(group): """Get the species of a group (e.g. returns string "mouse" on "BXD" """ - result = fetch1("select Species.Name from Species, InbredSet where InbredSet.Name = '%s' and InbredSet.SpeciesId = Species.Id" % ( - group), "/cross/" + group + ".json", lambda r: (r["species"],))[0] + with database_connection() as conn, conn.cursor() as cursor: + cursor.execute( + "SELECT Species.Name FROM Species, InbredSet WHERE InbredSet.Name = %s AND InbredSet.SpeciesId = Species.Id", + (group,)) + return cursor.fetchone()[0] return result def retrieve_species_id(group): - result = fetch1("select SpeciesId from InbredSet where Name = '%s'" % ( - group), "/cross/" + group + ".json", lambda r: (r["species_id"],))[0] - return result + with database_connection() as conn, conn.cursor() as cursor: + cursor.execute("SELECT SpeciesId FROM InbredSet WHERE Name = %s", + (group,)) + return cursor.fetchone()[0] |