任务一

library(DBI)

library(RMySQL)

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

dbSendQuery(con,'SET NAMES utf8')

dbGetInfo(con)  

dbListTables(con)  

dbRemoveTable(con,"test")

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

test

dbWriteTable(con,"test",test,overwrite=TRUE)

dbRemoveTable(con,"test")

killDbConnections <- function () {

  all_cons <- dbListConnections(MySQL())

  print(all_cons)

  for(con in all_cons)

    +  dbDisconnect(con)

  print(paste(length(all_cons), " connections killed."))

}

    a b          c
1   1 a -0.7986066
2   2 b  2.1112306
3   3 c -1.1693077
4   4 d  1.4097341
5   5 e  1.1031356
6   6 f -0.4183949
7   7 g -1.0658300
8   8 h -1.7372615
9   9 i -0.8308506
10 10 j -1.2501171

任务二

killDbConnections()

library(RMySQL)

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

dbSendQuery(con,'SET NAMES utf8')

library(httr)

totalNum=563 

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) #clear http status

  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)

append=TRUE

  dbWriteTable(con,"article",article,append=TRUE) 

  currentPage = currentPage + 1 

}

dbDisconnect(con)

任务三

library(RMySQL)

library(xml2)

library(httr)

killDbConnections()

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

dbSendQuery(con,'SET NAMES utf8')

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) #clear http status

  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)

  }

}

大概因为网不好所以就没有出结果