############################################################################################
###################### 行业数据 #############################################
############################################################################################
create table stock_industry_wind
(stock_id varchar(10) -- 股票代码
,DATETIME date -- 数据日期
,STOCK_name varchar(50) -- 公司名
,START_DATE DATE -- 上市日期
,CONCEPT VARCHAR(500) -- 概念板块
,industry_gics VARCHAR(150) -- WIND行业名称 11种分类
,industry_gicscode INT -- WIND行业代码 11种分类
,industry_CSRC12 VARCHAR(150) -- 证监会行业名称 19种分类
)
############################################################################################
############### 循环加载交易数据 ##################
############################################################################################
library(RCurl)
library(RMySQL) # 载入RMySQL包
library(WindR)
w.start()
table_name <- "stock_industry_wind"
flag <- ".SH"
date_dt <- '20170320'
filepath <- "E:/.../数据源/"
security <- readLines(paste(filepath,'沪市代码list.txt',sep = ''))
filename <- paste(filepath,table_name,"_",date_dt,'.txt',sep = '')
code <- vector(length = length(security))
listingdate <- vector(length = length(security))
security.tab <- data.frame(code, listingdate, stringsAsFactors=FALSE)
for (i in 1 : length(security))
{ security.tab[i,] = do.call(cbind,(strsplit(security[i],"\t"))) }
for (i in 1 : length(security))
{
code <- paste(security.tab$code[i],flag,sep="")
cat(i,"\t---",code,"\t---","\n")
w_wsd_data<-w.wss(code,'sec_name,ipo_date,concept,industry_gics,industry_gicscode,industry_CSRC12',tradeDate=date_dt,'industryType=1')
data_df1<-w_wsd_data$Data
data_df1<-cbind(data_df1[,1],'2017-03-20',data_df1[,2:length(data_df1)])
data_df1$IPO_DATE<-as.Date('1899-12-30')+data_df1$IPO_DATE
if (i==1) { data_df2<-data_df1 }
else { data_df2<-rbind(data_df1,data_df2) }
}
conn <- dbConnect(MySQL(),dbname = "gushi",username = "root") #建立数据库连接
dbSendQuery(conn, "SET @@sql_mode=ANSI;") # 启动非严格模式
dbSendQuery(conn, "SET NAMES GBK") # win7环境下如果汉字乱码，就运行这条命令
write.table(data_df2,file=filename,sep="\t",quote=FALSE,row.name=FALSE, col.names = FALSE)
strSQL <- paste('load data infile \'',filename,'\' into table ',table_name,' character set GBK fields terminated by \'\t\' lines terminated by \'\n\'',sep="")
res1 <-dbSendQuery(conn, strSQL)
#dbWriteTable(conn,table_name,data_df1,append=T,row.names=F) ## 存在中文的话会不显示
#dbReadTable(conn,table_name)
dbDisconnect(conn)
