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 insert_publication_view(acc, article): sql = """INSERT INTO VIEW_PUBLICATIONS (STUDYACC, PUBMED, DOI, AUTHORLIST, TITLE, ISSUE, PAGES, PUBLICATION, VOLUME, YEAR ) VALUES( '{STUDYACC}', '{PUBMED}', '{DOI}', '{AUTHORLIST}', '{TITLE}', '{ISSUE}', '{PAGES}', '{PUBLICATION}', '{VOLUME}', '{YEAR}' )""".format( STUDYACC=acc, PUBMED=article.get('pmid', ''), DOI=article.get('doi', None), AUTHORLIST=article.get('authorString', '').replace("'", '').encode('utf8'), TITLE=article.get('title', '').replace("'", '').encode('utf8'), ISSUE=article.get('issue', None), PAGES=article.get('pageInfo', None), PUBLICATION=article.get('journalTitle', '').encode('utf8'), VOLUME=article.get('journalVolume', ''), YEAR=article.get('pubYear', '')) # print sql execute_insert(sql, db)
def update_publication_view_by_pubid(pub_id, article): sql = u"""UPDATE VIEW_PUBLICATIONS SET PUBMED ='{PUBMED}' , DOI='{DOI}', AUTHORLIST='{AUTHORLIST}', TITLE='{TITLE}', ISSUE='{ISSUE}', PAGES='{PAGES}', PUBLICATION='{PUBLICATION}', VOLUME='{VOLUME}', YEAR='{YEAR}' WHERE PUBID = {PUBID}""".format( PUBMED=article.get('pmid', ''), DOI=article.get('doi', None), AUTHORLIST=article.get('authorString', '').replace("'", ''), TITLE=article.get('title', '').replace("'", ''), ISSUE=article.get('issue', None), PAGES=article.get('pageInfo', None), PUBLICATION=article.get('journalTitle', ''), VOLUME=article.get('journalVolume', ''), YEAR=article.get('pubYear', ''), PUBID=str(pub_id)) execute_insert(sql, db)
def delete_publication_by_id(pub_id): sql = """DELETE FROM PUBLICATION WHERE ID = %s""" % str(pub_id) execute_insert(sql, db)
def insert_study_publication(study_id, pub_id): sql = """insert into STUDY_PUBLICATION (study_id, publication_id) values ({study_id}, {pub_id})""".format(study_id=study_id, pub_id=pub_id) # print sql execute_insert(sql, db)
def delete_study_publication(study_id, pub_id): sql = '''DELETE FROM STUDY_PUBLICATION WHERE STUDY_ID =%s AND PUBLICATION_ID = %s''' % ( str(study_id), str(pub_id)) # print sql execute_insert(sql, db)
def update_task_status_by_id(id, status, status_message): sql = """UPDATE CONAN_TASKS SET STATE = '%s', STATUS_MESSAGE='%s' WHERE ID = %s""" % ( status, status_message, str(id)) execute_insert(sql, db)