Use context manager with database connection

Use the `with` context manager with database connections and cursors
to ensure that they are closed once they are no longer needed.

Where it was not feasible to use the `with` context manager without a
huge refactor/rewrite, the cursors and connections are closed manually.

14 files changed, 365 insertions, 421 deletions

diff --git a/scripts/maintenance/QTL_Reaper_v6.py b/scripts/maintenance/QTL_Reaper_v6.py
index 35f2d1a1..20fd8e3b 100755
--- a/scripts/maintenance/QTL_Reaper_v6.py
+++ b/scripts/maintenance/QTL_Reaper_v6.py
@@ -106,3 +106,6 @@ for ProbeSetFreezeId in ProbeSetFreezeIds:
 
 
 	print(ProbeSetFreezeIds)
+
+cursor.close()
+con.close()
diff --git a/scripts/maintenance/Update_Case_Attributes_MySQL_tab.py b/scripts/maintenance/Update_Case_Attributes_MySQL_tab.py
index bf796df4..a3cd1c35 100644
--- a/scripts/maintenance/Update_Case_Attributes_MySQL_tab.py
+++ b/scripts/maintenance/Update_Case_Attributes_MySQL_tab.py
@@ -10,18 +10,13 @@ import time
 import csv
 ########################################################################
 
-mydb = MySQLdb.connect(host='localhost',
-    user='username',
-    passwd='',
-    db='db_webqtl')
-cursor = mydb.cursor()
+with MySQLdb.connect(
+        host='localhost', user='username', passwd='', db='db_webqtl') as mydb:
+    with mydb.cursor() as cursor:
 
-csv_data = csv.reader(file('GN711_pvalues.txt'), delimiter ="\t")
-for row in csv_data:
-
-    cursor.execute("""UPDATE ProbeSetXRef SET pValue = %s WHERE ProbeSetFreezeId = %s AND ProbeSetId = %s """,
-          (row))
-#close the connection to the database.
-mydb.commit()
-cursor.close()
-print("Done")
\ No newline at end of file
+        csv_data = csv.reader(file('GN711_pvalues.txt'), delimiter ="\t")
+        for row in csv_data:
+            cursor.execute(
+                """UPDATE ProbeSetXRef SET pValue = %s WHERE ProbeSetFreezeId = %s AND ProbeSetId = %s """,
+                (row))
+print("Done")
diff --git a/scripts/maintenance/readProbeSetMean_v7.py b/scripts/maintenance/readProbeSetMean_v7.py
index 43f084f4..56adcdfd 100755
--- a/scripts/maintenance/readProbeSetMean_v7.py
+++ b/scripts/maintenance/readProbeSetMean_v7.py
@@ -269,4 +269,5 @@ if len(values1) > 0:
     cmd = 'insert into ProbeSetXRef(ProbeSetFreezeId, ProbeSetId, DataId) values %s' % cmd
     db.execute(cmd)
 
+db.close()
 con.close()
diff --git a/scripts/maintenance/readProbeSetSE_v7.py b/scripts/maintenance/readProbeSetSE_v7.py
index 2cfe2e07..88a347bf 100755
--- a/scripts/maintenance/readProbeSetSE_v7.py
+++ b/scripts/maintenance/readProbeSetSE_v7.py
@@ -251,4 +251,5 @@ if len(DataValues) > 0:
     cmd = 'insert ProbeSetSE values %s' % DataValues
     db.execute(cmd)
 
+db.close()
 con.close()
diff --git a/scripts/maintenance/utilities.py b/scripts/maintenance/utilities.py
index 886410c2..1fe14809 100644
--- a/scripts/maintenance/utilities.py
+++ b/scripts/maintenance/utilities.py
@@ -1,16 +1,6 @@
-import MySQLdb
 import re
 import configparser
 
-def get_cursor():
-    host = 'tux.uthsc.edu'
-    user = 'webqtlout'
-    passwd = 'webqtlout'
-    db = 'db_webqtl'
-    con = MySQLdb.Connect(db=db, host=host, user=user, passwd=passwd)
-    cursor = con.cursor()
-    return cursor, con
-
 def clearspaces(s, default=None):
     if s:
         s = re.sub('\s+', ' ', s)
diff --git a/wqflask/maintenance/gen_select_dataset.py b/wqflask/maintenance/gen_select_dataset.py
index db65a11f..9f4b670d 100644
--- a/wqflask/maintenance/gen_select_dataset.py
+++ b/wqflask/maintenance/gen_select_dataset.py
@@ -39,21 +39,13 @@ from wqflask import app
 
 from utility.tools import locate, locate_ignore_error, TEMPDIR, SQL_URI
 
-import MySQLdb
-
 import simplejson as json
 import urllib.parse
 
 
-#import sqlalchemy as sa
-
 from pprint import pformat as pf
 
-#Engine = sa.create_engine(zach_settings.SQL_URI)
-
-# build MySql database connection
-
-#conn = Engine.connect()
+from wqflask.database import database_connection
 
 
 def parse_db_uri():
@@ -71,19 +63,19 @@ def parse_db_uri():
     return db_conn_info
 
 
-def get_species():
+def get_species(cursor):
     """Build species list"""
-    #Cursor.execute("select Name, MenuName from Species where Species.Name != 'macaque monkey' order by OrderId")
-    Cursor.execute("select Name, MenuName from Species order by OrderId")
-    species = list(Cursor.fetchall())
+    #cursor.execute("select Name, MenuName from Species where Species.Name != 'macaque monkey' order by OrderId")
+    cursor.execute("select Name, MenuName from Species order by OrderId")
+    species = list(cursor.fetchall())
     return species
 
 
-def get_groups(species):
+def get_groups(cursor, species):
     """Build groups list"""
     groups = {}
     for species_name, _species_full_name in species:
