diff options
Diffstat (limited to 'scripts/maintenance')
| -rwxr-xr-x | scripts/maintenance/QTL_Reaper_v6.py | 10 | ||||
| -rw-r--r-- | scripts/maintenance/Update_Case_Attributes_MySQL_tab.py | 2 | ||||
| -rwxr-xr-x | scripts/maintenance/datastructure.py | 177 | ||||
| -rwxr-xr-x | scripts/maintenance/delete_genotypes.py | 16 | ||||
| -rwxr-xr-x | scripts/maintenance/delete_phenotypes.py | 14 | ||||
| -rwxr-xr-x | scripts/maintenance/load_genotypes.py | 20 | ||||
| -rwxr-xr-x | scripts/maintenance/load_phenotypes.py | 35 | ||||
| -rwxr-xr-x | scripts/maintenance/readProbeSetMean_v7.py | 292 | ||||
| -rwxr-xr-x | scripts/maintenance/readProbeSetSE_v7.py | 508 | ||||
| -rw-r--r-- | scripts/maintenance/utilities.py | 89 |
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 |