Merge github.com:genenetwork/genenetwork2 into acenteno

10 files changed, 717 insertions, 446 deletions

diff --git a/scripts/maintenance/QTL_Reaper_v6.py b/scripts/maintenance/QTL_Reaper_v6.py
index e50dbd40..35f2d1a1 100755
--- a/scripts/maintenance/QTL_Reaper_v6.py
+++ b/scripts/maintenance/QTL_Reaper_v6.py
@@ -7,7 +7,7 @@ import reaper
 import MySQLdb
 import time
 
-con = MySQLdb.Connect(db='db_webqtl',user='username',passwd='', host="localhost")
+con = MySQLdb.Connect(db='db_webqtl', user='username', passwd='', host="localhost")
 cursor = con.cursor()
 
 genotypeDir = '/gnshare/gn/web/genotypes/'
@@ -23,7 +23,7 @@ for item in results:
 ProbeSetFreezeIds=sys.argv[1:]
 if ProbeSetFreezeIds:
 	#####convert the Ids to integer
-	ProbeSetFreezeIds=map(int, ProbeSetFreezeIds)
+	ProbeSetFreezeIds=list(map(int, ProbeSetFreezeIds))
 
 else:
 	#####get all of the dataset that need be updated
@@ -53,7 +53,7 @@ for ProbeSetFreezeId in ProbeSetFreezeIds:
 	#if InbredSetId==12:
 	#	InbredSetId=2
 
-	print ProbeSetFreezeId, InbredSets[InbredSetId]
+	print((ProbeSetFreezeId, InbredSets[InbredSetId]))
 
 	genotype_1.read(InbredSets[InbredSetId])
 	locuses = []
@@ -102,7 +102,7 @@ for ProbeSetFreezeId in ProbeSetFreezeIds:
 
 		kj += 1
 		if kj%1000==0:
-			print ProbeSetFreezeId, InbredSets[InbredSetId],kj
+			print((ProbeSetFreezeId, InbredSets[InbredSetId], kj))
 
 
-	print ProbeSetFreezeIds
+	print(ProbeSetFreezeIds)
diff --git a/scripts/maintenance/Update_Case_Attributes_MySQL_tab.py b/scripts/maintenance/Update_Case_Attributes_MySQL_tab.py
index 0f8602c9..bf796df4 100644
--- a/scripts/maintenance/Update_Case_Attributes_MySQL_tab.py
+++ b/scripts/maintenance/Update_Case_Attributes_MySQL_tab.py
@@ -24,4 +24,4 @@ for row in csv_data:
 #close the connection to the database.
 mydb.commit()
 cursor.close()
