def retrieve_public_experiments():
sql = """select s.ACC from
AE2.SC_LABEL la join AE2.SC_OWNER o on la.ID = o.SC_LABEL_ID
join AE2.STUDY s on la.NAME = s.ACC
where o.SC_USER_ID =1 and s.ACC like 'E-MTAB-%'
order by s.id asc """
return execute_select(sql, db, True)
def retrieve_public_experiments():
sql = """select s.id, s.ACC from
AE2.SC_LABEL la join AE2.SC_OWNER o on la.ID = o.SC_LABEL_ID
join AE2.STUDY s on la.NAME = s.ACC
where o.SC_USER_ID =1 and s.Acc like '%E-MTAB-%'"""
# print(sql)
return execute_select(sql, db, True)
def retrieve_all_experiments(min_date=None):
sql = """select s.id, s.ACC from
AE2.SC_LABEL la join AE2.SC_OWNER o on la.ID = o.SC_LABEL_ID
join AE2.STUDY s on la.NAME = s.ACC"""
if min_date:
sql += """ WHERE s.lastupdatedate >= DATE '%s'""" % min_date
print(sql)
return execute_select(sql, db, True)
def retrieve_owner(acc):
# print(acc, type(acc))
sql = """SELECT * FROM AE2.SC_OWNER o, AE2.SC_USER u, AE2.SC_LABEL l, AE2.CONTACT c
WHERE o.SC_USER_ID=u.ID
AND USERNAME not like 'Reviewer%%'
AND u.ID != 1
AND o.SC_LABEL_ID = l.ID
AND l.NAME = '%s'
AND u.USEREMAIL = c.EMAIL""" % acc
# print(sql)
return execute_select(sql, db)
def retrieve_arrays_by_exp_acc(exp_acc):
sql = """SELECT distinct(ma.ARRAYACCESSION) as array
FROM AE2.NODE n join study s on s.id=n.STUDY_ID join AE2.MATERIAL ma on n.MATERIAL_ID = ma.ID
where s.acc = '%s'""" % exp_acc
return execute_select(sql, db)
def retrieve_release_date(accession):
sql = "select RELEASEDATE from Study where acc = '%s'" % accession
# print(sql)
return execute_select(sql, db)
def retrieve_study_type_by_accession(accession):
sql = """SELECT * FROM STUDY_TYPE WHERE ACC='%s'""" % accession
return execute_select(sql, db)
def retrieve_xml_by_biosample_id_list(bio_samples_ids):
sql = '''SELECT BIOSAMPLE_ID, xmltype.getStringVal(SAMPLE_XML) as sample_xml from SAMPLE WHERE BIOSAMPLE_ID in (%s)''' % ','.join(
["'%s'" % i for i in bio_samples_ids])
return execute_select(sql, db)
def retrieve_samples_by_submission_acc(submission_acc):
return execute_select(
"""Select * from SAMPLE where SUBMISSION_ID = '%s'""" % submission_acc,
db)
def retrieve_sample_by_acc(acc):
return execute_select(
"""SELECT * FROM SAMPLE WHERE SAMPLE_ID = '%s'""" % acc, db)
def retrieve_alias_by_sample_id(ena_sample):
sql = """SELECT SAMPLE_ALIAS from ERA.SAMPLE where SAMPLE_ID = '%s'""" % ena_sample
print(sql)
return execute_select(sql, db)
def retrieve_arrays_by_exp_acc(exp_acc):
sql = """SELECT distinct(ma.ARRAYACCESSION) as array
FROM AE2.NODE n join study s on s.id=n.STUDY_ID join AE2.MATERIAL ma on n.MATERIAL_ID = ma.ID
where s.acc = '%s'""" % exp_acc
return execute_select(sql, db)
def get_study_type_by_acc(accession):
sql = """select text from AE2.STUDY_ANNOTATIONS a, CONTROLLED_VOCABULARY cv, study s
where ACC='%s' and STUDY_ID = s.id and TYPE_ID = cv.id and name like '%%Type]' """ % accession
return execute_select(sql, db)