def AddMap(db,experimentname,mapfilename,studyid=False,deleteifpresent=False,samplesadded=False):
"""
Add a tsv mapping file to the database db mapping file and experiment tables
input:
db - the database
experimentname - name of the experiment (for the experiment table)
mapfilename - name of the mapping file to add
studyid - the id of the study or false to get from mapping file
"""
au.Debug(1,"Adding experiment to mapping database")
# get the studyid
if not studyid:
studyid=GetStudyIDFromMap(mapfilename)
if not studyid:
raise ValueError("study_id not supplied and not in mapping file")
return()
# test if studyid already in database:
db.cur.execute("SELECT * FROM Experiments WHERE StudyID=?",[studyid])
res=db.cur.fetchone()
if res:
if not deleteifpresent:
au.Debug(10,"Experiment",experimentname,"from mapfile",mapfilename,"already in database. id=",studyid)
return()
# if present and deleteifpresent - delete it!
else:
db.cur.execute("DELETE FROM Experiments WHERE StudyID=?",[studyid])
au.Debug(6,"Study already in database - deleted it",studyid)
# add study to Experiments table
db.cur.execute("INSERT INTO Experiments (StudyID,ExpName,MapFileName) VALUES (?,?,?)",(studyid,experimentname,mapfilename))
au.Debug(1,"Added experiment to experiments table")
mf=open(mapfilename,'rU')
reader=csv.DictReader(mf,delimiter='\t')
for cline in reader:
cline=au.dictupper(cline)
# get the sampleids and the studyid
try:
sampleid=cline['#SAMPLEID']
au.Debug(0,"SampleID",sampleid,"found in map",mapfilename)
except:
au.Debug(10,"#SampleID not found in map file",mapfilename)
return()
# if we have a list of samples with enough reads (from addbiomtable) and this sample didn't have enough reads - don't add it
if samplesadded:
if sampleid not in samplesadded:
continue
suid,isnew=GetSuidFromSampleID(db,sampleid=sampleid,expid=studyid,addnew=True)
if not isnew:
au.Debug(1,"Sample already exists - deleting mapping file entries")
db.cur.execute("DELETE FROM Maps WHERE SampleID=?",[suid])
for (field,val) in cline.items():
db.cur.execute("INSERT INTO Maps (SampleID,Field,Value) VALUES (?,?,?)",(suid,field,val))
mf.close()
db.con.commit()
au.Debug(1,"Added experiment to mapping database")
def GetStudyIDFromMap(mapfilename): """ get the study_id value from the mapping file if it exists otherwise, return False """ mf=open(mapfilename,'rU') reader=csv.DictReader(mf,delimiter='\t') cline=reader.next() cline=au.dictupper(cline) if 'STUDY_ID' in cline: studyid=cline['STUDY_ID'] else: au.Debug(6,"STUDY_ID not found in map file",mapfilename) studyid=False mf.close() return studyid