-print "Done"
\ No newline at end of file
+print("Done")
\ No newline at end of file
diff --git a/scripts/maintenance/datastructure.py b/scripts/maintenance/datastructure.py
new file mode 100755
index 00000000..9f3e8b1e
--- /dev/null
+++ b/scripts/maintenance/datastructure.py
@@ -0,0 +1,177 @@
+import utilities
+
+def get_probesetfreezes(inbredsetid):
+    cursor, con = utilities.get_cursor()
+    sql = """
+        SELECT ProbeSetFreeze.`Id`, ProbeSetFreeze.`Name`, ProbeSetFreeze.`FullName`
+        FROM ProbeSetFreeze, ProbeFreeze
+        WHERE ProbeSetFreeze.`ProbeFreezeId`=ProbeFreeze.`Id`
+        AND ProbeFreeze.`InbredSetId`=%s
+        """
+    cursor.execute(sql, (inbredsetid))
+    return cursor.fetchall()
+
+def get_probesetfreeze(probesetfreezeid):
+    cursor, con = utilities.get_cursor()
+    sql = """
+        SELECT ProbeSetFreeze.`Id`, ProbeSetFreeze.`Name`, ProbeSetFreeze.`FullName`
+        FROM ProbeSetFreeze
+        WHERE ProbeSetFreeze.`Id`=%s
+        """
+    cursor.execute(sql, (probesetfreezeid))
+    return cursor.fetchone()
+    
+def get_strains(inbredsetid):
+    cursor, con = utilities.get_cursor()
+    sql = """
+        SELECT Strain.`Id`, Strain.`Name`
+        FROM StrainXRef, Strain
+        WHERE StrainXRef.`InbredSetId`=%s
+        AND StrainXRef.`StrainId`=Strain.`Id`
+        ORDER BY StrainXRef.`OrderId`
+        """
+    cursor.execute(sql, (inbredsetid))
+    return cursor.fetchall()
+
+def get_inbredset(probesetfreezeid):
+    cursor, con = utilities.get_cursor()
+    sql = """
+        SELECT InbredSet.`Id`, InbredSet.`Name`, InbredSet.`FullName`
+        FROM InbredSet, ProbeFreeze, ProbeSetFreeze
+        WHERE InbredSet.`Id`=ProbeFreeze.`InbredSetId`
+        AND ProbeFreeze.`Id`=ProbeSetFreeze.`ProbeFreezeId`
+        AND ProbeSetFreeze.`Id`=%s
+        """
+    cursor.execute(sql, (probesetfreezeid))
+    return cursor.fetchone()
+    
+def get_species(inbredsetid):
+    cursor, con = utilities.get_cursor()
+    sql = """
+        SELECT Species.`Id`, Species.`Name`, Species.`MenuName`, Species.`FullName`
+        FROM InbredSet, Species
+        WHERE InbredSet.`Id`=%s
+        AND InbredSet.`SpeciesId`=Species.`Id`
+        """
+    cursor.execute(sql, (inbredsetid))
+    return cursor.fetchone()
+    
+def get_genofreeze_byinbredsetid(inbredsetid):
+    cursor, con = utilities.get_cursor()
+    sql = """
+        SELECT GenoFreeze.`Id`, GenoFreeze.`Name`, GenoFreeze.`FullName`, GenoFreeze.`InbredSetId`
+        FROM GenoFreeze
+        WHERE GenoFreeze.`InbredSetId`=%s
+        """
+    cursor.execute(sql, (inbredsetid))
+    return cursor.fetchone()
+
+def get_nextdataid_genotype():
+    cursor, con = utilities.get_cursor()
+    sql = """
+        SELECT GenoData.`Id`
+        FROM GenoData
+        ORDER BY GenoData.`Id` DESC
+        LIMIT 1
+        """
+    cursor.execute(sql)
+    re = cursor.fetchone()
+    dataid = re[0]
+    dataid += 1
+    return dataid
+    
+def get_nextdataid_phenotype():
+    cursor, con = utilities.get_cursor()
+    sql = """
+        SELECT PublishData.`Id`
+        FROM PublishData
+        ORDER BY PublishData.`Id` DESC
+        LIMIT 1
+        """
+    cursor.execute(sql)
+    re = cursor.fetchone()
+    dataid = re[0]
+    dataid += 1
+    return dataid
+
+def get_nextorderid_strainxref(inbredsetid):
+    cursor, con = utilities.get_cursor()
+    sql = """
+        SELECT StrainXRef.`OrderId`
+        FROM StrainXRef
+        WHERE StrainXRef.`InbredSetId`=%s
+        ORDER BY StrainXRef.`OrderId` DESC
+        LIMIT 1
+        """
+    cursor.execute(sql, (inbredsetid))
+    re = cursor.fetchone()
+    if re:
+        orderid = re[0] + 1
+    else:
+        orderid = 1
+    return orderid
+
+def insert_strain(inbredsetid, strainname):
+    speciesid = get_species(inbredsetid)[0]
+    cursor, con = utilities.get_cursor()
+    sql = """
+        INSERT INTO Strain
+        SET
+        Strain.`Name`=%s,
+        Strain.`Name2`=%s,
+        Strain.`SpeciesId`=%s
+        """
+    cursor.execute(sql, (strainname, strainname, speciesid))
+
+def insert_strainxref(inbredsetid, strainid):
+    orderid = get_nextorderid_strainxref(inbredsetid)
+    cursor, con = utilities.get_cursor()
+    sql = """
+        INSERT INTO StrainXRef
+        SET
+        StrainXRef.`InbredSetId`=%s,
+        StrainXRef.`StrainId`=%s,
+        StrainXRef.`OrderId`=%s,
+        StrainXRef.`Used_for_mapping`=%s,
+        StrainXRef.`PedigreeStatus`=%s
+        """
+    cursor.execute(sql, (inbredsetid, strainid, orderid, "N", None))
+
+def get_strain(inbredsetid, strainname):
+    speciesid = get_species(inbredsetid)[0]
+    cursor, con = utilities.get_cursor()
+    sql = """
+        SELECT Strain.`Id`, Strain.`Name`
+        FROM Strain
+        WHERE Strain.`SpeciesId`=%s
+        AND Strain.`Name` LIKE %s
+        """
+    cursor.execute(sql, (speciesid, strainname))
+    return cursor.fetchone()
+
+def get_strainxref(inbredsetid, strainid):
+    cursor, con = utilities.get_cursor()
+    sql = """
+        SELECT StrainXRef.`StrainId`
+        FROM StrainXRef
+        WHERE StrainXRef.`InbredSetId`=%s
+        AND StrainXRef.`StrainId`=%s
+        """
+    cursor.execute(sql, (inbredsetid, strainid))
+    return cursor.fetchone()
+
+def get_strain_sure(inbredsetid, strainname, updatestrainxref=None):
+    strain = get_strain(inbredsetid, strainname)
+    if not strain:
+        insert_strain(inbredsetid, strainname)
+        strain = get_strain(inbredsetid, strainname)
+    strainxref = get_strainxref(inbredsetid, strain[0])
+    if not strainxref and updatestrainxref:
+        insert_strainxref(inbredsetid, strain[0])
+    return strain
+
+def get_strains_bynames(inbredsetid, strainnames, updatestrainxref=None):
+    strains = []
+    for strainname in strainnames:
+        strains.append(get_strain_sure(inbredsetid, strainname, updatestrainxref))
+    return strains
diff --git a/scripts/maintenance/delete_genotypes.py b/scripts/maintenance/delete_genotypes.py
index fa693f0f..b7f83758 100755
--- a/scripts/maintenance/delete_genotypes.py
+++ b/scripts/maintenance/delete_genotypes.py
@@ -8,26 +8,26 @@ import genotypes
 def main(argv):
     # config
     config = utilities.get_config(argv[1])
-    print "config:"
+    print("config:")
     for item in config.items('config'):
-        print "\t%s" % (str(item))
+        print(("\t%s" % (str(item))))
     # var
-    print "variable:"
+    print("variable:")
     inbredsetid = config.get('config', 'inbredsetid')
-    print "\tinbredsetid: %s" % inbredsetid
+    print(("\tinbredsetid: %s" % inbredsetid))
     # datafile
     datafile = open(config.get('config', 'datafile'), 'r')
     datafile = csv.reader(datafile, delimiter='\t', quotechar='"')
-    datafile.next()
+    next(datafile)
     delrowcount = 0
     for row in datafile:
         if len(row) == 0:
             continue
         genoname = row[0]
         delrowcount += genotypes.delete(genoname, inbredsetid)
-    print "deleted %d genotypes" % (delrowcount)
+    print(("deleted %d genotypes" % (delrowcount)))
 
 if __name__ == "__main__":
-    print "command line arguments:\n\t%s" % sys.argv
+    print(("command line arguments:\n\t%s" % sys.argv))
     main(sys.argv)
-    print "exit successfully"
+    print("exit successfully")
diff --git a/scripts/maintenance/delete_phenotypes.py b/scripts/maintenance/delete_phenotypes.py
index 326c466e..60dbec61 100755
--- a/scripts/maintenance/delete_phenotypes.py
+++ b/scripts/maintenance/delete_phenotypes.py
@@ -8,13 +8,13 @@ import phenotypes
 def main(argv):
     # config
     config = utilities.get_config(argv[1])
-    print "config:"
+    print("config:")
     for item in config.items('config'):
-        print "\t%s" % (str(item))
+        print(("\t%s" % (str(item))))
     # var
-    print "variable:"
+    print("variable:")
     inbredsetid = config.get('config', 'inbredsetid')
-    print "\tinbredsetid: %s" % inbredsetid
+    print(("\tinbredsetid: %s" % inbredsetid))
     # datafile
     datafile = open(config.get('config', 'datafile'), 'r')
     datafile = csv.reader(datafile, delimiter='\t', quotechar='"')
@@ -27,9 +27,9 @@ def main(argv):
         except:
             continue
         delrowcount += phenotypes.delete(publishxrefid=publishxrefid, inbredsetid=inbredsetid)
