aboutsummaryrefslogtreecommitdiff
path: root/scripts/maintenance
diff options
context:
space:
mode:
Diffstat (limited to 'scripts/maintenance')
-rwxr-xr-xscripts/maintenance/QTL_Reaper_v6.py10
-rw-r--r--scripts/maintenance/Update_Case_Attributes_MySQL_tab.py2
-rwxr-xr-xscripts/maintenance/datastructure.py177
-rwxr-xr-xscripts/maintenance/delete_genotypes.py16
-rwxr-xr-xscripts/maintenance/delete_phenotypes.py14
-rwxr-xr-xscripts/maintenance/load_genotypes.py20
-rwxr-xr-xscripts/maintenance/load_phenotypes.py35
-rwxr-xr-xscripts/maintenance/readProbeSetMean_v7.py292
-rwxr-xr-xscripts/maintenance/readProbeSetSE_v7.py508
-rw-r--r--scripts/maintenance/utilities.py89
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