任务1

con <- dbConnect(MySQL(),host="localhost",dbname="rdb",user="root",password="")

t_demo<-data.frame(a=seq(1:10), b=letters[1:10], c=rnorm(10))

t_demo

dbWriteTable(con, "t_demo", t_demo)

任务2

library(RISmed)

cell2017<-EUtilsSummary("cell[TA] AND 2017[DP]")

 data<-QueryId(cell2017)

 summary(cell2017)

pmid<- as.numeric(data)

 mylist<- list(id=1:562,pmid=pmid)

 mylist=as.data.frame(mylist)

dbWriteTable(con,"article",mylist,append=T,row.names=F)

任务3

library(RMySQL)

library(xml2)

library(httr)

killDbConnections()

con <- dbConnect(MySQL(),host="localhost",dbname="rdb",user="root",password="")

dbSendQuery(con,'SET NAMES utf8')

on.exit(dbDisconnect(con))

rs <- dbSendQuery(con, "SELECT * FROM article WHERE isdone=0")

while (!dbHasCompleted(rs)) {

  chunk <- dbFetch(rs, 10)

mode(chunk)

  print(chunk)

pmidStr=""

 i=1

  n=nrow(chunk)

while (i<=n){

pmidStr = paste(pmidStr,chunk[i,3],sep=",")

    i = i + 1

 }

pmidStr=substr(pmidStr,2,100000)

postFetchUrl='https://eutils.ncbi.nlm.nih.gov/entrez/eutils/efetch.fcgi'

 r2 <- POST(postFetchUrl,

             body = list(

               db='pubmed',

               id=pmidStr,

               retmode='xml'

             )

  )

  stop_for_status(r2)

data2=content(r2, "parsed", "application/xml")

  article=xml_children(data2)

count=length(article)

  cnt=1

  while(cnt<=count){

title=xml_text(xml_find_first(article[cnt],".//ArticleTitle"))

abstract=xml_text(xml_find_first(article[cnt],".//AbstractText"))

 pmid=xml_text(xml_find_first(article[cnt],".//PMID"))

title = gsub("'","",title)

 abstract = gsub("'","",abstract)

sql=paste("UPDATE article SET title='",title,"',abstract='",abstract,"',isdone=1"," where pmid='",pmid,"'",sep="")

con2 <- dbConnect(MySQL(),host="localhost",dbname="rdb",user="root",password="")

    dbSendQuery(con2,'SET NAMES utf8')

    dbSendQuery(con2,sql)

    dbDisconnect(con2)

cnt = cnt + 1

Sys.sleep(1)

}

}