R科研作图学习小组

http://group.keyangou.com/RGraph
组长: 管理员:
  • 访问次数:25983
  • 小组等级:9
  • 话题:593
  • 回答:39
  • 签到:429
  • 小组排名:
  • R2-27-第二阶段第二次作业

    whk 发布于:2018.03.06

    任务一

    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)

      }

    }

    1.png

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

     
    1条评论 174浏览 邀请回答

    木萱小主 回答于:2018年03月07日 09:01:071楼

    北京 北京协和医学院 微生物与生化药学博士 

    都导入了吧,以后要用


    小组告示

    科研狗 2012-2016 京ICP备16006621 科研好助手,专业的科研社交共享平台