-        Cursor.execute("""select InbredSet.Name, InbredSet.FullName from InbredSet,
+        cursor.execute("""select InbredSet.Name, InbredSet.FullName from InbredSet,
                        Species,
                        ProbeFreeze, GenoFreeze, PublishFreeze where Species.Name = '%s'
                        and InbredSet.SpeciesId = Species.Id and
@@ -92,7 +84,7 @@ def get_groups(species):
                         or ProbeFreeze.InbredSetId = InbredSet.Id)
                         group by InbredSet.Name
                         order by InbredSet.FullName""" % species_name)
-        results = Cursor.fetchall()
+        results = cursor.fetchall()
         groups[species_name] = list(results)
     return groups
 
@@ -273,13 +265,13 @@ def build_datasets(species, group, type_name):
     return datasets
 
 
-def main():
+def main(cursor):
     """Generates and outputs (as json file) the data for the main dropdown menus on the home page"""
 
     parse_db_uri()
 
-    species = get_species()
-    groups = get_groups(species)
+    species = get_species(cursor)
+    groups = get_groups(cursor, species)
     types = get_types(groups)
     datasets = get_datasets(types)
 
@@ -316,6 +308,6 @@ def _test_it():
 
 
 if __name__ == '__main__':
-    Conn = MySQLdb.Connect(**parse_db_uri())
-    Cursor = Conn.cursor()
-    main()
+    with database_connection() as conn:
+        with conn.cursor() as cursor:
+            main(cursor)
diff --git a/wqflask/maintenance/generate_probesetfreeze_file.py b/wqflask/maintenance/generate_probesetfreeze_file.py
index e964c8ed..f43f952b 100644
--- a/wqflask/maintenance/generate_probesetfreeze_file.py
+++ b/wqflask/maintenance/generate_probesetfreeze_file.py
@@ -8,20 +8,11 @@ import os
 import collections
 import csv
 
-import MySQLdb
-
 from base import webqtlConfig
 
 from pprint import pformat as pf
 
-
-def get_cursor():
-    con = MySQLdb.Connect(db=webqtlConfig.DB_UPDNAME,
-                          host=webqtlConfig.MYSQL_UPDSERVER,
-                          user=webqtlConfig.DB_UPDUSER,
-                          passwd=webqtlConfig.DB_UPDPASSWD)
-    cursor = con.cursor()
-    return cursor
+from wqflask.database import database_connection
 
 
 def show_progress(process, counter):
@@ -116,13 +107,14 @@ def main():
         "(Oct08)_RankInv_Beta.txt")
     dataset_name = "Eye_AXBXA_1008_RankInv"
 
-    cursor = get_cursor()
-    strains = get_strains(cursor)
-    print("Getting probset_vals")
-    probeset_vals = get_probeset_vals(cursor, dataset_name)
-    print("Finished getting probeset_vals")
-    trimmed_strains = trim_strains(strains, probeset_vals)
-    write_data_matrix_file(trimmed_strains, probeset_vals, filename)
+    with database_connection as conn:
+        with conn.cursor() as cursor:
+            strains = get_strains(cursor)
+            print("Getting probset_vals")
+            probeset_vals = get_probeset_vals(cursor, dataset_name)
+            print("Finished getting probeset_vals")
+            trimmed_strains = trim_strains(strains, probeset_vals)
+            write_data_matrix_file(trimmed_strains, probeset_vals, filename)
 
 
 if __name__ == '__main__':
diff --git a/wqflask/maintenance/quantile_normalize.py b/wqflask/maintenance/quantile_normalize.py
index 32780ca6..2e2b0ec3 100644
--- a/wqflask/maintenance/quantile_normalize.py
+++ b/wqflask/maintenance/quantile_normalize.py
@@ -1,6 +1,5 @@
 import sys
 sys.path.insert(0, './')
-import MySQLdb
 import urllib.parse
 
 import numpy as np
@@ -9,6 +8,7 @@ import pandas as pd
 from flask import Flask, g, request
 
 from wqflask import app
+from wqflask.database import database_connection
 
 
 def parse_db_uri():
@@ -52,7 +52,7 @@ def quantileNormalize(df_input):
     return df
 
 
-def set_data(dataset_name):
+def set_data(cursor, dataset_name):
     orig_file = "/home/zas1024/cfw_data/" + dataset_name + ".txt"
 
     sample_list = []
@@ -80,8 +80,8 @@ def set_data(dataset_name):
                                  ProbeSetFreeze.Id = ProbeSetXRef.ProbeSetFreezeId and
                                  ProbeSetXRef.ProbeSetId = ProbeSet.Id and
                                  ProbeSet.Name = '%s'""" % (dataset_name, line1.split('\t')[0])
