def __init__(self): self.wsetData = [ "000001.SH", "399300.SZ", "000016.SH", "000905.SH", "000906.SH" ] # 要获取数据的证券代码 self.indexFieldName = [ "open", "high", "low", "close", "volume", "amt", "chg", "pct_chg", "turn" ] # 要获取的数据字段 self.fundFieldName = ["nav", "NAV_acc", "sec_name"] self.monetaryFund = [ "mmf_annualizedyield", "mmf_unityield", "sec_name" ] self.stockFieldName = [ "open", "high", "low", "close", "volume", "amt", "turn", "mkt_cap_ard", "pe_ttm", "ps_ttm", "pb_lf" ] self.engine = MysqlCon().getMysqlCon(flag='engine') self.conn = MysqlCon().getMysqlCon(flag='connect') self.GetDataToMysqlDemo = GetDataToMysql() self.logger = mylog.logger # w.start() log_state = THS_iFinDLogin('zszq5072', '754628') if log_state == 0: self.logger.info("同花顺账号登录成功!") else: self.logger.error("同花顺账号登录异常,请检查!") return
class GetDataToMysql: def __init__(self): self.conn = MysqlCon().getMysqlCon(flag='connect') self.logger = mylog.logger def GetMain(self, dataDf, tableName): # 插入数据语句 tableList = dataDf.columns.tolist() strFormat = '%s,' * len(tableList) sqlStr = "replace into %s(%s)" % ( tableName, ','.join(tableList)) + "VALUES(%s)" % strFormat[:-1] # dataDf.fillna('None',inplace=True) dataDf = dataDf.astype(object).where(pd.notnull(dataDf), None) # dataDf.where(dataDf.notnull(), None) # dataDf.where(dataDf.notnull(), None) cursor = self.conn.cursor() try: for r in range(0, len(dataDf)): values = tuple(dataDf.iloc[r][tableList].tolist()) cursor.execute(sqlStr, values) except: self.logger.error("插入数据到数据库错误,请检查!") cursor.close() self.conn.commit()
class GetDataToMysql: def __init__(self): self.conn = MysqlCon().getMysqlCon(flag='connect') self.logger = mylog.set_log() def GetMain( self, dataDf, tableName, ): # 插入数据语句 tableList = dataDf.columns.tolist() strFormat = '%s,' * len(tableList) sqlStr = "replace into %s(%s)" % ( tableName, ','.join(tableList)) + "VALUES(%s)" % strFormat[:-1] dataDf = dataDf.astype(object).where(pd.notnull(dataDf), None) cursor = self.conn.cursor() for r in range(0, len(dataDf)): values = tuple(dataDf.iloc[r][tableList].tolist()) cursor.execute(sqlStr, values) cursor.close() self.conn.commit() self.logger.info("数据存入mysql成功!")
def __init__(self, data_resource='ifind'): self.logger = mylog.set_log() self.dic_init = {} self.dic_init['data_resource'] = data_resource self.dic_init['data_init_flag'] = self.log_init(data_resource) mysql_con_demo = MysqlCon() self.engine = mysql_con_demo.getMysqlCon(flag='engine') self.conn = mysql_con_demo.getMysqlCon(flag='connect') self.GetDataToMysqlDemo = GetDataToMysql()
def __init__(self): self.start_date = '2020-07-01' self.end_date = '2020-07-31' self.file_path = r"D:\\工作文件\\指数基金月报\\培训PPT\\0821\\" MysqlConDemo = MysqlCon() self.engine = MysqlConDemo.getMysqlCon('engine') self.name_dic = { 'fund_code': '基金代码', 'fund_type': '基金类型', 'product_type': '产品类型', 'fund_name': '基金名称', 'establish_date': '基金成立日', 'indx_sname': '跟踪指数', 'class_classify': '跟踪指数类型', 'index_code': '跟踪指数代码' } self.logger = mylog.set_log()
def __init__(self): self.wsetData = [ "000001.SH", "399300.SZ", "000016.SH", "000905.SH", "000906.SH" ] # 要获取数据的证券代码 self.indexFieldName = [ "open", "high", "low", "close", "volume", "amt", "chg", "pct_chg", "turn" ] # 要获取的数据字段 self.fundFieldName = ["nav", "NAV_acc", "sec_name"] self.monetaryFund = [ "mmf_annualizedyield", "mmf_unityield", "sec_name" ] self.stockFieldName = [ "open", "high", "low", "close", "volume", "amt", "turn", "mkt_cap_ard", "pe_ttm", "ps_ttm", "pb_lf" ] self.engine = MysqlCon().getMysqlCon(flag='engine') self.conn = MysqlCon().getMysqlCon(flag='connect') self.GetDataToMysqlDemo = GetDataToMysql() self.logger = mylog.logger
def __init__(self): self.logger = mylog.set_log() mysql_con_demo = MysqlCon() self.engine = mysql_con_demo.getMysqlCon(flag='engine') w.start()
def __init__(self): MysqlConDemo = MysqlCon() self.engine = MysqlConDemo.getMysqlCon('engine')
class GetDataFromWindAndMySql: def __init__(self): self.wsetData = [ "000001.SH", "399300.SZ", "000016.SH", "000905.SH", "000906.SH" ] # 要获取数据的证券代码 self.indexFieldName = [ "open", "high", "low", "close", "volume", "amt", "chg", "pct_chg", "turn" ] # 要获取的数据字段 self.fundFieldName = ["nav", "NAV_acc", "sec_name"] self.monetaryFund = [ "mmf_annualizedyield", "mmf_unityield", "sec_name" ] self.stockFieldName = [ "open", "high", "low", "close", "volume", "amt", "turn", "mkt_cap_ard", "pe_ttm", "ps_ttm", "pb_lf" ] self.engine = MysqlCon().getMysqlCon(flag='engine') self.conn = MysqlCon().getMysqlCon(flag='connect') self.GetDataToMysqlDemo = GetDataToMysql() self.logger = mylog.logger # w.start() log_state = THS_iFinDLogin('zszq5072', '754628') if log_state == 0: self.logger.info("同花顺账号登录成功!") else: self.logger.error("同花顺账号登录异常,请检查!") return def getStockMonthToMySql(self): start_date = '2011-11-01' end_date = '2019-12-31' total_trade_list = self.getTradeDay(startDate=start_date, endDate=end_date, Period='M') wsetdata = w.wset( "sectorconstituent", "date=%s;sectorid=a001010100000000" % total_trade_list[0]) if wsetdata.ErrorCode != 0: self.logger.debug("获取全A股数据有误,错误代码" + str(wsetdata.ErrorCode)) index_df = pd.DataFrame(wsetdata.Data, index=wsetdata.Fields, columns=wsetdata.Codes).T if index_df.empty: return for trade_date in total_trade_list: optionstr = "tradeDate=%s;cycle=M" % ( trade_date[:4] + trade_date[5:7] + trade_date[8:]) wssdata = w.wss( codes=index_df['wind_code'].tolist(), fields=["mkt_freeshares", "pe_ttm", "ps_ttm", "pct_chg"], options=optionstr) if wssdata.ErrorCode != 0: self.logger.debug("获取因子数据有误,错误代码" + str(wssdata.ErrorCode)) return pd.DataFrame() resultDf = pd.DataFrame(wssdata.Data, index=wssdata.Fields, columns=wssdata.Codes).T df_list = [] for col in resultDf: temp_df = pd.DataFrame(resultDf[col].values, index=resultDf.index, columns=['factor_value']) temp_df['update_time'] = trade_date temp_df['stock_code'] = resultDf.index.tolist() temp_df['factor_name'] = col df_list.append(temp_df) total_fa_df = pd.concat(df_list, axis=0, sort=True) self.GetDataToMysqlDemo.GetMain(total_fa_df, 'stock_factor_month_value') self.logger.info("存储日期%s因子数据成功!" % trade_date) def getFactorValue(self, code_list=[], factor_list=[], start_date='2019-04-01', end_date='2019-05-01'): # 获取截面因子数据 sqlStr = "select * from stock_factor_month_value where stock_code in %s and factor_name in %s and update_time='%s'" % ( str(tuple(code_list)), str(tuple(factor_list)), start_date) resultDf = pd.read_sql(sql=sqlStr, con=self.engine) if resultDf.empty: dateFormat = start_date[:4] + start_date[5:7] + start_date[8:] wssdata = w.wss(codes=code_list, fields=factor_list, options="unit=1;tradeDate=%s" % dateFormat) if wssdata.ErrorCode != 0: self.logger.debug("获取因子数据有误,错误代码" + str(wssdata.ErrorCode)) return pd.DataFrame() resultDf = pd.DataFrame(wssdata.Data, index=wssdata.Fields, columns=wssdata.Codes).T else: df_list = [] for factor, temp_df in resultDf.groupby(by='factor_name'): temp = pd.DataFrame(temp_df['factor_value'].values, index=temp_df['stock_code'], columns=[factor]) df_list.append(temp) resultDf = pd.concat(df_list, sort=True, axis=1) return resultDf return resultDf def getIndexConstituent(self, indexCode='000300.SH', getDate='2019-06-06'): ''' 获取指数成分股 :param indexCode: :param getDate: :return: ''' if indexCode != '全A股': sqlStr = "select * from index_constituent where index_code='%s' and update_time='%s'" % ( indexCode, getDate) resultDf = pd.read_sql(sql=sqlStr, con=self.engine) if resultDf.empty: wsetdata = w.wset("indexconstituent", "date=%s;windcode=%s" % (getDate, indexCode)) if wsetdata.ErrorCode != 0: self.logger.error("获取指数成分股数据有误,错误代码" + str(wsetdata.ErrorCode)) return pd.DataFrame() resultDf = pd.DataFrame(wsetdata.Data, index=wsetdata.Fields).T if resultDf.empty: wsetdata = w.wset( "sectorconstituent", "date=%s;windcode=%s" % (getDate, indexCode)) if wsetdata.ErrorCode != 0: self.logger.error("获取板块指数成分股数据有误,错误代码" + str(wsetdata.ErrorCode)) return pd.DataFrame() resultDf = pd.DataFrame(wsetdata.Data, index=wsetdata.Fields).T if resultDf.empty: self.logger.info("指定日期内,未找到有效成分股数据") return resultDf dateList = [ datetampStr.strftime('%Y-%m-%d') for datetampStr in resultDf['date'].tolist() ] resultDf['date'] = dateList nameDic = { 'date': 'adjust_time', 'wind_code': 'stock_code', "sec_name": 'stock_name', 'i_weight': 'stock_weight' } resultDf.rename(columns=nameDic, inplace=True) resultDf['update_time'] = getDate resultDf['index_code'] = indexCode if 'industry' in resultDf: resultDf.drop(labels='industry', inplace=True, axis=1) self.GetDataToMysqlDemo.GetMain(resultDf, 'index_constituent') else: sqlStr = "select distinct stock_code from stock_factor_month_value where update_time='%s'" % getDate resultDf = pd.read_sql(sql=sqlStr, con=self.engine) return resultDf def getLackDataToMySql(self, tempCode, startDate, endDate): tableStr = 'index_value' codeName = 'index_code' sqlStr = "select max(update_time),min(update_time) from %s where %s='%s'" % ( tableStr, codeName, tempCode) cursor = self.conn.cursor() cursor.execute(sqlStr) dateStrTuple = cursor.fetchall()[0] maxDate = dateStrTuple[0] minDate = dateStrTuple[1] if not maxDate: self.get_hq_data_from_ths(tempCode, startDate=startDate, endDate=endDate) return if endDate < minDate or startDate > minDate: self.get_hq_data_from_ths(tempCode, startDate=startDate, endDate=endDate) elif startDate <= minDate: if minDate <= endDate < maxDate: if startDate != minDate: self.get_hq_data_from_ths(tempCode, startDate=startDate, endDate=minDate) elif endDate >= maxDate: self.get_hq_data_from_ths(tempCode, startDate=startDate, endDate=minDate) if endDate != maxDate: self.get_hq_data_from_ths(tempCode, startDate=maxDate, endDate=endDate) elif endDate > maxDate: self.get_hq_data_from_ths(tempCode, startDate=maxDate, endDate=endDate) def getDataFromMySql(self, tempCode, startDate, endDate, tableFlag='index', nameList=['close_price']): if not nameList: self.logger.error('传入获取指数的字段不合法,请检查!') if tableFlag == 'index': tableStr = 'index_value' codeName = 'index_code' elif tableFlag == 'fund': codeName = 'fund_code' tableStr = 'fund_net_value' elif tableFlag == 'stock': codeName = 'stock_code' tableStr = 'stock_hq_value' elif tableFlag == 'private': codeName = 'fund_code' tableStr = 'private_net_value' elif tableFlag == 'monetary_fund': codeName = 'fund_code' tableStr = 'monetary_fund' sqlStr = "select %s,update_time from %s where %s='%s' and update_time>='%s'" \ " and update_time<='%s'" % (','.join(nameList), tableStr, codeName, tempCode, startDate, endDate) resultDf = pd.read_sql(sql=sqlStr, con=self.engine) resultDf = resultDf.drop_duplicates('update_time').sort_index() resultDf.set_index(keys='update_time', inplace=True, drop=True) return resultDf def getCurrentNameData(self, tempCodeList, startDate, endDate, tableFlag='stock', nameStr='close_price'): ''' 获取指定字段的数据 ''' if tableFlag == 'stock': totalCodeStr = '' for stockCode in tempCodeList: totalCodeStr = totalCodeStr + stockCode + "','" sqlStr1 = "select max(update_time),min(update_time) from stock_hq_value where stock_code in ('%s')" % totalCodeStr[: -3] cursor = self.conn.cursor() cursor.execute(sqlStr1) dateStrTuple = cursor.fetchall()[0] maxDate = dateStrTuple[0] minDate = dateStrTuple[1] if not maxDate: for tempCode in tempCodeList: self.getDataFromWind(tempCode, startDate=startDate, endDate=endDate, tableFlag=tableFlag) return else: if endDate < minDate or startDate > minDate: for tempCode in tempCodeList: self.getDataFromWind(tempCode, startDate=startDate, endDate=endDate, tableFlag=tableFlag) elif startDate <= minDate: if minDate <= endDate < maxDate: for tempCode in tempCodeList: self.getDataFromWind(tempCode, startDate=startDate, endDate=minDate, tableFlag=tableFlag) elif endDate >= maxDate: for tempCode in tempCodeList: self.getDataFromWind(tempCode, startDate=startDate, endDate=minDate, tableFlag=tableFlag) self.getDataFromWind(tempCode, startDate=maxDate, endDate=endDate, tableFlag=tableFlag) elif endDate >= maxDate: for tempCode in tempCodeList: self.getDataFromWind(tempCode, startDate=maxDate, endDate=endDate, tableFlag=tableFlag) sqlStr = "select %s,update_time,stock_code from stock_hq_value where stock_code in ('%s') and update_time<='%s' " \ "and update_time>='%s'" % (nameStr, totalCodeStr, endDate, startDate) resultDf = pd.read_sql(sql=sqlStr, con=self.engine) dfList = [] for code, tempDf in resultDf.groupby('stock_code'): df = pd.DataFrame(tempDf[nameStr].values, index=tempDf['update_time'], columns=[code]) dfList.append(df) resultDf = pd.concat(dfList, axis=1) return resultDf def getCurrentDateData(self, tempCodeList, getDate, tableFlag='stock', nameList=['close_price']): ''' 获取指定日期的截面数据 :return: ''' if tableFlag == 'stock': totalCodeStr = "" for stockCode in tempCodeList: totalCodeStr = totalCodeStr + stockCode + "','" sqlStr = "select * from stock_hq_value where stock_code in ('%s') and update_time='%s'" % ( totalCodeStr[:-3], getDate) resultDf = pd.read_sql(sql=sqlStr, con=self.engine) if resultDf.empty: codes = tempCodeList fields = self.stockFieldName tradeDate = getDate wssData = w.wss(codes=codes, fields=fields, options="tradeDate=%s;priceAdj=F;cycle=D" % tradeDate) if wssData.ErrorCode != 0: self.logger.error("获取行情数据有误,错误代码" + str(wssData.ErrorCode)) return pd.DataFrame() tempDf = pd.DataFrame(wssData.Data, index=fields, columns=codes).T tempDf.dropna(inplace=True) if tempDf.empty: self.logger.error("当前日期%s无行情" % getDate) return pd.DataFrame() tempDf['update_time'] = getDate nameDic = { "open": "open_price", "high": "high_price", "low": "low_price", "close": "close_price", "mkt_cap_ard": "market_value", } tempDf.rename(columns=nameDic, inplace=True) tempDf['stock_code'] = tempDf.index.tolist() self.GetDataToMysqlDemo.GetMain(tempDf, 'stock_hq_value') returnDf = tempDf[nameList] return returnDf else: resultDf.set_index('stock_code', drop=True, inplace=True) returnDf = resultDf[nameList] return returnDf def getFirstDayData(self, codeList, tableFlag='fund'): if tableFlag == 'fund': wssData = w.wss(codes=codeList, fields=["fund_setupDate"]) if wssData.ErrorCode != 0: self.logger.error("getFirstDayData获取wind数据错误,错误代码%s" % wssData.ErrorCode) return pd.DataFrame() self.logger.debug("getFirstDayData获取wind数据成功!") resultDf = pd.DataFrame(wssData.Data, columns=wssData.Codes, index=wssData.Fields) return resultDf def get_hq_data_from_ths( self, tempCode, startDate='2019-04-01', endDate='2019-04-30', ): ths_data = THS_HistoryQuotes( thscode=tempCode, jsonIndicator= 'open,high,low,close,volume,changeRatio,turnoverRatio,change', jsonparam= 'Interval:D,CPS:6,baseDate:1900-01-01,Currency:YSHB,fill:Previous', begintime=startDate, endtime=endDate, outflag=True) if ths_data['errorcode'] != 0: self.logger.error("同花顺获取行情数据错误,请检查:%s" % ths_data['errmsg']) return tempDf = THS_Trans2DataFrame(ths_data) tempDf.dropna(how='all', inplace=True) tempDf[codeName] = tempCode tempDf['update_time'] = tempDf.index.tolist() tempDf.rename(columns=nameDic, inplace=True) dateList = [ dateStr.strftime("%Y-%m-%d") for dateStr in tempDf['update_time'].tolist() ] tempDf['update_time'] = dateList self.GetDataToMysqlDemo.GetMain(tempDf, 'index_value') return tempDf def getHQData(self, tempCode, startDate='2019-03-01', endDate='2019-05-30', tableFlag='index', nameList=['close_price']): ''' #获取指数行情数据入口 ''' self.getLackDataToMySql(tempCode, startDate, endDate, tableFlag) resultDf = self.getDataFromMySql(tempCode, startDate, endDate, tableFlag=tableFlag, nameList=nameList) return resultDf def getTradeDay(self, startDate, endDate, Period='M'): ''' 获取指定周期交易日,封装wind接口 :param Period: ''日,W周,M月,Q季,S半年,Y年 :return: ''' # w.start() data = w.tdays(beginTime=startDate, endTime=endDate, options="Period=%s" % Period) if data.ErrorCode != 0: self.logger.error('wind获取交易日期错误,请检查!') return tradeDayList = data.Data[0] tradeDayList = [ tradeDay.strftime('%Y-%m-%d') for tradeDay in tradeDayList ] # w.close() return tradeDayList
class GetDataFromWindAndMySql: def __init__(self): self.wsetData = [ "000001.SH", "399300.SZ", "000016.SH", "000905.SH", "000906.SH" ] # 要获取数据的证券代码 self.indexFieldName = [ "open", "high", "low", "close", "volume", "amt", "chg", "pct_chg", "turn" ] # 要获取的数据字段 self.fundFieldName = ["nav", "NAV_acc", "sec_name"] self.monetaryFund = [ "mmf_annualizedyield", "mmf_unityield", "sec_name" ] self.stockFieldName = [ "open", "high", "low", "close", "volume", "amt", "turn", "mkt_cap_ard", "pe_ttm", "ps_ttm", "pb_lf" ] self.engine = MysqlCon().getMysqlCon(flag='engine') self.conn = MysqlCon().getMysqlCon(flag='connect') self.GetDataToMysqlDemo = GetDataToMysql() self.logger = mylog.logger def getBelongIndustry(self, codeList, tradeDate='2018-12-31'): ''' 获取股票所属的行业 注:查询所属行业前,应确保查询日期大于股票ipo日期,该逻辑不在本方法验证。 :return: ''' sqlStr = "select * from stock_industry_value where stock_code in %s and update_time='%s'" % ( tuple(codeList), tradeDate) resultDf = pd.read_sql(sql=sqlStr, con=self.engine) if resultDf.empty: self.logger.debug("getBelongIndustry从wind获取!") tradeDateParam = tradeDate[:4] + tradeDate[5:7] + tradeDate[8:] wssData = w.wss(codes=codeList, fields=["industry_citic"], options="tradeDate=%s;industryType=1" % tradeDateParam) if wssData.ErrorCode != 0: self.logger.error("获取指数成分股数据有误,错误代码" + str(wssData.ErrorCode)) return pd.DataFrame() df = pd.DataFrame(wssData.Data, index=wssData.Fields, columns=wssData.Codes).T df.rename(columns={"INDUSTRY_CITIC": "industry_name"}, inplace=True) df['stock_code'] = df.index.tolist() df['update_time'] = tradeDate df['industry_wind_code'] = ["industry_citic"] * df.shape[0] df['industry_flag'] = [1] * df.shape[0] self.GetDataToMysqlDemo.GetMain(df, 'stock_industry_value') resultDf = df[['stock_code', 'industry_name']] else: self.logger.debug("getBelongIndustry从本地数据库获取!") resultDf = resultDf[['stock_code', 'industry_name']] return resultDf def getFactorReportData(self, codeList, factors, rptDate='2018-12-31', backYears=0): # 单个年报数据的获取 sqlStr = "select stock_code,item_value from stock_factor_value where stock_code in %s and update_time='%s' and item_wind_code='%s' " \ % (tuple(codeList), rptDate, factors[0]) resultDf = pd.read_sql(sql=sqlStr, con=self.engine) if resultDf.empty: self.logger.debug("getFactorReportData从wind数据库获取%s!" % factors[0]) rptDateParam = rptDate[:4] + rptDate[5:7] + rptDate[8:] if backYears == 0: if "wgsd_assets" not in factors: wssData = w.wss(codes=codeList, fields=factors, options="rptDate=%s" % rptDateParam) else: wssData = w.wss( codes=codeList, fields=factors, options="unit=1;rptDate=%s;rptType=1;currencyType=" % rptDateParam) else: wssData = w.wss(codes=codeList, fields=factors, options="rptDate=%s;N=%s" % (rptDateParam, str(backYears))) if wssData.ErrorCode != 0: self.logger.error("getFactorDailyData获取%s有误,错误代码%s" % (factors, str(wssData.ErrorCode))) return pd.DataFrame() df = pd.DataFrame(wssData.Data, columns=wssData.Codes, index=factors).T resultDf = df.copy() df['stock_code'] = df.index.tolist() df['update_time'] = rptDate df.rename(columns={factors: "item_value"}, inplace=True) df['item_wind_code'] = factors[0] df['rpt_flag'] = 1 self.GetDataToMysqlDemo.GetMain(df, 'stock_factor_value') else: self.logger.debug("getFactorReportData从本地数据库获取%s!" % factors[0]) resultDf = resultDf.set_index( "stock_code", drop=True).rename(columns={"item_value": factors[0]}) return resultDf def getPetChg(self, codeList, startDate, endDate): ''' 获取股票区间涨跌幅数据 :return: ''' sqlStr = "select stock_code,pct_chg_value from stock_range_updown_value where stock_code in %s and start_date='%s' and end_date='%s'" % ( tuple(codeList), startDate, endDate) resultDf = pd.read_sql(sql=sqlStr, con=self.engine) if not resultDf.empty: lackCode = [ code for code in codeList if code not in resultDf['stock_code'].tolist() ] else: lackCode = codeList[:] if lackCode: self.logger.debug("getPetChg从wind获取!") startDateParam = startDate[:4] + startDate[5:7] + startDate[8:] endDateParam = endDate[:4] + endDate[5:7] + endDate[8:] wssData = w.wss(codes=lackCode, fields=["pct_chg_per"], options="startDate=%s;endDate=%s" % (startDateParam, endDateParam)) if wssData.ErrorCode != 0: self.logger.error("getPetChg获取pct_chg_per有误,错误代码%s" % (str(wssData.ErrorCode))) return pd.DataFrame() df = pd.DataFrame(wssData.Data, index=["pct_chg_value"], columns=wssData.Codes).T df['stock_code'] = df.index.tolist() df['start_date'] = startDate df['end_date'] = endDate self.GetDataToMysqlDemo.GetMain(df, 'stock_range_updown_value') resultDf = pd.concat([resultDf, df], axis=0, sort=True)[['stock_code', 'pct_chg_value']] else: self.logger.debug("getPetChg从本地数据库获取!") resultDf.set_index('stock_code', inplace=True, drop=True) return resultDf def getFactorDailyData(self, codeList, factors, tradeDate='2018-12-31'): # 单个非年报数据的获取 sqlStr = "select stock_code,item_value from stock_factor_value where stock_code in %s and update_time='%s' and item_wind_code='%s' " \ % (tuple(codeList), tradeDate, factors[0]) resultDf = pd.read_sql(sql=sqlStr, con=self.engine) if resultDf.empty: self.logger.debug("getFactorDailyData从wind获取%s!" % factors[0]) tradeDateParam = tradeDate[:4] + tradeDate[5:7] + tradeDate[8:] if 'mkt_cap_float' not in factors: wssData = w.wss(codes=codeList, fields=factors, options="tradeDate=%s" % tradeDateParam) else: wssData = w.wss(codes=codeList, fields=factors, options="unit=1;tradeDate=%s;currencyType=" % tradeDateParam) if wssData.ErrorCode != 0: self.logger.error("getFactorDailyData获取%s有误,错误代码%s" % (factors, str(wssData.ErrorCode))) return pd.DataFrame() df = pd.DataFrame(wssData.Data, index=factors, columns=wssData.Codes).T resultDf = df.copy() df['stock_code'] = df.index.tolist() df['update_time'] = tradeDate df.rename(columns={factors[0]: "item_value"}, inplace=True) df['item_wind_code'] = factors[0] df['rpt_flag'] = 0 self.GetDataToMysqlDemo.GetMain(df, 'stock_factor_value') else: self.logger.debug("getFactorDailyData从本地数据库获取%s!" % factors[0]) resultDf = resultDf.set_index( "stock_code", drop=True).rename(columns={"item_value": factors[0]}) return resultDf def getIndexConstituent(self, indexCode='000300.SH', getDate='2019-06-06', indexOrSector='index'): ''' 获取指数成分股 ''' sqlStr = "select * from index_constituent where index_code='%s' and update_time='%s'" % ( indexCode, getDate) resultDf = pd.read_sql(sql=sqlStr, con=self.engine) if resultDf.empty: self.logger.debug("getIndexConstituent从wind获取!") if indexOrSector == 'index': wsetdata = w.wset("indexconstituent", "date=%s;windcode=%s" % (getDate, indexCode)) else: wsetdata = w.wset("sectorconstituent", "date=2019-08-21;windcode=%s" % indexCode) if wsetdata.ErrorCode != 0: self.logger.error("获取指数成分股数据有误,错误代码" + str(wsetdata.ErrorCode)) return pd.DataFrame() resultDf = pd.DataFrame(wsetdata.Data, index=wsetdata.Fields).T dateList = [ datetampStr.strftime('%Y-%m-%d') for datetampStr in resultDf['date'].tolist() ] resultDf['date'] = dateList nameDic = { 'date': 'adjust_time', 'wind_code': 'stock_code', "sec_name": 'stock_name', 'i_weight': 'stock_weight' } resultDf.rename(columns=nameDic, inplace=True) resultDf['update_time'] = getDate resultDf['index_code'] = indexCode if 'stock_weight' not in resultDf.columns.tolist(): resultDf['stock_weight'] = np.nan self.GetDataToMysqlDemo.GetMain(resultDf, 'index_constituent') else: self.logger.debug("getIndexConstituent从本地获取!") return resultDf def getLackDataToMySql(self, tempCode, startDate, endDate, tableFlag='index'): if tableFlag == 'index': tableStr = 'index_value' codeName = 'index_code' elif tableFlag == 'fund': tableStr = 'fund_net_value' codeName = 'fund_code' elif tableFlag == 'stock': tableStr = 'stock_hq_value' codeName = 'stock_code' elif tableFlag == 'private': return elif tableFlag == 'monetary_fund': tableStr = 'monetary_fund' codeName = 'fund_code' sqlStr = "select max(update_time),min(update_time) from %s where %s='%s'" % ( tableStr, codeName, tempCode) cursor = self.conn.cursor() cursor.execute(sqlStr) dateStrTuple = cursor.fetchall()[0] maxDate = dateStrTuple[0] minDate = dateStrTuple[1] if not maxDate: self.getDataFromWind(tempCode, startDate=startDate, endDate=endDate, tableFlag=tableFlag) return if endDate < minDate or startDate > minDate: self.getDataFromWind(tempCode, startDate=startDate, endDate=endDate, tableFlag=tableFlag) elif startDate <= minDate: if minDate <= endDate < maxDate: if startDate != minDate: self.getDataFromWind(tempCode, startDate=startDate, endDate=minDate, tableFlag=tableFlag) elif endDate >= maxDate: self.getDataFromWind(tempCode, startDate=startDate, endDate=minDate, tableFlag=tableFlag) if endDate != maxDate: self.getDataFromWind(tempCode, startDate=maxDate, endDate=endDate, tableFlag=tableFlag) elif endDate > maxDate: self.getDataFromWind(tempCode, startDate=maxDate, endDate=endDate, tableFlag=tableFlag) def getDataFromWind(self, tempCode, startDate='2019-04-01', endDate='2019-04-30', tableFlag='index'): if tableFlag == 'index': tableStr = 'index_value' nameDic = { "OPEN": "open_price", "HIGH": "high_price", "LOW": "low_price", "CLOSE": "close_price", "VOLUME": "volume", "AMT": "amt", "CHG": "chg", "PCT_CHG": "pct_chg", "TURN": "turn" } fields = self.indexFieldName codeName = 'index_code' elif tableFlag == 'fund': tableStr = 'fund_net_value' nameDic = { "NAV": "net_value", "NAV_ACC": "acc_net_value", "SEC_NAME": "fund_name" } fields = self.fundFieldName codeName = 'fund_code' elif tableFlag == 'stock': tableStr = 'stock_hq_value' nameDic = { "OPEN": "open_price", "HIGH": "high_price", "LOW": "low_price", "CLOSE": "close_price", "VOLUME": "volume", "AMT": "amt", "TURN": "turn", "MKT_CAP_ARD": "market_value", "PE_TTM": "pe_ttm", "PS_TTM": "ps_ttm", "PB_LF": "pb_lf" } fields = self.stockFieldName codeName = 'stock_code' elif tableFlag == 'monetary_fund': tableStr = 'monetary_fund' nameDic = { "MMF_ANNUALIZEDYIELD": "week_annual_return", "MMF_UNITYIELD": "wan_unit_return", "SEC_NAME": "fund_name" } fields = self.monetaryFund codeName = 'fund_code' if tableFlag == 'stock': wsetdata = w.wsd(codes=tempCode, fields=fields, beginTime=startDate, endTime=endDate, options="PriceAdj=F") else: wsetdata = w.wsd(codes=tempCode, fields=fields, beginTime=startDate, endTime=endDate) if wsetdata.ErrorCode != 0: self.logger.error("获取行情数据有误,错误代码" + str(wsetdata.ErrorCode)) return tempDf = pd.DataFrame(wsetdata.Data, index=wsetdata.Fields, columns=wsetdata.Times).T tempDf.dropna(how='all', inplace=True) tempDf[codeName] = tempCode tempDf['update_time'] = tempDf.index.tolist() tempDf.rename(columns=nameDic, inplace=True) dateList = [ dateStr.strftime("%Y-%m-%d") for dateStr in tempDf['update_time'].tolist() ] tempDf['update_time'] = dateList self.GetDataToMysqlDemo.GetMain(tempDf, tableStr) return tempDf def getDataFromMySql(self, tempCode, startDate, endDate, tableFlag='index', nameList=['close_price']): if not nameList: self.logger.error('传入获取指数的字段不合法,请检查!') if tableFlag == 'index': tableStr = 'index_value' codeName = 'index_code' elif tableFlag == 'fund': codeName = 'fund_code' tableStr = 'fund_net_value' elif tableFlag == 'stock': codeName = 'stock_code' tableStr = 'stock_hq_value' elif tableFlag == 'private': codeName = 'fund_code' tableStr = 'private_net_value' elif tableFlag == 'monetary_fund': codeName = 'fund_code' tableStr = 'monetary_fund' sqlStr = "select %s,update_time from %s where %s='%s' and update_time>='%s'" \ " and update_time<='%s'" % (','.join(nameList), tableStr, codeName, tempCode, startDate, endDate) resultDf = pd.read_sql(sql=sqlStr, con=self.engine) resultDf = resultDf.drop_duplicates('update_time').sort_index() resultDf.set_index(keys='update_time', inplace=True, drop=True) return resultDf def checkLackMonthData(self, tempDf, codeList): totalCodeList = list(tempDf['stock_code'].unique()) lackCode = [code for code in codeList if code not in totalCodeList] haveCode = list(set(codeList).difference(lackCode)) for code in haveCode: if tempDf[tempDf['stock_code'] == code].shape[0] < 2: lackCode.append(code) return lackCode def getMonthData(self, codeList=[], startDate='2019-03-01', endDate='2019-05-30'): totalTradeList = [startDate, endDate] sqlstr = "select * from stock_month_value where stock_code in %s and update_time in %s" % ( tuple(codeList), tuple(totalTradeList)) tempDf = pd.read_sql(sql=sqlstr, con=self.engine) lackCode = self.checkLackMonthData(tempDf, codeList) if lackCode: self.logger.debug("getMonthData从wind获取,缺失code: %s" % ','.join(lackCode)) dfList = [] for tradeDate in [startDate, endDate]: tradeDateStr = tradeDate[:4] + tradeDate[5:7] + tradeDate[8:] wssData = w.wss(codes=lackCode, fields=['close', 'sec_name'], options="tradeDate=%s;priceAdj=F;cycle=M" % tradeDateStr) if wssData.ErrorCode != 0: self.logger.error("获取股票截面行情价格有误,错误代码" + str(wssData.ErrorCode)) return pd.DataFrame() df = pd.DataFrame(wssData.Data, columns=wssData.Codes, index=wssData.Fields).T df.rename(columns={ "CLOSE": "close_price", "SEC_NAME": "stock_name" }, inplace=True) df['update_time'] = [tradeDate] * len(df) df['stock_code'] = df.index.tolist() dfList.append(df) tempLackDf = pd.concat(dfList, axis=0, sort=True) self.GetDataToMysqlDemo.GetMain(tempLackDf, 'stock_month_value') tempDf = pd.concat([tempDf, tempLackDf], axis=0, sort=True) tempDf = tempDf.drop_duplicates( subset=['stock_code', 'update_time']) else: self.logger.debug("getMonthData从本地数据库获取!") return tempDf[['stock_code', 'close_price', 'update_time']] def getHQData(self, tempCode, startDate='2019-03-01', endDate='2019-05-30', tableFlag='index', nameList=['close_price']): ''' #获取指数行情数据入口 ''' self.getLackDataToMySql(tempCode, startDate, endDate, tableFlag) resultDf = self.getDataFromMySql(tempCode, startDate, endDate, tableFlag=tableFlag, nameList=nameList) return resultDf def getRiskFree(self, startDate='2019-03-01', endDate='2019-05-30'): wsetdata = w.wsd(codes=["SHI3MS1Y.IR"], fields=["close"], beginTime=startDate, endTime=endDate) if wsetdata.ErrorCode != 0: self.logger.error("获取行情数据有误,错误代码" + str(wsetdata.ErrorCode)) return tempDf = pd.DataFrame(wsetdata.Data, index=wsetdata.Fields, columns=wsetdata.Times).T return tempDf def getTradeDay(self, startDate, endDate, Period=''): ''' 获取指定周期交易日,封装wind接口 :param Period: ''日,W周,M月,Q季,S半年,Y年 :return: ''' # w.start() data = w.tdays(beginTime=startDate, endTime=endDate, options="Period=%s" % Period) if data.ErrorCode != 0: self.logger.error('wind获取交易日期错误,请检查!') return tradeDayList = data.Data[0] tradeDayList = [ tradeDay.strftime('%Y-%m-%d') for tradeDay in tradeDayList ] df = pd.DataFrame(tradeDayList, columns=['tradeDate']) df['startDate'] = [startDate] * df.shape[0] df['endDate'] = [endDate] * df.shape[0] df['Period'] = [Period] * df.shape[0] return df
def __init__(self): self.conn = MysqlCon().getMysqlCon(flag='connect') self.logger = mylog.logger
def __init__(self): MysqlConDemo = MysqlCon() self.engine = MysqlConDemo.getMysqlCon('engine') self.GetDataTotalMainDemo = GetDataTotalMain(data_resource='wind') self.industry_trade_limit = 20000000