def SetExtensionInfoIntoSql(self, msg): try: infoDict = OrderedDict() idDict = OrderedDict() if (msg['DB'] == 'DAY'): timeOffset = datetime2days(msg['ANA']['TRADE_DATE']) idDict['IC'] = int(msg['ANA']['INNER_CODE']) idDict['TO'] = timeOffset infoDict['_id'] = idDict infoDict['TO'] = timeOffset elif (msg['DB'] == 'WEEK'): idDict['IC'] = int(msg['ANA']['INNER_CODE']) idDict['YR'] = msg['ANA']['TRADE_YEAR'] idDict['WK'] = msg['ANA']['TRADE_WEEK'] infoDict['_id'] = idDict infoDict['TO'] = datetime2days(msg['ANA']['LAST_TRADE_DATE']) elif (msg['DB'] == 'MONTH'): idDict['IC'] = int(msg['ANA']['INNER_CODE']) idDict['YR'] = msg['ANA']['TRADE_YEAR'] idDict['MN'] = msg['ANA']['TRADE_MONTH'] infoDict['_id'] = idDict infoDict['TO'] = datetime2days(msg['ANA']['LAST_TRADE_DATE']) infoDict['OP'] = float(msg['ANA']['TOPEN']) infoDict['CP'] = float(msg['ANA']['TCLOSE']) infoDict['HP'] = float(msg['ANA']['THIGH']) infoDict['LP'] = float(msg['ANA']['TLOW']) infoDict['VL'] = long(msg['ANA']['TVOLUME']) infoDict['AM'] = float(msg['ANA']['TVALUE']) if ((msg['ANA']['EXCHR'] == None) or (msg['ANA']['EXCHR'] == 0)): infoDict['TR'] = 0 else: infoDict['TR'] = float(msg['ANA']['EXCHR']) infoDict['CA'] = float(msg['ANA']['CHNG']) infoDict['AA'] = [float(msg['EXT']['MA5']), float(msg['EXT']['MA10']), float(msg['EXT']['MA13']), float(msg['EXT']['MA14']), float(msg['EXT']['MA20']),float(msg['EXT']['MA25']), float(msg['EXT']['MA43'])] infoDict['AV'] = [long(msg['EXT']['VOL5']), long(msg['EXT']['VOL10']), long(msg['EXT']['VOL30']), long(msg['EXT']['VOL60']), long(msg['EXT']['VOL135'])] infoDict['KV'] = [float(msg['EXT']['K9']), float(msg['EXT']['K34'])] infoDict['DV'] = [float(msg['EXT']['D3']), float(msg['EXT']['D9'])] infoDict['JV'] = [float(msg['EXT']['J3']), float(msg['EXT']['J9'])] infoDict['DIF'] = [float(msg['EXT']['DIF1']), float(msg['EXT']['DIF2']), float(msg['EXT']['DIF3'])] infoDict['DEA'] = [float(msg['EXT']['DEA1']), float(msg['EXT']['DEA2']), float(msg['EXT']['DEA3'])] infoDict['MACD'] = [float(msg['EXT']['MACD1']), float(msg['EXT']['MACD2']), float(msg['EXT']['MACD3'])] infoDict['ER'] = int(msg['XRXD']) infoDict['RSV'] = [float(msg['EXT']['RSV9']), float(msg['EXT']['RSV34'])] infoDict['EMA'] = [float(msg['EXT']['EMA5']), float(msg['EXT']['EMA6']), float(msg['EXT']['EMA12']), float(msg['EXT']['EMA13']), float(msg['EXT']['EMA26']), float(msg['EXT']['EMA35'])] for mongo in self.mongoList: mongo.update(dbDict[msg['DB']]['TABLE']['MONGO'], 'data_'+self.colNameDict[msg['DB']][msg['EXT']['INNER_CODE']], {'_id':idDict}, infoDict) #并表数据写入 if (msg['WMT']): mongo.update(dbDict[msg['DB']]['TABLE']['MERGE'], 'data', {'_id':idDict}, infoDict) #print dbDict[msg['DB']]['TABLE']['MONGO'],'data_'+self.colNameDict[msg['DB']][msg['EXT']['INNER_CODE']] #print idDict #print infoDict except Exception,e: print 'Set extension info into MSSQL failed,',e print traceback.format_exc()
def updateMongo(self, updateData): try: infoDict = collections.OrderedDict() idDict = collections.OrderedDict() if (updateData['DB'] == 'DAY'): timeOffset = common.datetime2days(updateData['ANA']['TRADE_DATE']) idDict['IC'] = int(updateData['ANA']['INNER_CODE']) idDict['TO'] = timeOffset infoDict['_id'] = idDict infoDict['TO'] = timeOffset elif (updateData['DB'] == 'WEEK'): idDict['IC'] = int(updateData['ANA']['INNER_CODE']) idDict['YR'] = updateData['ANA']['TRADE_YEAR'] idDict['WK'] = updateData['ANA']['TRADE_WEEK'] infoDict['_id'] = idDict infoDict['TO'] = common.datetime2days(updateData['ANA']['LAST_TRADE_DATE']) elif (updateData['DB'] == 'MONTH'): idDict['IC'] = int(updateData['ANA']['INNER_CODE']) idDict['YR'] = updateData['ANA']['TRADE_YEAR'] idDict['MN'] = updateData['ANA']['TRADE_MONTH'] infoDict['_id'] = idDict infoDict['TO'] = common.datetime2days(updateData['ANA']['LAST_TRADE_DATE']) infoDict['OP'] = float(updateData['ANA']['TOPEN']) infoDict['CP'] = float(updateData['ANA']['TCLOSE']) infoDict['HP'] = float(updateData['ANA']['THIGH']) infoDict['LP'] = float(updateData['ANA']['TLOW']) infoDict['VL'] = long(updateData['ANA']['TVOLUME']) infoDict['AM'] = float(updateData['ANA']['TVALUE']) infoDict['TR'] = float(updateData['ANA']['EXCHR']) if (updateData['ANA']['EXCHR'] is not None) else float(0) infoDict['CA'] = float(updateData['ANA']['CHNG']) if (updateData['ANA']['CHNG'] is not None) else float(0) infoDict['AA'] = [float(updateData['EXT']['MA5']), float(updateData['EXT']['MA10']), float(updateData['EXT']['MA13']), float(updateData['EXT']['MA14']), float(updateData['EXT']['MA20']),float(updateData['EXT']['MA25']), float(updateData['EXT']['MA43'])] infoDict['AV'] = [long(updateData['EXT']['VOL5']), long(updateData['EXT']['VOL10']), long(updateData['EXT']['VOL30']), long(updateData['EXT']['VOL60']), long(updateData['EXT']['VOL135'])] infoDict['KV'] = [float(updateData['EXT']['K9']), float(updateData['EXT']['K34'])] infoDict['DV'] = [float(updateData['EXT']['D3']), float(updateData['EXT']['D9'])] infoDict['JV'] = [float(updateData['EXT']['J3']), float(updateData['EXT']['J9'])] infoDict['DIF'] = [float(updateData['EXT']['DIF1']), float(updateData['EXT']['DIF2']), float(updateData['EXT']['DIF3'])] infoDict['DEA'] = [float(updateData['EXT']['DEA1']), float(updateData['EXT']['DEA2']), float(updateData['EXT']['DEA3'])] infoDict['MACD'] = [float(updateData['EXT']['MACD1']), float(updateData['EXT']['MACD2']), float(updateData['EXT']['MACD3'])] infoDict['ER'] = int(updateData['XRXD']) infoDict['RSV'] = [float(updateData['EXT']['RSV9']), float(updateData['EXT']['RSV34'])] infoDict['EMA'] = [float(updateData['EXT']['EMA5']), float(updateData['EXT']['EMA6']), float(updateData['EXT']['EMA12']), float(updateData['EXT']['EMA13']), float(updateData['EXT']['EMA26']), float(updateData['EXT']['EMA35'])] for mongoHandle in self.mongoHandleList: mongoHandle.update(QN_constant.dbDict[updateData['DB']]['TABLE']['MONGO'], 'data', {'_id':idDict}, infoDict) #并表数据写入 if (updateData['WMT']): mongoHandle.update(QN_constant.dbDict[updateData['DB']]['TABLE']['MERGE'], 'data', {'_id':idDict}, infoDict) except: self.logHandle.logInfo(str(traceback.format_exc())) print traceback.format_exc() return None
def InsertRecentIntoMongo(mongoHandle, db, recentInfoDict, splitSecurityDict): print '>>', sys._getframe().f_code.co_name for innerCode in recentInfoDict: infoDict = OrderedDict() idDict = OrderedDict() if (db == 'DAY'): timeOffset = datetime2days(recentInfoDict[innerCode]['TRADE_DATE']) idDict['IC'] = int(recentInfoDict[innerCode]['INNER_CODE']) idDict['TO'] = timeOffset infoDict['_id'] = idDict infoDict['TO'] = timeOffset elif (db == 'WEEK'): idDict['IC'] = int(recentInfoDict[innerCode]['INNER_CODE']) idDict['YR'] = recentInfoDict[innerCode]['TRADE_YEAR'] idDict['WK'] = recentInfoDict[innerCode]['TRADE_WEEK'] infoDict['_id'] = idDict infoDict['TO'] = datetime2days(recentInfoDict[innerCode]['LAST_TRADE_DATE']) elif (db == 'MONTH'): idDict['IC'] = int(recentInfoDict[innerCode]['INNER_CODE']) idDict['YR'] = recentInfoDict[innerCode]['TRADE_YEAR'] idDict['MN'] = recentInfoDict[innerCode]['TRADE_MONTH'] infoDict['_id'] = idDict infoDict['TO'] = datetime2days(recentInfoDict[innerCode]['LAST_TRADE_DATE']) infoDict['OP'] = float(recentInfoDict[innerCode]['TOPEN']) infoDict['CP'] = float(recentInfoDict[innerCode]['TCLOSE']) infoDict['HP'] = float(recentInfoDict[innerCode]['THIGH']) infoDict['LP'] = float(recentInfoDict[innerCode]['TLOW']) infoDict['VL'] = long(recentInfoDict[innerCode]['TVOLUME']) infoDict['AM'] = float(recentInfoDict[innerCode]['TVALUE']) infoDict['TR'] = float(0.0) infoDict['CA'] = float(recentInfoDict[innerCode]['CHNG']) infoDict['AA'] = [float(recentInfoDict[innerCode]['MA5']), float(recentInfoDict[innerCode]['MA10']), float(recentInfoDict[innerCode]['MA13']), float(recentInfoDict[innerCode]['MA14']), float(recentInfoDict[innerCode]['MA20']),float(recentInfoDict[innerCode]['MA25']), float(recentInfoDict[innerCode]['MA43'])] infoDict['AV'] = [long(recentInfoDict[innerCode]['VOL5']), long(recentInfoDict[innerCode]['VOL10']), long(recentInfoDict[innerCode]['VOL30']), long(recentInfoDict[innerCode]['VOL60']), long(recentInfoDict[innerCode]['VOL135'])] infoDict['KV'] = [float(recentInfoDict[innerCode]['K9']), float(recentInfoDict[innerCode]['K34'])] infoDict['DV'] = [float(recentInfoDict[innerCode]['D3']), float(recentInfoDict[innerCode]['D9'])] infoDict['JV'] = [float(recentInfoDict[innerCode]['J3']), float(recentInfoDict[innerCode]['J9'])] infoDict['DIF'] = [float(recentInfoDict[innerCode]['DIF1']), float(recentInfoDict[innerCode]['DIF2']), float(recentInfoDict[innerCode]['DIF3'])] infoDict['DEA'] = [float(recentInfoDict[innerCode]['DEA1']), float(recentInfoDict[innerCode]['DEA2']), float(recentInfoDict[innerCode]['DEA3'])] infoDict['MACD'] = [float(recentInfoDict[innerCode]['MACD1']), float(recentInfoDict[innerCode]['MACD2']), float(recentInfoDict[innerCode]['MACD3'])] infoDict['ER'] = int(recentInfoDict[innerCode]['XRXD']) infoDict['RSV'] = [float(recentInfoDict[innerCode]['RSV9']), float(recentInfoDict[innerCode]['RSV34'])] infoDict['EMA'] = [float(recentInfoDict[innerCode]['EMA5']), float(recentInfoDict[innerCode]['EMA6']), float(recentInfoDict[innerCode]['EMA12']), float(recentInfoDict[innerCode]['EMA13']), float(recentInfoDict[innerCode]['EMA26']), float(recentInfoDict[innerCode]['EMA35'])] mongoHandle.update(dbDict[db]['TABLE']['MONGO'], 'data_'+ splitSecurityDict[db][innerCode], {'_id':idDict}, infoDict) print '<<', sys._getframe().f_code.co_name return None
def getRecentTradeTime(self): ''' 从中间库获取所有股票最后更新记录时间 ''' try: sqlCmd = ''' SELECT [INNER_CODE], [TRADE_DATE] FROM( SELECT [INNER_CODE], [TRADE_DATE], ROW_NUMBER() OVER(PARTITION BY INNER_CODE ORDER BY TRADE_DATE DESC) AS DESCENT FROM [QN_Quotation].[dbo].[SECURITY_ANALYSIS_DAY] ) t WHERE DESCENT = '1' ''' recentDict = dict() records = self.mssqlHandle.sqlQuery(sqlCmd) for record in records: recentDict[str(record['INNER_CODE'])] = common.datetime2days(record['TRADE_DATE']) return recentDict except IndexError: self.logHandle.logInfo(str(traceback.format_exc())) print 'Get stock lately update time failed.' return None
def setData(self, records): ''' Insert stock info into mongo. ''' try: #for mongoHandle in self.mongoHandleList: # mongoHandle.remove() recordNum = 0 recentDict = self.getRecentTradeTime() for record in records: #Get inward code inwardId = record['INNER_CODE'] keyDoc = collections.OrderedDict() keyDoc['IC'] = int(inwardId) keyDoc['TO'] = int(common.datetime2days(record['EX_DIVI_DATE'])) #spec spec = collections.OrderedDict() spec['_id'] = keyDoc #document document = collections.OrderedDict() document['_id'] = keyDoc document['BS'] = float(0) if (record['CASH_BT'] is None) else float(record['CASH_BT']) document['PR'] = float(0) if (record['ALLOT_PRC'] is None) else float(record['ALLOT_PRC']) CirculationStockChangeRatio = float(0) CirculationStockChangeRatio += float(0) if (record['CAP_SHR'] is None) else float(record['CAP_SHR']) CirculationStockChangeRatio += float(0) if (record['ALLOT_PCT'] is None) else float(record['ALLOT_PCT']) CirculationStockChangeRatio += float(0) if (record['BONUS_SHR'] is None) else float(record['BONUS_SHR']) document['FR'] = CirculationStockChangeRatio / float(10) for mongoHandle in self.mongoHandleList: if (recentDict.get(inwardId) is None): document['EF'] = int(0) elif (recentDict.get(inwardId) >= keyDoc['TO']): document['EF'] = int(1) else: document['EF'] = int(0) mongoHandle.update('INFO', 'RIGHT', spec, document) recordNum += 1 self.logHandle.logInfo('Update ration succeed, update records: ' + str(recordNum)) except: self.logHandle.logInfo('Update ration failed, update records: ' + str(recordNum) + '/' + str(len(records)) + ', exception: ' + str(traceback.format_exc())) print traceback.format_exc() return None