任务一

library(RMySQL)
con <- dbConnect(MySQL(),host="localhost",dbname="rdb",user="root",password="自己设的密码")
dbSendQuery(con,'SET NAMES utf8')

任务二

library(RMySQL)
con <- dbConnect(MySQL(),host="localhost",dbname="rdb",user="root",password="自己设的密码")
dbSendQuery(con,'SET NAMES utf8')
library(httr)
 totalNum=562
 pageSize=10
totalPage=ceiling(totalNum/pageSize)
 currentPage=1
term='(cell[TA]) AND 2017[DP]'
 usehistory='Y'
 querykey=''
webenv=''
postSearchUrl='https://eutils.ncbi.nlm.nih.gov/entrez/eutils/esearch.fcgi'
while(currentPage<=totalPage){retstart=(currentPage-1)*pageSize
r <- POST(postSearchUrl, body = list(db='pubmed',term=term,retmode='json',retstart=retstart,retmax=pageSize, usehistory=usehistory,rettype='uilist'))
stop_for_status(r)
data=content(r, "parsed", "application/json")
esearchresult=data$esearchresult
querykey=esearchresult$querykey
webenv=esearchresult$webenv
idlist =esearchresult$idlist
  n = length(idlist)
pmid=c()
i = 1
  while(i<=n){pmid=c(pmid, as.character(idlist[i][1]))
i = i+1
}
article=data.frame('pmid'=pmid)
dbWriteTable(con,"article",article,append=TRUE)
  currentPage = currentPage + 1
}
dbDisconnect(con)

任务三

 library(RMySQL)
 library(xml2)
 library(httr)
killDbConnections()
list()
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)
 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)
  }
 }