-    print "deleted %d phenotypes" % (delrowcount)
+    print(("deleted %d phenotypes" % (delrowcount)))
 
 if __name__ == "__main__":
-    print "command line arguments:\n\t%s" % sys.argv
+    print(("command line arguments:\n\t%s" % sys.argv))
     main(sys.argv)
-    print "exit successfully"
+    print("exit successfully")
diff --git a/scripts/maintenance/load_genotypes.py b/scripts/maintenance/load_genotypes.py
index 338483f4..51278d48 100755
--- a/scripts/maintenance/load_genotypes.py
+++ b/scripts/maintenance/load_genotypes.py
@@ -8,7 +8,7 @@ def main(argv):
     config = utilities.get_config(argv[1])
     print("config file:")
     for item in config.items('config'):
-        print("\t%s" % str(item))
+        print(("\t%s" % str(item)))
     parse_genofile(config, fetch_parameters(config))
 
 def fetch_parameters(config):
@@ -19,8 +19,8 @@ def fetch_parameters(config):
     config_dic['dataid'] = datastructure.get_nextdataid_genotype()
     config_dic['genofile'] = config.get('config', 'genofile')
     print("config dictionary:")
-    for k, v in config_dic.items():
-        print("\t%s: %s" % (k, v))
+    for k, v in list(config_dic.items()):
+        print(("\t%s: %s" % (k, v)))
     return config_dic
 
 def parse_genofile(config, config_dic):
@@ -42,10 +42,10 @@ def parse_genofile(config, config_dic):
         if line.lower().startswith("chr"):
             #
             print("geno file meta dictionary:")
-            for k, v in meta_dic.items():
-                print("\t%s: %s" % (k, v))
+            for k, v in list(meta_dic.items()):
+                print(("\t%s: %s" % (k, v)))
             #
-            print("geno file head:\n\t%s" % line)
+            print(("geno file head:\n\t%s" % line))
             strainnames = line.split()[4:]
             config_dic['strains'] = datastructure.get_strains_bynames(inbredsetid=config_dic['inbredsetid'], strainnames=strainnames, updatestrainxref="yes")
             continue
@@ -81,7 +81,7 @@ def check_or_insert_geno(config_dic, marker_dic):
     result = cursor.fetchone()
     if result:
         genoid = result[0]
-        print("get geno record: %d" % genoid)
+        print(("get geno record: %d" % genoid))
     else:
         sql = """
             INSERT INTO Geno
@@ -95,7 +95,7 @@ def check_or_insert_geno(config_dic, marker_dic):
         cursor.execute(sql, (config_dic['speciesid'], marker_dic['locus'], marker_dic['locus'], marker_dic['chromosome'], marker_dic['mb']))
         rowcount = cursor.rowcount
         genoid = con.insert_id()
-        print("INSERT INTO Geno: %d record: %d" % (rowcount, genoid))
+        print(("INSERT INTO Geno: %d record: %d" % (rowcount, genoid)))
     return genoid
 
 def check_genoxref(config_dic, marker_dic):
@@ -146,9 +146,9 @@ def insert_genoxref(config_dic, marker_dic):
         """
     cursor.execute(sql, (config_dic['genofreezeid'], marker_dic['genoid'], config_dic['dataid'], marker_dic['cm'], 'N'))
     rowcount = cursor.rowcount
-    print("INSERT INTO GenoXRef: %d record" % (rowcount))
+    print(("INSERT INTO GenoXRef: %d record" % (rowcount)))
 
 if __name__ == "__main__":
-    print("command line arguments:\n\t%s" % sys.argv)
+    print(("command line arguments:\n\t%s" % sys.argv))
     main(sys.argv)
     print("exit successfully")
diff --git a/scripts/maintenance/load_phenotypes.py b/scripts/maintenance/load_phenotypes.py
index c3c6570b..aa02d0cd 100755
--- a/scripts/maintenance/load_phenotypes.py
+++ b/scripts/maintenance/load_phenotypes.py
@@ -1,3 +1,11 @@
+# Load Python3 environment with GN2 utilities:
+#
+#   source /usr/local/guix-profiles/gn-latest-20210512/etc/profile
+#
+# and run
+#
+#   python load_phenotypes.py [args...] 
+
 import sys
 import csv
 
@@ -7,31 +15,30 @@ import datastructure
 def main(argv):
     # config
     config = utilities.get_config(argv[1])
-    print "config:"
+    print("config:")
     for item in config.items('config'):
-        print "\t%s" % (str(item))
+        print("\t%s" % (str(item)))
     # var
     inbredsetid = config.get('config', 'inbredsetid')
-    print "inbredsetid: %s" % inbredsetid
+    print("inbredsetid: %s" % inbredsetid)
     species = datastructure.get_species(inbredsetid)
     speciesid = species[0]
-    print "speciesid: %s" % speciesid
+    print("speciesid: %s" % speciesid)
     dataid = datastructure.get_nextdataid_phenotype()
-    print "next data id: %s" % dataid
+    print("next data id: %s" % dataid)
     cursor, con = utilities.get_cursor()
     # datafile
     datafile = open(config.get('config', 'datafile'), 'r')
     phenotypedata = csv.reader(datafile, delimiter='\t', quotechar='"')
     phenotypedata_head = phenotypedata.next()
-    print "phenotypedata head:\n\t%s" % phenotypedata_head
+    print("phenotypedata head:\n\t%s" % phenotypedata_head)
     strainnames = phenotypedata_head[1:]
     strains = datastructure.get_strains_bynames(inbredsetid=inbredsetid, strainnames=strainnames, updatestrainxref="yes")
     # metafile
     metafile = open(config.get('config', 'metafile'), 'r')
     phenotypemeta = csv.reader(metafile, delimiter='\t', quotechar='"')
     phenotypemeta_head = phenotypemeta.next()
-    print "phenotypemeta head:\n\t%s" % phenotypemeta_head
-    print
+    print("phenotypemeta head:\n\t%s" % phenotypemeta_head)
     # load
     for metarow in phenotypemeta:
         #
@@ -67,7 +74,7 @@ def main(argv):
             ))
         rowcount = cursor.rowcount
         phenotypeid = con.insert_id()
