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)