def retrieve_gxa_studies(): sql = """SELECT sub.accNo, l.id as link_id,l.url,l.section_id, la.id as link_att_id, la.name, la.value from Submission sub join Section sec on sub.rootSection_id = sec.id join Link l on l.section_id = sec.id join LinkAttribute la on la.link_id = l.id where sub.accNo like 'E-%' and la.name='Type'""" return execute_select(sql, db=db)
def get_ae_submissions(): # sql = """SELECT s.* FROM SubmissionAttribute sa join Submission s on s.id = sa.submission_id # where name = 'AttachTo' and value = 'ArrayExpress';""" sql = """SELECT s.* FROM Submission_AccessTag sa join Submission s on sa.Submission_id = s.id join AccessTag t on sa.accessTags_id = t.id where t.name = 'ArrayExpress' and s.accNo <> 'ArrayExpress'""" return execute_select(sql, db)
def insert_gxa_link(acc, section_id, link_type, dscr): sql = """INSERT INTO Link (local, tableIndex, url, section_id) values(0, -1, '{acc}', {sec})""".format(acc=acc, sec=section_id) # print(sql) execute_insert(sql, db) sql = """SELECT max(id) as link_id from Link where local = 0 and tableIndex=-1 and url='{acc}' and section_id={sec}""".format( acc=acc, sec=section_id) print(sql) res = execute_select(sql, db) print(res) link_id = res[0]['link_id'] insrt_sql = """INSERT INTO LinkAttribute (name, value, link_id,numValue, reference ) values ('Type', '{link_type}', {link_id}, 0, 0), ('Description', '{dscr}', {link_id}, 0,0)""".format( link_type=link_type, dscr=dscr, link_id=link_id) print(insrt_sql) execute_insert(insrt_sql, db)
def get_root_section_by_accession(acc): sql = """ SELECT * FROM SectionAttribute where section_id in (select rootSection_id from Submission where accNo = '{acc}')""".format( acc=acc) return execute_select(sql, db=db)
def get_attr(acc): sql = """Select * from SectionAttribute sa join Submission s on sa.section_id = s.rootSection_id where s.accNo = '{acc}'""".format(acc=acc) return execute_select(sql, db)
def retrieve_submission_by_acc(acc): sql = """SELECT * FROM Submission WHERE accNo ='{acc}'""".format(acc=acc) # print(sql) return execute_select(sql, db)
def get_subscount_by_userid(user_id): sql = """SELECT count(*) as `count` from Submission where owner_id = {}""".format( user_id) # print(sql) return execute_select(sql, db)