-        print "INSERT INTO Phenotype: %d record: %d" % (rowcount, phenotypeid)
+        print("INSERT INTO Phenotype: %d record: %d" % (rowcount, phenotypeid))
         # Publication
         publicationid = None # reset
         pubmed_id = utilities.to_db_string(metarow[0], None)
@@ -81,7 +88,7 @@ def main(argv):
             re = cursor.fetchone()
             if re:
                 publicationid = re[0]
-                print "get Publication record: %d" % publicationid
+                print("get Publication record: %d" % publicationid)
         if not publicationid:
             sql = """
                 INSERT INTO Publication
@@ -109,7 +116,7 @@ def main(argv):
                 ))
             rowcount = cursor.rowcount
             publicationid = con.insert_id()
-            print "INSERT INTO Publication: %d record: %d" % (rowcount, publicationid)
+            print("INSERT INTO Publication: %d record: %d" % (rowcount, publicationid))
         # data
         for index, strain in enumerate(strains):
             #
@@ -158,7 +165,7 @@ def main(argv):
         cursor.execute(sql, (inbredsetid, phenotypeid, publicationid, dataid, ""))
         rowcount = cursor.rowcount
         publishxrefid = con.insert_id()
-        print "INSERT INTO PublishXRef: %d record: %d" % (rowcount, publishxrefid)
+        print("INSERT INTO PublishXRef: %d record: %d" % (rowcount, publishxrefid))
         # for loop next
         dataid += 1
         print
@@ -166,6 +173,6 @@ def main(argv):
     con.close()
 
 if __name__ == "__main__":
-    print "command line arguments:\n\t%s" % sys.argv
+    print("command line arguments:\n\t%s" % sys.argv)
     main(sys.argv)
-    print "exit successfully"
+    print("exit successfully")
diff --git a/scripts/maintenance/readProbeSetMean_v7.py b/scripts/maintenance/readProbeSetMean_v7.py
index e9c8f25c..43f084f4 100755
--- a/scripts/maintenance/readProbeSetMean_v7.py
+++ b/scripts/maintenance/readProbeSetMean_v7.py
@@ -9,19 +9,17 @@ import sys
 import MySQLdb
 import getpass
 import time
-#import pdb
 
-#pdb.set_trace()
 
 ########################################################################
 
 def translateAlias(str):
-	if str == "B6":
-		return "C57BL/6J"
-	elif str == "D2":
-		return "DBA/2J"
-	else:
-		return str
+    if str == "B6":
+        return "C57BL/6J"
+    elif str == "D2":
+        return "DBA/2J"
+    else:
+        return str
 
 ########################################################################
 #
@@ -29,23 +27,25 @@ def translateAlias(str):
 #
 ########################################################################
 
+
 dataStart = 1
 
-GeneChipId = int( raw_input("Enter GeneChipId:") )
-ProbeSetFreezeId = int( raw_input("Enter ProbeSetFreezeId:") )
-input_file_name = raw_input("Enter file name with suffix:")
+GeneChipId = int(input("Enter GeneChipId:"))
+ProbeSetFreezeId = int(input("Enter ProbeSetFreezeId:"))
+input_file_name = input("Enter file name with suffix:")
 
 fp = open("%s" % input_file_name, 'rb')
 
 try:
-        passwd = getpass.getpass('Please enter mysql password here : ') 
-        con = MySQLdb.Connect(db='db_webqtl',host='localhost', user='username',passwd=passwd)
+    passwd = getpass.getpass('Please enter mysql password here : ')
+    con = MySQLdb.Connect(db='db_webqtl', host='localhost',
+                          user='username', passwd=passwd)
 
-        db = con.cursor()
-        print "You have successfully connected to mysql.\n"
+    db = con.cursor()
+    print("You have successfully connected to mysql.\n")
 except:
-        print "You entered incorrect password.\n"
-        sys.exit(0)
+    print("You entered incorrect password.\n")
+    sys.exit(0)
 
 time0 = time.time()
 
@@ -55,163 +55,163 @@ time0 = time.time()
 #  generate the gene list of expression data here
 #
 #########################################################################
-print 'Checking if each line have same number of members'
+print('Checking if each line have same number of members')
 
 GeneList = []
 isCont = 1
 header = fp.readline()
-header = string.split(string.strip(header),'\t')
-header = map(string.strip, header)
+header = header.strip().split('\t')
+header = [x.strip() for x in header]
 nfield = len(header)
 line = fp.readline()
 
-kj=0
+kj = 0
 while line:
-	line2 = string.split(string.strip(line),'\t')
-	line2 = map(string.strip, line2)
-	if len(line2) != nfield:
-		print "Error : " + line
-		isCont = 0
+    line2 = line.strip().split('\t')
+    line2 = [x.strip() for x in line2]
+    if len(line2) != nfield:
+        print(("Error : " + line))
+        isCont = 0
 
-	GeneList.append(line2[0])
-	line = fp.readline()
+    GeneList.append(line2[0])
+    line = fp.readline()
 
-	kj+=1
-	if kj%100000 == 0:
-		print 'checked ',kj,' lines'
+    kj += 1
+    if kj % 100000 == 0:
+        print(('checked ', kj, ' lines'))
 
-GeneList = map(string.lower, GeneList)
-GeneList.sort()
-	
-if isCont==0:
-	sys.exit(0)
+GeneList = sorted(map(string.lower, GeneList))
 
+if isCont == 0:
+    sys.exit(0)
 
-print 'used ',time.time()-time0,' seconds'
+
+print(('used ', time.time()-time0, ' seconds'))
 #########################################################################
 #
 #  Check if each strain exist in database
 #  generate the string id list of expression data here
 #
 #########################################################################
-print 'Checking if each strain exist in database'
+print('Checking if each strain exist in database')
 
 isCont = 1
 fp.seek(0)
 header = fp.readline()
-header = string.split(string.strip(header),'\t')
-header = map(string.strip, header)
-header = map(translateAlias, header)
+header = header.strip().split('\t')
+header = [x.strip() for x in header]
+header = list(map(translateAlias, header))
 header = header[dataStart:]
 Ids = []
 for item in header:
-	try:
-		db.execute('select Id from Strain where Name = "%s"' % item)
-		Ids.append(db.fetchall()[0][0])
-	except:
-		print item,'does not exist, check the if the strain name is correct'
-		isCont=0
+    try:
+        db.execute('select Id from Strain where Name = "%s"' % item)
+        Ids.append(db.fetchall()[0][0])
+    except:
+        print((item, 'does not exist, check the if the strain name is correct'))
+        isCont = 0
 
-if isCont==0:
-	sys.exit(0)
+if isCont == 0:
+    sys.exit(0)
 
 
-print 'used ',time.time()-time0,' seconds'
+print(('used ', time.time()-time0, ' seconds'))
 ########################################################################
 #
 # Check if each ProbeSet exist in database
 #
 ########################################################################
-print 'Check if each ProbeSet exist in database'
+print('Check if each ProbeSet exist in database')
 
 ##---- find PID is name or target ----##
 line = fp.readline()
 line = fp.readline()
-line2 = string.split(string.strip(line),'\t')
-line2 = map(string.strip, line2)
+line2 = line.strip().split('\t')
+line2 = [x.strip() for x in line2]
 PId = line2[0]
 
-db.execute('select Id from ProbeSet where Name="%s" and ChipId=%d' % (PId, GeneChipId) )
+db.execute('select Id from ProbeSet where Name="%s" and ChipId=%d' %
+           (PId, GeneChipId))
 results = db.fetchall()
 IdStr = 'TargetId'
-if len(results)>0:
-	IdStr = 'Name'
+if len(results) > 0:
+    IdStr = 'Name'
 
 
 ##---- get Name/TargetId list from database ----##
-db.execute('select distinct(%s) from ProbeSet where ChipId=%d order by %s' % (IdStr, GeneChipId, IdStr))
+db.execute('select distinct(%s) from ProbeSet where ChipId=%d order by %s' % (
+    IdStr, GeneChipId, IdStr))
 results = db.fetchall()
-	
+
 Names = []
 for item in results:
-	Names.append(item[0])
-	
-print Names
+    Names.append(item[0])
 
-Names = map(string.lower, Names)
+print(Names)
 
-Names.sort() # -- Fixed the lower case problem of ProbeSets affx-mur_b2_at  doesn't exist --#
+Names = sorted(map(string.lower, Names))
 
 
 ##---- compare genelist with names ----##
-x=y=0
-x1=-1
-GeneList2=[]
-while x<len(GeneList) and y<len(Names):
-	if GeneList[x]==Names[y]:
-		x += 1
-		y += 1
-	elif GeneList[x]<Names[y]:
-		if x!=x1:
-			GeneList2.append(GeneList[x])
-			x1 = x
-		x += 1
-	elif GeneList[x]>Names[y]:
-		y += 1
-
-	if x%100000==0:
-		print 'check Name, checked %d lines'%x
-
-while x<len(GeneList):
-	GeneList2.append(GeneList[x])
-	x += 1
-
-isCont=1
+x = y = 0
+x1 = -1
+GeneList2 = []
+while x < len(GeneList) and y < len(Names):
+    if GeneList[x] == Names[y]:
+        x += 1
+        y += 1
+    elif GeneList[x] < Names[y]:
+        if x != x1:
+            GeneList2.append(GeneList[x])
+            x1 = x
+        x += 1
+    elif GeneList[x] > Names[y]:
+        y += 1
+
+    if x % 100000 == 0:
+        print(('check Name, checked %d lines' % x))
+
+while x < len(GeneList):
+    GeneList2.append(GeneList[x])
+    x += 1
+
+isCont = 1
 ferror = open("ProbeSetError.txt", "wb")
 for item in GeneList2:
-	ferror.write(item + " doesn't exist \n")
-	print item, " doesn't exist, check if the ProbeSet name is correct"
-	isCont = 0
-        
-if isCont==0:
-	sys.exit(0)
+    ferror.write(item + " doesn't exist \n")
+    print((item, " doesn't exist, check if the ProbeSet name is correct"))
+    isCont = 0
+
+if isCont == 0:
+    sys.exit(0)
 
 
-print 'used ',time.time()-time0,' seconds'
+print(('used ', time.time()-time0, ' seconds'))
 #########################################################################
 #
 # Insert data into database
 #
 #########################################################################
-print 'getting ProbeSet/Id'
+print('getting ProbeSet/Id')
 
 
 #---- get Name/Id map ----#
-db.execute('select %s, Id from ProbeSet where ChipId=%d order by %s' % (IdStr, GeneChipId, IdStr))
+db.execute('select %s, Id from ProbeSet where ChipId=%d order by %s' %
+           (IdStr, GeneChipId, IdStr))
 results = db.fetchall()
 NameIds = {}
 for item in results:
-	NameIds[item[0]] = item[1]
-print 'used ',time.time()-time0,' seconds'
+    NameIds[item[0]] = item[1]
+print(('used ', time.time()-time0, ' seconds'))
 
 
-print 'inserting data'
+print('inserting data')
 
 ##---- get old max dataId ----##
 db.execute('select max(Id) from ProbeSetData')
 maxDataId = int(db.fetchall()[0][0])
 bmax = maxDataId
-print "old_max = %d\n" % bmax
+print(("old_max = %d\n" % bmax))
 
 ##---- insert data ----##
 fp.seek(0)
@@ -222,53 +222,51 @@ kj = 0
 values1 = []
 values2 = []
 while line:
-	line2 = string.split(string.strip(line),'\t')
-	line2 = map(string.strip, line2)
-	PId = line2[0]
-	recordId = NameIds[PId]
-
-	maxDataId += 1
-	datasorig = line2[dataStart:]
-	
-	###### Data Table items ######
-	i=0
-	for item in datasorig:
-		try:
-			values1.append('(%d,%d,%s)' % (maxDataId, Ids[i], float(item)))
-      		except:
-			pass
-		i += 1
-
-	values2.append("(%d,%d,%d)" % (ProbeSetFreezeId, recordId, maxDataId))
-
-
-	##---- insert into table ----##
-	kj += 1
-	if kj % 100 == 0:
-		cmd = ','.join(values1)
-		cmd = 'insert into ProbeSetData values %s' % cmd
-		db.execute(cmd)
-
-		cmd = ','.join(values2)
-		cmd = 'insert into ProbeSetXRef(ProbeSetFreezeId, ProbeSetId, DataId) values %s' % cmd
-		db.execute(cmd)
-
-		values1=[]
-		values2=[]
-		print 'Inserted ', kj,' lines'
-		print 'used ',time.time()-time0,' seconds'
-	
-	line = fp.readline()
-
-
-
-if len(values1)>0:
-	cmd = ','.join(values1)
-	cmd = 'insert into ProbeSetData values %s' % cmd
-	db.execute(cmd)
-
-	cmd = ','.join(values2)
-	cmd = 'insert into ProbeSetXRef(ProbeSetFreezeId, ProbeSetId, DataId) values %s' % cmd
-	db.execute(cmd)
+    line2 = line.strip().split('\t')
+    line2 = [x.strip() for x in line2]
+    PId = line2[0]
+    recordId = NameIds[PId]
+
+    maxDataId += 1
+    datasorig = line2[dataStart:]
+
+    ###### Data Table items ######
+    i = 0
+    for item in datasorig:
+        try:
+            values1.append('(%d,%d,%s)' % (maxDataId, Ids[i], float(item)))
+        except:
+            pass
+        i += 1
+
+    values2.append("(%d,%d,%d)" % (ProbeSetFreezeId, recordId, maxDataId))
+
+    ##---- insert into table ----##
+    kj += 1
+    if kj % 100 == 0:
+        cmd = ','.join(values1)
+        cmd = 'insert into ProbeSetData values %s' % cmd
+        db.execute(cmd)
+
+        cmd = ','.join(values2)
+        cmd = 'insert into ProbeSetXRef(ProbeSetFreezeId, ProbeSetId, DataId) values %s' % cmd
+        db.execute(cmd)
+
+        values1 = []
+        values2 = []
+        print(('Inserted ', kj, ' lines'))
+        print(('used ', time.time()-time0, ' seconds'))
+
+    line = fp.readline()
+
+
+if len(values1) > 0:
+    cmd = ','.join(values1)
+    cmd = 'insert into ProbeSetData values %s' % cmd
+    db.execute(cmd)
+
+    cmd = ','.join(values2)
+    cmd = 'insert into ProbeSetXRef(ProbeSetFreezeId, ProbeSetId, DataId) values %s' % cmd
+    db.execute(cmd)
 
 con.close()
diff --git a/scripts/maintenance/readProbeSetSE_v7.py b/scripts/maintenance/readProbeSetSE_v7.py
index fd6f0bb8..2cfe2e07 100755
--- a/scripts/maintenance/readProbeSetSE_v7.py
+++ b/scripts/maintenance/readProbeSetSE_v7.py
@@ -1,254 +1,254 @@
-#!/usr/bin/python2

-"""This script use the nearest marker to the transcript as control, increasing permutation rounds according to the p-value"""

-########################################################################

-# Last Updated Sep 27, 2011 by Xiaodong

-# This version fix the bug that incorrectly exclude the first 2 probesetIDs

-########################################################################

-

-import string

-import sys

-import MySQLdb

-import getpass

-import time

-

-

-def translateAlias(str):

-	if str == "B6":

-		return "C57BL/6J"

-	elif str == "D2":

-		return "DBA/2J"

-	else:

-		return str

-

-########################################################################

-#

-#  Indicate Data Start Position, ProbeFreezeId, GeneChipId, DataFile

-#

-########################################################################

-

-dataStart = 1

-

-GeneChipId = int( raw_input("Enter GeneChipId:") )

-ProbeSetFreezeId = int( raw_input("Enter ProbeSetFreezeId:") )

-input_file_name = raw_input("Enter file name with suffix:")

-

-fp = open("%s" % input_file_name, 'rb')

-

-

-try:

-	passwd = getpass.getpass('Please enter mysql password here : ')

-        con = MySQLdb.Connect(db='db_webqtl',host='localhost', user='username',passwd=passwd)

-

-	db = con.cursor()

-        print "You have successfully connected to mysql.\n"

-except:

-	print "You entered incorrect password.\n"

-        sys.exit(0)

-	

-time0 = time.time()

-########################################################################

-#

-#  Indicate Data Start Position, ProbeFreezeId, GeneChipId, DataFile

-#

-########################################################################

-

-#GeneChipId = 4 

-#dataStart = 1 	

-#ProbeSetFreezeId = 359 #JAX Liver 6C Affy M430 2.0 (Jul11) MDP

-#fp = open("GSE10493_AllSamples_6C_Z_AvgSE.txt", 'rb')

-

-

-#########################################################################

-#

-#  Check if each line have same number of members

-#  generate the gene list of expression data here

-#

-#########################################################################

-print 'Checking if each line have same number of members'

-

-GeneList = []

-isCont = 1

-header = fp.readline()

-header = string.split(string.strip(header),'\t')

-header = map(string.strip, header)

-nfield = len(header)

-line = fp.readline()

-

-kj=0

-while line:

-	line2 = string.split(string.strip(line),'\t')

-	line2 = map(string.strip, line2)

-	if len(line2) != nfield:

-		print "Error : " + line

-		isCont = 0

-

-	GeneList.append(line2[0])

-	line = fp.readline()

-

-	kj+=1

-	if kj%100000 == 0:

-		print 'checked ',kj,' lines'

-

-GeneList = map(string.lower, GeneList)

-GeneList.sort()

-	

-if isCont==0:

-	sys.exit(0)

-

-

-print 'used ',time.time()-time0,' seconds'

-#########################################################################

-#

-#  Check if each strain exist in database

-#  generate the string id list of expression data here

-#

-#########################################################################

-print 'Checking if each strain exist in database'

-

-isCont = 1

-fp.seek(0)

-header = fp.readline()

-header = string.split(string.strip(header),'\t')

-header = map(string.strip, header)

-header = map(translateAlias, header)

-header = header[dataStart:]

-Ids = []

-for item in header:

-	try:

-		db.execute('select Id from Strain where Name = "%s"' % item)

-		Ids.append(db.fetchall()[0][0])

-	except:

-		print item,'does not exist, check the if the strain name is correct'

-		isCont=0

-

-if isCont==0:

-	sys.exit(0)

-

-

-print 'used ',time.time()-time0,' seconds'

-########################################################################

-#

-# Check if each ProbeSet exist in database

-#

-########################################################################

-print 'Check if each ProbeSet exist in database'

-

-##---- find PID is name or target ----##

-line = fp.readline()

-line = fp.readline()

-line2 = string.split(string.strip(line),'\t')

-line2 = map(string.strip, line2)

-PId = line2[0]

-

-db.execute('select Id from ProbeSet where Name="%s" and ChipId=%d' % (PId, GeneChipId))

-results = db.fetchall()

-IdStr = 'TargetId'

-if len(results)>0:

-	IdStr = 'Name'

-

-

-##---- get Name/TargetId list from database ----##

-db.execute('select distinct(%s) from ProbeSet where ChipId=%d order by %s' % (IdStr, GeneChipId, IdStr))

-results = db.fetchall()

-	

-Names = []

-for item in results:

-	Names.append(item[0])

-Names = map(string.lower, Names)

-Names.sort() # -- Fixed the lower case problem of ProbeSets affx-mur_b2_at  doesn't exist --#

-

-##---- compare genelist with names ----##

-x=y=0

-x1=-1

-GeneList2=[]

-while x<len(GeneList) and y<len(Names):

-	if GeneList[x]==Names[y]:

-		x += 1

-		y += 1

-	elif GeneList[x]<Names[y]:

-		if x!=x1:

-			GeneList2.append(GeneList[x])

-			x1 = x

-		x += 1

-	elif GeneList[x]>Names[y]:

-		y += 1

-

-	if x%100000==0:

-		print 'check Name, checked %d lines'%x

-

-while x<len(GeneList):

-	GeneList2.append(GeneList[x])

-	x += 1

-

-isCont=1

-ferror = open("ProbeSetError.txt", "wb")

-for item in GeneList2:

-	ferror.write(item + " doesn't exist \n")

-	print item, " doesn't exist"

-	isCont = 0

-        

-if isCont==0:

-	sys.exit(0)

-

-

-print 'used ',time.time()-time0,' seconds'

-#############################

-#Insert new Data into SE

-############################

-db.execute("""

