Replace g.db with database_connection() context manager

* wqflask/wqflask/update_search_results.py: Replace "Flask" and "g"
import with "database_connection".
(GSearch.__init__): Reformat SQL.  Rename "re" to "results" since "re"
is part of the stdlib.
* wqflask/wqflask/marker_regression/rqtl_mapping.py: Replace flask.g
with database_connection.
(write_covarstruct_file): Replace g.db with database_connection().
* wqflask/wqflask/search_results.py: Replace "Flask" and "g" imports
with "database_connection" import.
(trait_info_str): Replace g.db with database_connection.
* wqflask/wqflask/docs.py (Docs): Use dababase_connection() context
manager.
(update_text): Ditto.
* wqflask/wqflask/marker_regression/display_mapping_results.py:
Replace "Flask" and "g" import with "database_connection".
(DisplayMappingResults.__init__): Use database_connection.
(DisplayMappingResults.drawSNPTrackNew): Ditto.

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(