def retrieve_daemon_more_than_week_running_tasks(): sql = """SELECT ct.* FROM CONAN_TASKS ct join CONAN_USERS cu on ct.USER_ID = cu.ID where ct.STATE='RUNNING' and cu.LAST_NAME='Daemon' AND START_DATE < TO_DATE('%s', 'YYYY-MM-DD"T"HH24:MI:SS')""" % \ (datetime.datetime.today() - datetime.timedelta(days=7)).isoformat().split('.')[0] print sql exit() return execute_select(sql, db)
def retrieve_job_status(accession): sql_stmt = """SELECT STATE FROM CONAN_TASKS where NAME = '%s' and STATE != 'ABORTED' order by ID desc """ % accession result = execute_select(sql_stmt, db) if result and result[0]: return result[0].state return None
def retrieve_pub(acc, pubmed): sql = """SELECT * FROM PUBLICATION WHERE PUBMEDID ='{pubmed}' OR ACC='{acc}'""".format( acc=str(acc), pubmed=str(pubmed)) # sql = "SELECT * FROM PUBLICATION WHERE PUBMEDID ='{pubmed}'".format(acc=str(acc), pubmed=str(pubmed)) # print db # sql = "SELECT * FROM PUBLICATION WHERE PUBMEDID ='6696735'" return execute_select(sql, db)
def retrieve_ena_accession(acc): sql = """select TEXT from study, study_annotations, controlled_vocabulary where study.id = study_annotations.study_id and study_annotations.type_id = controlled_vocabulary.id and study.acc = '{acc}' and NAME = 'Comment[SecondaryAccession]'""".format( acc=acc) # print sql return execute_select(sql, db)
def retrieve_permission_for_accession(acc): sql = """ select s.releasedate,o.sc_user_id from study s, sc_label l left join sc_owner o on l.id=o.sc_label_id where s.acc='{acc}' and l.name='{acc}' order by sc_user_id""".format(acc=acc) # print sql return execute_select(sql, db)
def get_file_by_run(run_acc): sql = """ SELECT DATA_FILE_PATH FROM DATA_FILE_META WHERE DATA_FILE_OWNER = 'RUN' AND DATA_FILE_OWNER_ID='%s' AND DATA_FILE_FORMAT = 'FASTQ' """ % run_acc return execute_select(sql, db)
def retrieve_today_updated_experiments(update_date=None): if not update_date: update_date = datetime.datetime.today().date().isoformat() sql = """select sa.TEXT , s.ACC, s.TITLE, s.RELEASEDATE, p.DOI, p.PUBMED, p.AUTHORLIST, p.TITLE as publication_title from AE2.STUDY s join AE2.VIEW_PUBLICATIONS p on s.ACC = p.STUDYACC left join (select * from AE2.STUDY_ANNOTATIONS where TYPE_ID='151752' ) sa on s.ID = sa.STUDY_ID where s.LASTUPDATEDATE >= DATE'%s' and s.ACC not like 'E-GEOD-%%' order by sa.TEXT asc""" % update_date return execute_select(sql, db)
def retrieve_study_by_acc(acc): sql = """select title, description from STUDY where ACC = '{acc}'""".format(acc=acc) # print sql res, con = execute_select(sql, db, keep_connection=True) a = 'No Description Available!' # print 'RESSSS: ', res if res and res[0]: a = res[0].description.read() con.close() res[0].description = a return res
def insert_publication(article): id = execute_select('select SEQ_PUBLICATION.nextval*50 as id from dual', db)[0].id sql = """INSERT INTO PUBLICATION ( ID, ACC, DOI, AUTHORLIST, TITLE, EDITOR, ISSUE, PAGES, PUBLICATION, PUBLISHER, URI, VOLUME, PUBMEDID, YEAR ) VALUES( '{id}', '{PUBMED}', '{DOI}', '{AUTHORLIST}', '{TITLE}', '{EDITOR}', '{ISSUE}', '{PAGES}', '{PUBLICATION}', '{PUBLISHER}', '{URI}', '{VOLUME}', '{PUBMED}', '{YEAR}' )""".format( id=str(id), PUBMED=article.get('id', ''), DOI=article.get('doi', None), AUTHORLIST=article.get('authorString', '').replace("'", '').encode('utf8'), TITLE=article.get('title', '').replace("'", '').encode('utf8'), EDITOR=article.get('editor', '').replace("'", '').encode('utf8'), ISSUE=article.get('issue', None), PAGES=article.get('pageInfo', None), PUBLICATION=article.get('journalTitle', '').encode('utf8'), PUBLISHER=article.get('publisher', '').encode('utf8'), URI=article.get('uri', '').encode('utf8'), VOLUME=article.get('journalVolume', ''), YEAR=article.get('pubYear', '')) execute_insert(sql, db) return str(id)
def retrieve_existing_publications_by_accession(acc): sql = """select distinct pubid, pubmed, doi, title, authorlist, publication, publisher, editor, year, volume, issue, pages, uri, studyid from view_publications where studyacc='{acc}'""".format(acc=acc) return execute_select(sql, db)
def retrieve_runs_by_experiment(exp_acc): sql = """SELECT * from ERA.WH_RUN where EXPERIMENT_ID = '%s' """ % exp_acc return execute_select(sql, db)
def retrieve_user_by_email(email): sql = """SELECT ID FROM CONAN_USERS WHERE EMAIL='%s' AND USER_NAME != 'conan-daemon'""" % email return execute_select(sql, db)
def is_array_design_exists(accession): sql = """SELECT * from PLAT_DESIGN WHERE ACC = '%s'""" % accession res = execute_select(sql, db) if res and len(res) > 0: return True return False
def retrieve_samples_by_submission_acc(submission_acc): return execute_select("""Select * from SAMPLE where SUBMISSION_ID = '%s'""" % submission_acc, 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_files_by_run_owner(run_acc): return execute_select("""SELECT * FROM WEBIN_FILE WHERE DATA_FILE_OWNER_ID = '%s' AND DATA_FILE_OWNER = 'RUN'""" % run_acc, db)
def retrieve_sample_by_acc(acc): return execute_select("""SELECT * FROM SAMPLE WHERE SAMPLE_ID = '%s'""" % acc, db)
def retrieve_ena_nodes_relations(ena_acc): sql = """SELECT * from ERA.WH_RUN where STUDY_ID = '%s' """ % ena_acc return execute_select(sql, db)
def retrieve_runs_by_submission_acc(submission_acc): sql = """SELECT * FROM RUN WHERE SUBMISSION_ID = '%s'""" % submission_acc # print sql # exit() return execute_select(sql, db)
def retrieve_run_by_acc(acc): sql = """SELECT * FROM RUN WHERE RUN_ID = '%s'""" % acc return execute_select(sql, db)
def retrieve_study_id_by_run_id(run_id): sql = """SELECT STUDY_ID FROM EXPERIMENT WHERE EXPERIMENT_ID = (SELECT EXPERIMENT_ID FROM RUN WHERE RUN_ID = '%s')""" % run_id return execute_select(sql, db)
def retrieve_files_by_study_id(study_id): sql = """select * from WEBIN_FILE WHERE DATA_FILE_OWNER_ID in (select RUN_ID from RUN where EXPERIMENT_ID in (SELECT EXPERIMENT_ID FROM EXPERIMENT WHERE STUDY_ID = '%s') ) AND DATA_FILE_OWNER = 'RUN'""" % study_id return execute_select(sql, db)
def retrieve_samples_by_study_id(study_id): sql = """select es.experiment_id, s.* from SAMPLE s,EXPERIMENT_SAMPLE es where es.EXPERIMENT_ID in (SELECT EXPERIMENT_ID FROM EXPERIMENT WHERE STUDY_ID = '%s') AND s.SAMPLE_ID = es.SAMPLE_ID""" % study_id return execute_select(sql, db)
def retrieve_runs_by_study_id(study_id): sql = """select * from RUN where EXPERIMENT_ID in (SELECT EXPERIMENT_ID FROM EXPERIMENT WHERE STUDY_ID = '%s')""" % study_id return execute_select(sql, db)
def retrieve_sample_acc_by_exp_acc(exp_acc): return execute_select( """SELECT SAMPLE_ID FROM EXPERIMENT_SAMPLE WHERE EXPERIMENT_ID ='%s'""" % exp_acc, db)
def retrieve_studies_by_accession(acc): sql = """select bp.acc, epv.category, TO_CHAR(epv.term_text) as term_text, ept.category as cat, ept.term_text as txt from BIO_PRODUCT bp, PRODUCT_PV ppv, EXP_PROP_TYPE ept, EXP_PROP_VAL epv where bp.acc = '%s' and bp.ID = ppv.OWNER_ID and ppv.PV_ID = epv.ID and epv.TYPE_ID = ept.ID""" % acc return execute_select(sql, db)
def retrieve_runs_by_experiment_acc(exp_acc): sql = """SELECT * FROM RUN WHERE EXPERIMENT_ID = '%s'""" % exp_acc # print sql # exit() return execute_select(sql, db)
def retrieve_alias_by_sample_id(ena_sample): sql = """SELECT SAMPLE_ALIAS from ERA.SAMPLE where SAMPLE_ID = '%s'""" % ena_sample return execute_select(sql, db)
def retrieve_contact_by_study_id(ae_id): sql = """SELECT * FROM CONTACT WHERE STUDY_ID=%s""" % str(ae_id) # print sql return execute_select(sql, db)
def retrieve_daemon_failed_tasks(): sql = """SELECT ct.* FROM CONAN_TASKS ct join CONAN_USERS cu on ct.USER_ID = cu.ID where ct.STATE='FAILED' and cu.LAST_NAME='Daemon'""" return execute_select(sql, db)