-	select ProbeSet.%s, ProbeSetXRef.DataId from ProbeSet, ProbeSetXRef 

-	where ProbeSet.Id=ProbeSetXRef.ProbeSetId and ProbeSetXRef.ProbeSetFreezeId=%d"""

-	 % (IdStr, ProbeSetFreezeId))

-results = db.fetchall()

-

-ProbeNameId = {}

-for Name, Id in results:

-	ProbeNameId[Name] = Id

-

-ferror = open("ProbeError.txt", "wb")

-

-DataValues = []

-

-fp.seek(0) #XZ add this line

-line = fp.readline() #XZ add this line

-line = fp.readline()

-

-kj = 0

-while line:

-	line2 = string.split(string.strip(line),'\t')

-	line2 = map(string.strip, line2)

-

-	CellId = line2[0]

-	if not ProbeNameId.has_key(CellId):

-		ferror.write(CellId + " doesn't exist\n")

-		print CellId, " doesn't exist"

-	else:

-		DataId = ProbeNameId[CellId]

-		datasorig = line2[dataStart:]

-

-		i = 0

-		for item in datasorig:

-			if item != '':

-				value = '('+str(DataId)+','+str(Ids[i])+','+str(item)+')'

-				DataValues.append(value)

-			i += 1

-

-	kj += 1

-	if kj % 100 == 0:

-		Dataitems = ','.join(DataValues)

-		cmd = 'insert ProbeSetSE values %s' % Dataitems

-		db.execute(cmd)

-

-		DataValues = []

-		print 'inserted ',kj,' lines'

-		print 'used ',time.time()-time0,' seconds'

-	line = fp.readline()

-

-if len(DataValues)>0:

-	DataValues = ','.join(DataValues)

-	cmd = 'insert ProbeSetSE values %s' % DataValues

-	db.execute(cmd)

-

-con.close()

-

-

+#!/usr/bin/python2
+"""This script use the nearest marker to the transcript as control, increasing permutation rounds according to the p-value"""
+########################################################################
+# Last Updated Sep 27, 2011 by Xiaodong
+# This version fix the bug that incorrectly exclude the first 2 probesetIDs
+########################################################################
+
+import string
+import sys
+import MySQLdb
+import getpass
+import time
+
+
+def translateAlias(str):
+    if str == "B6":
+        return "C57BL/6J"
+    elif str == "D2":
+        return "DBA/2J"
+    else:
+        return str
+
+########################################################################
+#
+#  Indicate Data Start Position, ProbeFreezeId, GeneChipId, DataFile
+#
+########################################################################
+
+
+dataStart = 1
+
+GeneChipId = int(input("Enter GeneChipId:"))
+ProbeSetFreezeId = int(input("Enter ProbeSetFreezeId:"))
+input_file_name = input("Enter file name with suffix:")
+
+fp = open("%s" % input_file_name, 'rb')
+
+
+try:
+    passwd = getpass.getpass('Please enter mysql password here : ')
+    con = MySQLdb.Connect(db='db_webqtl', host='localhost',
+                          user='username', passwd=passwd)
+
+    db = con.cursor()
+    print("You have successfully connected to mysql.\n")
+except:
+    print("You entered incorrect password.\n")
+    sys.exit(0)
+
+time0 = time.time()
+########################################################################
+#
+#  Indicate Data Start Position, ProbeFreezeId, GeneChipId, DataFile
+#
+########################################################################
+
+#GeneChipId = 4
+#dataStart = 1
+# ProbeSetFreezeId = 359 #JAX Liver 6C Affy M430 2.0 (Jul11) MDP
+#fp = open("GSE10493_AllSamples_6C_Z_AvgSE.txt", 'rb')
+
+
+#########################################################################
+#
+#  Check if each line have same number of members
+#  generate the gene list of expression data here
+#
+#########################################################################
+print('Checking if each line have same number of members')
+
+GeneList = []
+isCont = 1
+header = fp.readline()
+header = header.strip().split('\t')
+header = [item.strip() for item in header]
+nfield = len(header)
+line = fp.readline()
+
+kj = 0
+while line:
+    line2 = line.strip().split('\t')
+    line2 = [item.strip() for item in line2]
+    if len(line2) != nfield:
+        isCont = 0
+        print(("Error : " + line))
+
+    GeneList.append(line2[0])
+    line = fp.readline()
+
+    kj += 1
+    if kj % 100000 == 0:
+        print(('checked ', kj, ' lines'))
+
+GeneList = sorted(map(string.lower, GeneList))
+
+if isCont == 0:
+    sys.exit(0)
+
+
+print(('used ', time.time()-time0, ' seconds'))
+#########################################################################
+#
+#  Check if each strain exist in database
+#  generate the string id list of expression data here
+#
+#########################################################################
+print('Checking if each strain exist in database')
+
+isCont = 1
+fp.seek(0)
+header = fp.readline()
+header = header.strip().split('\t')
+header = [item.strip() for item in header]
+header = list(map(translateAlias, header))
+header = header[dataStart:]
+Ids = []
+for item in header:
+    try:
+        db.execute('select Id from Strain where Name = "%s"' % item)
+        Ids.append(db.fetchall()[0][0])
+    except:
+        isCont = 0
+        print((item, 'does not exist, check the if the strain name is correct'))
+
+if isCont == 0:
+    sys.exit(0)
+
+
+print(('used ', time.time()-time0, ' seconds'))
+########################################################################
+#
+# Check if each ProbeSet exist in database
+#
+########################################################################
+print('Check if each ProbeSet exist in database')
+
+##---- find PID is name or target ----##
+line = fp.readline()
+line = fp.readline()
+line2 = line.strip().split('\t')
+line2 = [x.strip() for x in line2]
+PId = line2[0]
+
+db.execute('select Id from ProbeSet where Name="%s" and ChipId=%d' %
+           (PId, GeneChipId))
+results = db.fetchall()
+IdStr = 'TargetId'
+if len(results) > 0:
+    IdStr = 'Name'
+
+
+##---- get Name/TargetId list from database ----##
+db.execute('select distinct(%s) from ProbeSet where ChipId=%d order by %s' % (
+    IdStr, GeneChipId, IdStr))
+results = db.fetchall()
+
+Names = []
+for item in results:
+    Names.append(item[0])
+    Names = sorted(map(string.lower, Names))
+
+##---- compare genelist with names ----##
+x = y = 0
+x1 = -1
+GeneList2 = []
+while x < len(GeneList) and y < len(Names):
+    if GeneList[x] == Names[y]:
+        x += 1
+        y += 1
+    elif GeneList[x] < Names[y]:
+        if x != x1:
+            GeneList2.append(GeneList[x])
+            x1 = x
+            x += 1
+    elif GeneList[x] > Names[y]:
+        y += 1
+
+    if x % 100000 == 0:
+        print(('check Name, checked %d lines' % x))
+
+while x < len(GeneList):
+    GeneList2.append(GeneList[x])
+    x += 1
+
+isCont = 1
+ferror = open("ProbeSetError.txt", "wb")
+for item in GeneList2:
+    ferror.write(item + " doesn't exist \n")
+    isCont = 0
+
+    print((item, " doesn't exist"))
+if isCont == 0:
+    sys.exit(0)
+
+
+print(('used ', time.time()-time0, ' seconds'))
+#############################
+# Insert new Data into SE
+############################
+db.execute("""
+        select ProbeSet.%s, ProbeSetXRef.DataId from ProbeSet, ProbeSetXRef
+        where ProbeSet.Id=ProbeSetXRef.ProbeSetId and ProbeSetXRef.ProbeSetFreezeId=%d"""
+           % (IdStr, ProbeSetFreezeId))
+results = db.fetchall()
+
+ProbeNameId = {}
+for Name, Id in results:
+    ProbeNameId[Name] = Id
+
+ferror = open("ProbeError.txt", "wb")
+
+DataValues = []
+
+fp.seek(0)  # XZ add this line
+line = fp.readline()  # XZ add this line
+line = fp.readline()
+
+kj = 0
+while line:
+    line2 = line.strip().split('\t')
+    line2 = [x.strip() for x in line2]
+
+    CellId = line2[0]
+    if CellId not in ProbeNameId:
+        ferror.write(CellId + " doesn't exist\n")
+    else:
+        DataId = ProbeNameId[CellId]
+        datasorig = line2[dataStart:]
+
+        i = 0
+        for item in datasorig:
+            if item != '':
+                value = '('+str(DataId)+','+str(Ids[i])+','+str(item)+')'
+                DataValues.append(value)
+                i += 1
+
+    kj += 1
+    if kj % 100 == 0:
+        Dataitems = ','.join(DataValues)
+        cmd = 'insert ProbeSetSE values %s' % Dataitems
+        db.execute(cmd)
+
+        DataValues = []
+        line = fp.readline()
+        print((CellId, " doesn't exist"))
+        print(('inserted ', kj, ' lines'))
+        print(('used ', time.time()-time0, ' seconds'))
+
+if len(DataValues) > 0:
+    DataValues = ','.join(DataValues)
+    cmd = 'insert ProbeSetSE values %s' % DataValues
+    db.execute(cmd)
+
+con.close()
diff --git a/scripts/maintenance/utilities.py b/scripts/maintenance/utilities.py
new file mode 100644
index 00000000..886410c2
--- /dev/null
+++ b/scripts/maintenance/utilities.py
@@ -0,0 +1,89 @@
+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)
+        s = s.strip()
+        return s
+    else:
+        return default
+        
+def to_dic(keys, values):
+    dic = {}
+    for i in range(len(keys)):
+        key = keys[i]
+        value = values[i]
+        dic[key] = value
+    return dic
+
+def overlap(dic1, dic2):
+    keys = []
+    values1 = []
+    values2 = []
+    for key in dic1.keys():
+        if key in dic2:
+            value1 = dic1[key]
+            value2 = dic2[key]
+            if value1 and value2:
+                keys.append(key)
+                values1.append(value1)
+                values2.append(value2)
+    return keys, values1, values2
+
+def to_db_string(s, default):
+    if s:
+        s = s.strip()
+        if len(s) == 0:
+            return default
+        elif s == 'x':
+            return default
+        else:
+            return s
+    else:
+        return default
+
+def to_db_float(s, default):
+    if s:
+        s = s.strip()
+        if len(s) == 0:
+            return default
+        elif s == 'x':
+            return default
+        else:
+            try:
+                return float(s)
+            except:
+                return default
+    else:
+        return default
+        
+def to_db_int(s, default):
+    if s:
+        s = s.strip()
+        if len(s) == 0:
+            return default
+        elif s == 'x':
+            return default
+        else:
+            try:
+                return int(s)
+            except:
+                return default
+    else:
+        return default
+
+def get_config(configfile):
+    config = configparser.ConfigParser()
+    config.read(configfile)
+    return config