Replace fetchall, fetchone, fetch1 with database_connection

* wqflask/base/data_set.py: Replace "db.call" import with
"database_connection".
(create_datasets_list): Use "database_connection" to fetch data.
(DatasetGroup.__init__): Ditto.
(DataSet.retrieve_other_names): Ditto.
(PhenotypeDataSet.setup): Remove query escaping in string and format
the string.
(GenotypeDataSet.setup): Ditto.
(MrnaAssayDataSet.setup): Ditto.
* wqflask/db/webqtlDatabaseFunction.py: Remove db.call import.
(retrieve_species): Use database_connection() to fetch data.
(retrieve_species_id): Ditto.

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]