From 4a7e2c1602ed82aabd7d04953067ba49cb1cebff Mon Sep 17 00:00:00 2001
From: Frederick Muriuki Muriithi
Date: Thu, 10 Mar 2022 08:55:26 +0300
Subject: 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.
---
 scripts/maintenance/QTL_Reaper_v6.py               |  3 +++
 .../Update_Case_Attributes_MySQL_tab.py            | 23 +++++++++-------------
 scripts/maintenance/readProbeSetMean_v7.py         |  1 +
 scripts/maintenance/readProbeSetSE_v7.py           |  1 +
 scripts/maintenance/utilities.py                   | 10 ----------
 5 files changed, 14 insertions(+), 24 deletions(-)

(limited to 'scripts')

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)
-- 
cgit v1.2.3