diff options
Diffstat (limited to 'scripts/maintenance/readProbeSetMean_v7.py')
| -rwxr-xr-x | scripts/maintenance/readProbeSetMean_v7.py | 250 |
1 files changed, 125 insertions, 125 deletions
diff --git a/scripts/maintenance/readProbeSetMean_v7.py b/scripts/maintenance/readProbeSetMean_v7.py index 59a51cf9..a540796a 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( input("Enter GeneChipId:") ) -ProbeSetFreezeId = int( input("Enter ProbeSetFreezeId:") ) +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() @@ -65,25 +65,25 @@ header = list(map(string.strip, header)) nfield = len(header) line = fp.readline() -kj=0 +kj = 0 while line: - line2 = string.split(string.strip(line), '\t') - line2 = list(map(string.strip, line2)) - if len(line2) != nfield: - print(("Error : " + line)) - isCont = 0 + line2 = string.split(string.strip(line), '\t') + line2 = list(map(string.strip, 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 = sorted(map(string.lower, GeneList)) - -if isCont==0: - sys.exit(0) + +if isCont == 0: + sys.exit(0) print(('used ', time.time()-time0, ' seconds')) @@ -104,15 +104,15 @@ 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')) @@ -130,59 +130,60 @@ line2 = string.split(string.strip(line), '\t') line2 = list(map(string.strip, 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]) - + Names.append(item[0]) + print(Names) 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')) @@ -195,11 +196,12 @@ 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] + NameIds[item[0]] = item[1] print(('used ', time.time()-time0, ' seconds')) @@ -220,53 +222,51 @@ kj = 0 values1 = [] values2 = [] while line: - line2 = string.split(string.strip(line), '\t') - line2 = list(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 = string.split(string.strip(line), '\t') + line2 = list(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) con.close() |