library(rvest)
library(rjson)
library(RMySQL)
##ȡʡ??ַ
url='http://hqb.nxin.com/hqb/queryPigPrice.shtml'
doc=read_html(url)
href1=doc%>%html_nodes('div[class="pt_city"] a')%>%html_attrs()%>%.[1:31]
sheng_name=doc%>%html_nodes('div[class="pt_city"] a')%>%html_text()%>%.[1:31]
sheng_href=c()
for(i in 1:31) sheng_href=c(sheng_href,href1[[i]])
sheng_area_id=gsub('[^0-9]','',sheng_href);names(sheng_area_id)=c()
sheng=data.frame(area=sheng_name,areaid=sheng_area_id,href=sheng_href,stringsAsFactors = F)
#####ȡ?е?ַ
shi=data.frame()
for(jj in 1:31){
URL='http://hqb.nxin.com'
url=paste0(URL,sheng_href[jj])
doc=read_html(url)
href2=doc%>%html_nodes('div[class="pt_city"] a ')%>%html_attrs()
shi_name=doc%>%html_nodes('div[class="pt_city"] a ')%>%html_text()
shi_href=c()
for(i in 1:length(href2)) shi_href=c(shi_href,href2[[i]])
shi_area_id=gsub('[^0-9]','',shi_href);names(shi_area_id)=c()
shi1=data.frame(area=shi_name,areaid=shi_area_id,areapid=rep(sheng_area_id[jj],length(href2)),
areapidname=rep(sheng_name[jj],length(href2)),href=shi_href,stringsAsFactors = FALSE)
shi=rbind(shi,shi1)
}
rm(shi1)
#############################################
shi_todaydata=data.frame()
for(i in 1:363){
URL='http://hqb.nxin.com'
url=paste0(URL,shi$href[i])
doc=read_html(url)
data1=doc%>%html_nodes('table[class="notable t_table"]')%>%html_table() ##
data2=data.frame(data1)
data2$X1=substring(data2$X1,1,23);data2$X2=substring(data2$X2,1,24);data2$X3=substring(data2$X3,1,24);data2$X4=substring(data2$X4,1,17)
data2=gsub('[u4e00-u9fa5]','',data2);data2=gsub(' ','',data2);data2=gsub('
','',data2)
data2=gsub('\(14%\)','',data2);data2=gsub('\(43%\)','',data2);data2=gsub('\(\)','',data2);data2=gsub('\/','',data2);
todaydate=doc%>%html_nodes('div[class="pt_tit"] ')%>%html_text() ##
todaydate=gsub(' ','',todaydate);todaydate=gsub('
','',todaydate) ##
todaydate=substring(todaydate,nchar(todaydate)-7,nchar(todaydate)) ###
todaydate=gsub('\(','',todaydate);todaydate=gsub('\)','',todaydate)
todaydate=gsub('月','-',todaydate);todaydate=gsub('日','',todaydate);todaydate=paste0('2017-',todaydate)
data3=data.frame(area=shi$area[i],areaid=shi$areaid[i],areapid=shi$areapid[i],pigprice=data2[1],cornprice=data2[2],
beanprice=data2[3],date=todaydate)
shi_todaydata=rbind(shi_todaydata,data3)
}
#########
#library(RMySQL)
con=dbConnect(MySQL(),dbname='henan_xumuwang',host='localhost',port=3306,username='root',password='')
dbSendQuery(con,'set names gbk')
dbWriteTable(con,'pigdata_shi',shi_todaydata,append=TRUE,row.names=FALSE)
dbDisconnect(con)