-                Cursor.execute(query)
-                result_info = Cursor.fetchone()
+                cursor.execute(query)
+                result_info = cursor.fetchone()
 
                 yield {
                     "_index": "traits",
@@ -99,15 +99,14 @@ def set_data(dataset_name):
 
 
 if __name__ == '__main__':
-    Conn = MySQLdb.Connect(**parse_db_uri())
-    Cursor = Conn.cursor()
+    with database_connection as conn:
+        with conn.cursor as cursor:
+            success, _ = bulk(es, set_data(cursor, sys.argv[1]))
 
-    success, _ = bulk(es, set_data(sys.argv[1]))
-
-    response = es.search(
-        index="traits", doc_type="trait", body={
-            "query": {"match": {"name": "ENSMUSG00000028982"}}
-        }
-    )
+            response = es.search(
+                index="traits", doc_type="trait", body={
+                    "query": {"match": {"name": "ENSMUSG00000028982"}}
+                }
+            )
 
-    print(response)
+            print(response)
diff --git a/wqflask/maintenance/set_resource_defaults.py b/wqflask/maintenance/set_resource_defaults.py
index 0f472494..22d73ba3 100644
--- a/wqflask/maintenance/set_resource_defaults.py
+++ b/wqflask/maintenance/set_resource_defaults.py
@@ -30,10 +30,9 @@ from utility.tools import SQL_URI
 from utility.redis_tools import get_redis_conn, get_user_id, add_resource, get_resources, get_resource_info
 Redis = get_redis_conn()
 
-import MySQLdb
-
 import urllib.parse
 
+from wqflask.database import database_connection
 from utility.logger import getLogger
 logger = getLogger(__name__)
 
@@ -53,14 +52,14 @@ def parse_db_uri():
     return db_conn_info
 
 
-def insert_probeset_resources(default_owner_id):
+def insert_probeset_resources(cursor, default_owner_id):
     current_resources = Redis.hgetall("resources")
-    Cursor.execute("""  SELECT
+    cursor.execute("""  SELECT
                             ProbeSetFreeze.Id, ProbeSetFreeze.Name, ProbeSetFreeze.confidentiality, ProbeSetFreeze.public
                         FROM
                             ProbeSetFreeze""")
 
-    resource_results = Cursor.fetchall()
+    resource_results = cursor.fetchall()
     for i, resource in enumerate(resource_results):
         resource_ob = {}
         resource_ob['name'] = resource[1]
@@ -80,9 +79,9 @@ def insert_probeset_resources(default_owner_id):
         add_resource(resource_ob, update=False)
 
 
-def insert_publish_resources(default_owner_id):
+def insert_publish_resources(cursor, default_owner_id):
     current_resources = Redis.hgetall("resources")
-    Cursor.execute("""  SELECT 
+    cursor.execute("""  SELECT 
                             PublishXRef.Id, PublishFreeze.Id, InbredSet.InbredSetCode
                         FROM
                             PublishXRef, PublishFreeze, InbredSet, Publication
@@ -91,7 +90,7 @@ def insert_publish_resources(default_owner_id):
                             InbredSet.Id = PublishXRef.InbredSetId AND
                             Publication.Id = PublishXRef.PublicationId""")
 
-    resource_results = Cursor.fetchall()
+    resource_results = cursor.fetchall()
     for resource in resource_results:
         if resource[2]:
             resource_ob = {}
@@ -114,14 +113,14 @@ def insert_publish_resources(default_owner_id):
             continue
 
 
-def insert_geno_resources(default_owner_id):
+def insert_geno_resources(cursor, default_owner_id):
     current_resources = Redis.hgetall("resources")
-    Cursor.execute("""  SELECT
+    cursor.execute("""  SELECT
                             GenoFreeze.Id, GenoFreeze.ShortName, GenoFreeze.confidentiality
                         FROM
                             GenoFreeze""")
 
-    resource_results = Cursor.fetchall()
+    resource_results = cursor.fetchall()
     for i, resource in enumerate(resource_results):
         resource_ob = {}
         resource_ob['name'] = resource[1]
@@ -147,15 +146,15 @@ def insert_geno_resources(default_owner_id):
 def insert_resources(default_owner_id):
     current_resources = get_resources()
     print("START")
-    insert_publish_resources(default_owner_id)
+    insert_publish_resources(cursor, default_owner_id)
     print("AFTER PUBLISH")
-    insert_geno_resources(default_owner_id)
+    insert_geno_resources(cursor, default_owner_id)
     print("AFTER GENO")
-    insert_probeset_resources(default_owner_id)
+    insert_probeset_resources(cursor, default_owner_id)
     print("AFTER PROBESET")
 
 
-def main():
+def main(cursor):
     """Generates and outputs (as json file) the data for the main dropdown menus on the home page"""
 
     Redis.delete("resources")
@@ -166,6 +165,6 @@ def main():
 
 
 if __name__ == '__main__':
-    Conn = MySQLdb.Connect(**parse_db_uri())
-    Cursor = Conn.cursor()
-    main()
+    with database_connection() as conn:
+        with conn.cursor() as cursor:
+            main(cursor)
diff --git a/wqflask/wqflask/api/router.py b/wqflask/wqflask/api/router.py
index a739e5a9..79dc5f1c 100644
--- a/wqflask/wqflask/api/router.py
+++ b/wqflask/wqflask/api/router.py
@@ -6,7 +6,6 @@ import csv
 import json
 import datetime
 import requests
-import MySQLdb
 
 from zipfile import ZipFile, ZIP_DEFLATED
 
@@ -24,6 +23,8 @@ from wqflask.api import correlation, mapping, gen_menu
 
 from utility.tools import flat_files
 
+from wqflask.database import database_connection
+
 import utility.logger
 logger = utility.logger.getLogger(__name__)
 
@@ -847,11 +848,8 @@ def get_genotypes(group_name, file_format="csv", dataset_name=None):
 
 @app.route("/api/v_{}/gen_dropdown".format(version), methods=("GET",))
 def gen_dropdown_menu():
-    conn = MySQLdb.Connect(db=current_app.config.get("DB_NAME"),
-                           user=current_app.config.get("DB_USER"),
-                           passwd=current_app.config.get("DB_PASS"),
-                           host=current_app.config.get("DB_HOST"))
-    results = gen_menu.gen_dropdown_json(conn)
+    with database_connection as conn:
+        results = gen_menu.gen_dropdown_json(conn)
 
     if len(results) > 0:
         return flask.jsonify(results)
diff --git a/wqflask/wqflask/comparison_bar_chart/comparison_bar_chart.py b/wqflask/wqflask/comparison_bar_chart/comparison_bar_chart.py
index cb88eb53..438d2276 100644
--- a/wqflask/wqflask/comparison_bar_chart/comparison_bar_chart.py
+++ b/wqflask/wqflask/comparison_bar_chart/comparison_bar_chart.py
@@ -26,8 +26,6 @@ from utility import webqtlUtil, helper_functions, corr_result_helpers
 import utility.webqtlUtil  # this is for parallel computing only.
 from wqflask.correlation import correlation_functions
 
-from MySQLdb import escape_string as escape
-
 from flask import Flask, g
 
 
diff --git a/wqflask/wqflask/do_search.py b/wqflask/wqflask/do_search.py
index 2cc6aa61..1e245d6a 100644
--- a/wqflask/wqflask/do_search.py
+++ b/wqflask/wqflask/do_search.py
@@ -989,8 +989,6 @@ def get_aliases(symbol, species):
 if __name__ == "__main__":
     # Usually this will be used as a library, but call it from the command line for testing
     # And it runs the code below
-
-    import MySQLdb
     import sys
 
     from base import webqtlConfig
@@ -998,15 +996,11 @@ if __name__ == "__main__":
     from utility import webqtlUtil
     from db import webqtlDatabaseFunction
 
-    db_conn = MySQLdb.Connect(db=webqtlConfig.DB_NAME,
-                              host=webqtlConfig.MYSQL_SERVER,
-                              user=webqtlConfig.DB_USER,
-                              passwd=webqtlConfig.DB_PASSWD)
-    cursor = db_conn.cursor()
-
-    dataset_name = "HC_M2_0606_P"
-    dataset = create_dataset(db_conn, dataset_name)
+    from wqflask.database import database_connection
 
-    results = PvalueSearch(['0.005'], '<', dataset, cursor, db_conn).run()
+    with database_connection() as db_conn:
+        with db_conn.cursor() as cursor:
+            dataset_name = "HC_M2_0606_P"
+            dataset = create_dataset(db_conn, dataset_name)
 
-    db_conn.close()
+            results = PvalueSearch(['0.005'], '<', dataset, cursor, db_conn).run()
diff --git a/wqflask/wqflask/metadata_edits.py b/wqflask/wqflask/metadata_edits.py
index bceb9f3b..cf684bba 100644
--- a/wqflask/wqflask/metadata_edits.py
+++ b/wqflask/wqflask/metadata_edits.py
@@ -7,7 +7,6 @@ from collections import namedtuple
 from itertools import groupby
 from typing import Dict
 
-import MySQLdb
 import difflib
 import redis
 
@@ -45,6 +44,7 @@ from gn3.db.traits import get_trait_csv_sample_data
 from gn3.db.traits import update_sample_data
 from gn3.db.traits import delete_sample_data
 from gn3.db.traits import insert_sample_data
+from wqflask.database import database_connection
 
 
 metadata_edit = Blueprint('metadata_edit', __name__)
@@ -178,261 +178,247 @@ def edit_probeset(conn, name):
 @edit_access_required
 @login_required
 def display_phenotype_metadata(dataset_id: str, name: str):
-    conn = MySQLdb.Connect(db=current_app.config.get("DB_NAME"),
-                           user=current_app.config.get("DB_USER"),
-                           passwd=current_app.config.get("DB_PASS"),
-                           host=current_app.config.get("DB_HOST"))
-    _d = edit_phenotype(conn=conn, name=name, dataset_id=dataset_id)
-    return render_template(
-        "edit_phenotype.html",
-        diff=_d.get("diff"),
-        publish_xref=_d.get("publish_xref"),
-        phenotype=_d.get("phenotype"),
-        publication=_d.get("publication"),
-        dataset_id=dataset_id,
-        resource_id=request.args.get("resource-id"),
-        version=os.environ.get("GN_VERSION"),
-    )
+    with database_connection() as conn:
+        _d = edit_phenotype(conn=conn, name=name, dataset_id=dataset_id)
+        return render_template(
+            "edit_phenotype.html",
+            diff=_d.get("diff"),
+            publish_xref=_d.get("publish_xref"),
+            phenotype=_d.get("phenotype"),
+            publication=_d.get("publication"),
+            dataset_id=dataset_id,
+            resource_id=request.args.get("resource-id"),
+            version=os.environ.get("GN_VERSION"),
+        )
 
 
 @metadata_edit.route("/traits/<name>")
 @edit_access_required
 @login_required
 def display_probeset_metadata(name: str):
-    conn = MySQLdb.Connect(db=current_app.config.get("DB_NAME"),
-                           user=current_app.config.get("DB_USER"),
-                           passwd=current_app.config.get("DB_PASS"),
-                           host=current_app.config.get("DB_HOST"))
-    _d = edit_probeset(conn=conn, name=name)
-    return render_template(
-        "edit_probeset.html",
-        diff=_d.get("diff"),
-        probeset=_d.get("probeset"),
-        name=name,
-        resource_id=request.args.get("resource-id"),
-        version=os.environ.get("GN_VERSION"),
-    )
+    with database_connection as conn:
+        _d = edit_probeset(conn=conn, name=name)
+        return render_template(
+            "edit_probeset.html",
+            diff=_d.get("diff"),
+            probeset=_d.get("probeset"),
+            name=name,
+            resource_id=request.args.get("resource-id"),
+            version=os.environ.get("GN_VERSION"),
+        )
 
 
 @metadata_edit.route("/<dataset_id>/traits/<name>", methods=("POST",))
 @edit_access_required
 @login_required
 def update_phenotype(dataset_id: str, name: str):
-    conn = MySQLdb.Connect(db=current_app.config.get("DB_NAME"),
-                           user=current_app.config.get("DB_USER"),
-                           passwd=current_app.config.get("DB_PASS"),
-                           host=current_app.config.get("DB_HOST"))
-    data_ = request.form.to_dict()
-    TMPDIR = current_app.config.get("TMPDIR")
-    author = ((g.user_session.record.get(b"user_id") or b"").decode("utf-8")
-              or g.user_session.record.get("user_id") or "")
-    phenotype_id = str(data_.get('phenotype-id'))
-    if not (file_ := request.files.get("file")):
-        flash("No sample-data has been uploaded", "warning")
-    else:
-        if not os.path.exists(SAMPLE_DATADIR := os.path.join(TMPDIR, "sample-data")):
-            os.makedirs(SAMPLE_DATADIR)
-        if not os.path.exists(os.path.join(SAMPLE_DATADIR,
-                                           "diffs")):
-            os.makedirs(os.path.join(SAMPLE_DATADIR,
-                                     "diffs"))
-        if not os.path.exists(os.path.join(SAMPLE_DATADIR,
-                                           "updated")):
-            os.makedirs(os.path.join(SAMPLE_DATADIR,
-                                     "updated"))
-        current_time = str(datetime.datetime.now().isoformat())
-        _file_name = (f"{author}.{request.args.get('resource-id')}."
-                      f"{current_time}")
-        new_file_name = (os.path.join(TMPDIR,
-                                      f"sample-data/updated/{_file_name}.csv"))
-        uploaded_file_name = (os.path.join(
-            TMPDIR, "sample-data/updated/",
-            f"{_file_name}.csv.uploaded"))
-        file_.save(new_file_name)
-        with open(uploaded_file_name, "w") as f_:
-            f_.write(get_trait_csv_sample_data(
-                conn=conn,
-                trait_name=str(name),
-                phenotype_id=str(phenotype_id)))
-        r = run_cmd(cmd=("csvdiff "
-                         f"'{uploaded_file_name}' '{new_file_name}' "
-                         "--format json"))
-        json_data = json.loads(r.get("output"))
-
-        # Only consider values where |ε| < 0.001; otherwise, use the
-        # old value in "Original".
-        _modifications = []
-        for m in json_data.get("Modifications"):
-            _original = m.get("Original").split(",")
-            _current = m.get("Current").split(",")
-            for i, (x, y) in enumerate(zip(_original, _current)):
-                if (x.replace('.', '').isdigit()
-                    and y.replace('.', '').isdigit()
-                    and abs(float(x) - float(y)) < 0.001):
-                    _current[i] = x
-            if not (__o:=",".join(_original)) == (__c:=",".join(_current)):
-                    _modifications.append(
-                        {
-                            "Original": __o,
-                            "Current": __c,
-                        })
-        json_data['Modifications'] = _modifications
-
-        # Edge case where the csv file has not been edited!
-        if not any(json_data.values()):
-            flash(f"You have not modified the csv file you downloaded!",
-                  "warning")
-            return redirect(f"/datasets/{dataset_id}/traits/{name}"
-                            f"?resource-id={request.args.get('resource-id')}")
-        diff_output = (f"{TMPDIR}/sample-data/diffs/"
-                       f"{_file_name}.json")
-        with open(diff_output, "w") as f:
-            dict_ = json_data
-            dict_.update({
-                "trait_name": str(name),
+    with database_connection() as conn:
+        data_ = request.form.to_dict()
+        TMPDIR = current_app.config.get("TMPDIR")
+        author = ((g.user_session.record.get(b"user_id") or b"").decode("utf-8")
+                  or g.user_session.record.get("user_id") or "")
+        phenotype_id = str(data_.get('phenotype-id'))
+        if not (file_ := request.files.get("file")):
+            flash("No sample-data has been uploaded", "warning")
+        else:
+            if not os.path.exists(SAMPLE_DATADIR := os.path.join(TMPDIR, "sample-data")):
+                os.makedirs(SAMPLE_DATADIR)
+            if not os.path.exists(os.path.join(SAMPLE_DATADIR,
+                                               "diffs")):
+                os.makedirs(os.path.join(SAMPLE_DATADIR,
+                                         "diffs"))
+            if not os.path.exists(os.path.join(SAMPLE_DATADIR,
+                                               "updated")):
+                os.makedirs(os.path.join(SAMPLE_DATADIR,
+                                         "updated"))
+            current_time = str(datetime.datetime.now().isoformat())
+            _file_name = (f"{author}.{request.args.get('resource-id')}."
+                          f"{current_time}")
+            new_file_name = (os.path.join(TMPDIR,
+                                          f"sample-data/updated/{_file_name}.csv"))
+            uploaded_file_name = (os.path.join(
+                TMPDIR, "sample-data/updated/",
+                f"{_file_name}.csv.uploaded"))
+            file_.save(new_file_name)
+            with open(uploaded_file_name, "w") as f_:
+                f_.write(get_trait_csv_sample_data(
+                    conn=conn,
+                    trait_name=str(name),
+                    phenotype_id=str(phenotype_id)))
+            r = run_cmd(cmd=("csvdiff "
+                             f"'{uploaded_file_name}' '{new_file_name}' "
+                             "--format json"))
+            json_data = json.loads(r.get("output"))
+
+            # Only consider values where |ε| < 0.001; otherwise, use the
+            # old value in "Original".
+            _modifications = []
+            for m in json_data.get("Modifications"):
+                _original = m.get("Original").split(",")
+                _current = m.get("Current").split(",")
+                for i, (x, y) in enumerate(zip(_original, _current)):
+                    if (x.replace('.', '').isdigit()
+                        and y.replace('.', '').isdigit()
+                        and abs(float(x) - float(y)) < 0.001):
+                        _current[i] = x
+                if not (__o:=",".join(_original)) == (__c:=",".join(_current)):
+                        _modifications.append(
+                            {
+                                "Original": __o,
+                                "Current": __c,
+                            })
+            json_data['Modifications'] = _modifications
+
+            # Edge case where the csv file has not been edited!
+            if not any(json_data.values()):
+                flash(f"You have not modified the csv file you downloaded!",
+                      "warning")
+                return redirect(f"/datasets/{dataset_id}/traits/{name}"
+                                f"?resource-id={request.args.get('resource-id')}")
+            diff_output = (f"{TMPDIR}/sample-data/diffs/"
+                           f"{_file_name}.json")
+            with open(diff_output, "w") as f:
+                dict_ = json_data
+                dict_.update({
+                    "trait_name": str(name),
+                    "phenotype_id": str(phenotype_id),
+                    "author": author,
+                    "timestamp": datetime.datetime.now().strftime(
+                        "%Y-%m-%d %H:%M:%S")
+                })
+                f.write(json.dumps(dict_))
+            flash("Sample-data has been successfully uploaded", "success")
+        # Run updates:
+        phenotype_ = {
+            "pre_pub_description": data_.get("pre-pub-desc"),
+            "post_pub_description": data_.get("post-pub-desc"),
+            "original_description": data_.get("orig-desc"),
+            "units": data_.get("units"),
+            "pre_pub_abbreviation": data_.get("pre-pub-abbrev"),
+            "post_pub_abbreviation": data_.get("post-pub-abbrev"),
+            "lab_code": data_.get("labcode"),
+            "submitter": data_.get("submitter"),
+            "owner": data_.get("owner"),
+            "authorized_users": data_.get("authorized-users"),
+        }
+        updated_phenotypes = update(
+            conn, "Phenotype",
+            data=Phenotype(**phenotype_),
+            where=Phenotype(id_=data_.get("phenotype-id")))
+        diff_data = {}
+        if updated_phenotypes:
+            diff_data.update({"Phenotype": diff_from_dict(old={
+                k: data_.get(f"old_{k}") for k, v in phenotype_.items()
+                if v is not None}, new=phenotype_)})
+        publication_ = {
+            "abstract": data_.get("abstract"),
+            "authors": data_.get("authors"),
+            "title": data_.get("title"),
+            "journal": data_.get("journal"),
+            "volume": data_.get("volume"),
+            "pages": data_.get("pages"),
+            "month": data_.get("month"),
+            "year": data_.get("year")
+        }
+        updated_publications = update(
+            conn, "Publication",
+            data=Publication(**publication_),
+            where=Publication(id_=data_.get("pubmed-id",
+                                            data_.get("old_id_"))))
+        if updated_publications:
+            diff_data.update({"Publication": diff_from_dict(old={
+                k: data_.get(f"old_{k}") for k, v in publication_.items()
+                if v is not None}, new=publication_)})
+        if diff_data:
+            diff_data.update({
                 "phenotype_id": str(phenotype_id),
+                "dataset_id": name,
+                "resource_id": request.args.get('resource-id'),
                 "author": author,
-                "timestamp": datetime.datetime.now().strftime(
-                    "%Y-%m-%d %H:%M:%S")
+                "timestamp": (datetime
+                              .datetime
+                              .now()
+                              .strftime("%Y-%m-%d %H:%M:%S")),
             })
-            f.write(json.dumps(dict_))
-        flash("Sample-data has been successfully uploaded", "success")
-    # Run updates:
-    phenotype_ = {
-        "pre_pub_description": data_.get("pre-pub-desc"),
-        "post_pub_description": data_.get("post-pub-desc"),
-        "original_description": data_.get("orig-desc"),
-        "units": data_.get("units"),
-        "pre_pub_abbreviation": data_.get("pre-pub-abbrev"),
-        "post_pub_abbreviation": data_.get("post-pub-abbrev"),
-        "lab_code": data_.get("labcode"),
-        "submitter": data_.get("submitter"),
-        "owner": data_.get("owner"),
-        "authorized_users": data_.get("authorized-users"),
-    }
-    updated_phenotypes = update(
-        conn, "Phenotype",
-        data=Phenotype(**phenotype_),
-        where=Phenotype(id_=data_.get("phenotype-id")))
-    diff_data = {}
-    if updated_phenotypes:
-        diff_data.update({"Phenotype": diff_from_dict(old={
-            k: data_.get(f"old_{k}") for k, v in phenotype_.items()
-            if v is not None}, new=phenotype_)})
-    publication_ = {
-        "abstract": data_.get("abstract"),
-        "authors": data_.get("authors"),
-        "title": data_.get("title"),
-        "journal": data_.get("journal"),
-        "volume": data_.get("volume"),
-        "pages": data_.get("pages"),
-        "month": data_.get("month"),
-        "year": data_.get("year")
-    }
-    updated_publications = update(
-        conn, "Publication",
-        data=Publication(**publication_),
-        where=Publication(id_=data_.get("pubmed-id",
-                                        data_.get("old_id_"))))
-    if updated_publications:
-        diff_data.update({"Publication": diff_from_dict(old={
-            k: data_.get(f"old_{k}") for k, v in publication_.items()
-            if v is not None}, new=publication_)})
-    if diff_data:
-        diff_data.update({
-            "phenotype_id": str(phenotype_id),
-            "dataset_id": name,
-            "resource_id": request.args.get('resource-id'),
-            "author": author,
-            "timestamp": (datetime
-                          .datetime
-                          .now()
-                          .strftime("%Y-%m-%d %H:%M:%S")),
-        })
-        insert(conn,
-               table="metadata_audit",
-               data=MetadataAudit(dataset_id=name,
-                                  editor=author,
-                                  json_data=json.dumps(diff_data)))
-        flash(f"Diff-data: \n{diff_data}\nhas been uploaded", "success")
-    return redirect(f"/datasets/{dataset_id}/traits/{name}"
-                    f"?resource-id={request.args.get('resource-id')}")
+            insert(conn,
+                   table="metadata_audit",
+                   data=MetadataAudit(dataset_id=name,
+                                      editor=author,
+                                      json_data=json.dumps(diff_data)))
+            flash(f"Diff-data: \n{diff_data}\nhas been uploaded", "success")
+        return redirect(f"/datasets/{dataset_id}/traits/{name}"
+                        f"?resource-id={request.args.get('resource-id')}")
 
 
 @metadata_edit.route("/traits/<name>", methods=("POST",))
 @edit_access_required
 @login_required
 def update_probeset(name: str):
-    conn = MySQLdb.Connect(db=current_app.config.get("DB_NAME"),
-                           user=current_app.config.get("DB_USER"),
-                           passwd=current_app.config.get("DB_PASS"),
-                           host=current_app.config.get("DB_HOST"))
-    data_ = request.form.to_dict()
-    probeset_ = {
-        "id_": data_.get("id"),
-        "symbol": data_.get("symbol"),
-        "description": data_.get("description"),
-        "probe_target_description": data_.get("probe_target_description"),
-        "chr_": data_.get("chr"),
-        "mb": data_.get("mb"),
-        "alias": data_.get("alias"),
-        "geneid": data_.get("geneid"),
-        "homologeneid": data_.get("homologeneid"),
-        "unigeneid": data_.get("unigeneid"),
-        "omim": data_.get("OMIM"),
-        "refseq_transcriptid": data_.get("refseq_transcriptid"),
-        "blatseq": data_.get("blatseq"),
-        "targetseq": data_.get("targetseq"),
-        "strand_probe": data_.get("Strand_Probe"),
-        "probe_set_target_region": data_.get("probe_set_target_region"),
-        "probe_set_specificity": data_.get("probe_set_specificity"),
-        "probe_set_blat_score": data_.get("probe_set_blat_score"),
-        "probe_set_blat_mb_start": data_.get("probe_set_blat_mb_start"),
-        "probe_set_blat_mb_end": data_.get("probe_set_blat_mb_end"),
-        "probe_set_strand": data_.get("probe_set_strand"),
-        "probe_set_note_by_rw": data_.get("probe_set_note_by_rw"),
-        "flag": data_.get("flag")
-    }
-    diff_data = {}
-    author = ((g.user_session.record.get(b"user_id") or b"").decode("utf-8")
-              or g.user_session.record.get("user_id") or "")
-    if (updated_probeset := update(
-            conn, "ProbeSet",
-            data=Probeset(**probeset_),
-            where=Probeset(id_=data_.get("id")))):
-        diff_data.update({"Probeset": diff_from_dict(old={
-            k: data_.get(f"old_{k}") for k, v in probeset_.items()
-            if v is not None}, new=probeset_)})
-    if diff_data:
-        diff_data.update({"probeset_name": data_.get("probeset_name")})
-        diff_data.update({"author": author})
-        diff_data.update({"resource_id": request.args.get('resource-id')})
-        diff_data.update({"timestamp": datetime.datetime.now().strftime(
-            "%Y-%m-%d %H:%M:%S")})
-        insert(conn,
-               table="metadata_audit",
-               data=MetadataAudit(dataset_id=data_.get("id"),
-                                  editor=author,
-                                  json_data=json.dumps(diff_data)))
-    return redirect(f"/datasets/traits/{name}"
-                    f"?resource-id={request.args.get('resource-id')}")
+    with database_connection as conn:
+        data_ = request.form.to_dict()
+        probeset_ = {
+            "id_": data_.get("id"),
+            "symbol": data_.get("symbol"),
+            "description": data_.get("description"),
+            "probe_target_description": data_.get("probe_target_description"),
+            "chr_": data_.get("chr"),
+            "mb": data_.get("mb"),
+            "alias": data_.get("alias"),
+            "geneid": data_.get("geneid"),
+            "homologeneid": data_.get("homologeneid"),
+            "unigeneid": data_.get("unigeneid"),
+            "omim": data_.get("OMIM"),
+            "refseq_transcriptid": data_.get("refseq_transcriptid"),
+            "blatseq": data_.get("blatseq"),
+            "targetseq": data_.get("targetseq"),
+            "strand_probe": data_.get("Strand_Probe"),
+            "probe_set_target_region": data_.get("probe_set_target_region"),
+            "probe_set_specificity": data_.get("probe_set_specificity"),
+            "probe_set_blat_score": data_.get("probe_set_blat_score"),
+            "probe_set_blat_mb_start": data_.get("probe_set_blat_mb_start"),
+            "probe_set_blat_mb_end": data_.get("probe_set_blat_mb_end"),
+            "probe_set_strand": data_.get("probe_set_strand"),
+            "probe_set_note_by_rw": data_.get("probe_set_note_by_rw"),
+            "flag": data_.get("flag")
+        }
+        diff_data = {}
+        author = ((g.user_session.record.get(b"user_id") or b"").decode("utf-8")
+                  or g.user_session.record.get("user_id") or "")
+        if (updated_probeset := update(
+                conn, "ProbeSet",
+                data=Probeset(**probeset_),
+                where=Probeset(id_=data_.get("id")))):
+            diff_data.update({"Probeset": diff_from_dict(old={
+                k: data_.get(f"old_{k}") for k, v in probeset_.items()
+                if v is not None}, new=probeset_)})
+        if diff_data:
+            diff_data.update({"probeset_name": data_.get("probeset_name")})
+            diff_data.update({"author": author})
+            diff_data.update({"resource_id": request.args.get('resource-id')})
+            diff_data.update({"timestamp": datetime.datetime.now().strftime(
+                "%Y-%m-%d %H:%M:%S")})
+            insert(conn,
+                   table="metadata_audit",
+                   data=MetadataAudit(dataset_id=data_.get("id"),
+                                      editor=author,
+                                      json_data=json.dumps(diff_data)))
+        return redirect(f"/datasets/traits/{name}"
+                        f"?resource-id={request.args.get('resource-id')}")
 
 
 @metadata_edit.route("/<dataset_id>/traits/<phenotype_id>/csv")
 @login_required
 def get_sample_data_as_csv(dataset_id: str, phenotype_id:     int):
-    return Response(
-        get_trait_csv_sample_data(
-            conn=MySQLdb.Connect(db=current_app.config.get("DB_NAME"),
-                                 user=current_app.config.get("DB_USER"),
-                                 passwd=current_app.config.get("DB_PASS"),
-                                 host=current_app.config.get("DB_HOST")),
-            trait_name=str(dataset_id),
-            phenotype_id=str(phenotype_id)),
-        mimetype="text/csv",
-        headers={"Content-disposition":
-                 f"attachment; filename=sample-data-{dataset_id}.csv"}
-    )
+    with database_connection as conn:
+        return Response(
+            get_trait_csv_sample_data(
+                conn=conn,
+                trait_name=str(dataset_id),
+                phenotype_id=str(phenotype_id)),
+            mimetype="text/csv",
+            headers={"Content-disposition":
+                     f"attachment; filename=sample-data-{dataset_id}.csv"}
+        )
 
 
 @metadata_edit.route("/diffs")
@@ -492,20 +478,45 @@ def reject_data(resource_id: str, file_name: str):
 @login_required
 def approve_data(resource_id:str, file_name: str):
     sample_data = {file_name: str}
-    conn = MySQLdb.Connect(db=current_app.config.get("DB_NAME"),
-                           user=current_app.config.get("DB_USER"),
-                           passwd=current_app.config.get("DB_PASS"),
-                           host=current_app.config.get("DB_HOST"))
-    TMPDIR = current_app.config.get("TMPDIR")
-    with open(os.path.join(f"{TMPDIR}/sample-data/diffs",
-                           file_name), 'r') as myfile:
-        sample_data = json.load(myfile)
-    for modification in (
-            modifications := [d for d in sample_data.get("Modifications")]):
-        if modification.get("Current"):
+    with database_connection() as conn:
+        TMPDIR = current_app.config.get("TMPDIR")
+        with open(os.path.join(f"{TMPDIR}/sample-data/diffs",
+                               file_name), 'r') as myfile:
+            sample_data = json.load(myfile)
+        for modification in (
+                modifications := [d for d in sample_data.get("Modifications")]):
+            if modification.get("Current"):
+                (strain_name,
+                 value, se, count) = modification.get("Current").split(",")
+                update_sample_data(
+                    conn=conn,
+                    trait_name=sample_data.get("trait_name"),
+                    strain_name=strain_name,
+                    phenotype_id=int(sample_data.get("phenotype_id")),
+                    value=value,
+                    error=se,
+                    count=count)
+
+        n_deletions = 0
+        for deletion in (deletions := [d for d in sample_data.get("Deletions")]):
+            strain_name, _, _, _ = deletion.split(",")
+            __deletions, _, _ = delete_sample_data(
+                conn=conn,
+                trait_name=sample_data.get("trait_name"),
+                strain_name=strain_name,
+                phenotype_id=int(sample_data.get("phenotype_id")))
+            if __deletions:
+                n_deletions += 1
+            # Remove any data that already exists from sample_data deletes
+            else:
+                sample_data.get("Deletions").remove(deletion)
+
+        n_insertions = 0
+        for insertion in (
+                insertions := [d for d in sample_data.get("Additions")]):
             (strain_name,
-             value, se, count) = modification.get("Current").split(",")
-            update_sample_data(
+             value, se, count) = insertion.split(",")
+            __insertions, _, _ = insert_sample_data(
                 conn=conn,
                 trait_name=sample_data.get("trait_name"),
                 strain_name=strain_name,
@@ -513,65 +524,37 @@ def approve_data(resource_id:str, file_name: str):
                 value=value,
                 error=se,
                 count=count)
-
-    n_deletions = 0
-    for deletion in (deletions := [d for d in sample_data.get("Deletions")]):
-        strain_name, _, _, _ = deletion.split(",")
-        __deletions, _, _ = delete_sample_data(
-            conn=conn,
-            trait_name=sample_data.get("trait_name"),
-            strain_name=strain_name,
-            phenotype_id=int(sample_data.get("phenotype_id")))
-        if __deletions:
-            n_deletions += 1
-        # Remove any data that already exists from sample_data deletes
-        else:
-            sample_data.get("Deletions").remove(deletion)
-
-    n_insertions = 0
-    for insertion in (
-            insertions := [d for d in sample_data.get("Additions")]):
-        (strain_name,
-         value, se, count) = insertion.split(",")
-        __insertions, _, _ = insert_sample_data(
-            conn=conn,
-            trait_name=sample_data.get("trait_name"),
-            strain_name=strain_name,
-            phenotype_id=int(sample_data.get("phenotype_id")),
-            value=value,
-            error=se,
-            count=count)
-        if __insertions:
-            n_insertions += 1
-        # Remove any data that already exists from sample_data inserts
-        else:
-            sample_data.get("Additions").remove(insertion)
-    if any([sample_data.get("Additions"),
-            sample_data.get("Modifications"),
-            sample_data.get("Deletions")]):
-        insert(conn,
-               table="metadata_audit",
-               data=MetadataAudit(
-                   dataset_id=sample_data.get("trait_name"),
-                   editor=sample_data.get("author"),
-                   json_data=json.dumps(sample_data)))
-        # Once data is approved, rename it!
-        os.rename(os.path.join(f"{TMPDIR}/sample-data/diffs", file_name),
-                  os.path.join(f"{TMPDIR}/sample-data/diffs",
-                               f"{file_name}.approved"))
-        message = ""
-        if n_deletions:
-            flash(f"# Deletions: {n_deletions}", "success")
-        if n_insertions:
-            flash("# Additions: {len(modifications)", "success")
-        if len(modifications):
-            flash("# Modifications: {len(modifications)}", "success")
-    else:  # Edge case where you need to automatically reject the file
-        os.rename(os.path.join(f"{TMPDIR}/sample-data/diffs", file_name),
-                  os.path.join(f"{TMPDIR}/sample-data/diffs",
-                               f"{file_name}.rejected"))
-        flash(("Automatically rejecting this file since no "
-               "changes could be applied."), "warning")
-
-    return redirect(url_for('metadata_edit.list_diffs'))
+            if __insertions:
+                n_insertions += 1
+            # Remove any data that already exists from sample_data inserts
+            else:
+                sample_data.get("Additions").remove(insertion)
+        if any([sample_data.get("Additions"),
+                sample_data.get("Modifications"),
+                sample_data.get("Deletions")]):
+            insert(conn,
+                   table="metadata_audit",
+                   data=MetadataAudit(
+                       dataset_id=sample_data.get("trait_name"),
+                       editor=sample_data.get("author"),
+                       json_data=json.dumps(sample_data)))
+            # Once data is approved, rename it!
+            os.rename(os.path.join(f"{TMPDIR}/sample-data/diffs", file_name),
+                      os.path.join(f"{TMPDIR}/sample-data/diffs",
+                                   f"{file_name}.approved"))
+            message = ""
+            if n_deletions:
+                flash(f"# Deletions: {n_deletions}", "success")
+            if n_insertions:
+                flash("# Additions: {len(modifications)", "success")
+            if len(modifications):
+                flash("# Modifications: {len(modifications)}", "success")
+        else:  # Edge case where you need to automatically reject the file
+            os.rename(os.path.join(f"{TMPDIR}/sample-data/diffs", file_name),
+                      os.path.join(f"{TMPDIR}/sample-data/diffs",
+                                   f"{file_name}.rejected"))
+            flash(("Automatically rejecting this file since no "
+                   "changes could be applied."), "warning")
+
+        return redirect(url_for('metadata_edit.list_diffs'))
 
diff --git a/wqflask/wqflask/views.py b/wqflask/wqflask/views.py
index 3bf17da6..59dba309 100644
--- a/wqflask/wqflask/views.py
+++ b/wqflask/wqflask/views.py
@@ -1,6 +1,4 @@
 """Main routing table for GN2"""
-
-import MySQLdb
 import array
 import base64
 import csv
@@ -123,6 +121,7 @@ def shutdown_session(exception=None):
     db = getattr(g, '_database', None)
     if db is not None:
         db_session.remove()
+        g.db.dispose()
         g.db = None