Exemple #1
0
def UpdateExtensionInfo(mssqlDict):
  mssqlHandle = MssqlAPI(server = mssqlDict['SERVER'], db = mssqlDict['DB'], user = mssqlDict['USER'], pwd = mssqlDict['PWD'])
  logFile = LogFile(name = 'Indirection')
  try:
    mssqlHandle.sqlExecuteProc('usp_UpdateFrom251', ())
    mssqlHandle.sqlCommit()
    logFile.logInfo('Execute SQL Proc "usp_UpdateFrom251" succeed.')
  except:
    logFile.logInfo('Execute SQL Proc "usp_UpdateFrom251" failed.')
    print traceback.format_exc()
Exemple #2
0
def dropOriginalData(mongoDict):
  logFile = LogFile(name = 'Indirection')
  for mongo in mongoDict:
    try:
      mongoHandle = MongoAPI(server = mongo['SERVER'], port = mongo['PORT'])
      #drop table
      splitTableList = ['KDayData', 'KWeeklyData', 'KMonthData']
      mergeTableList = ['CandleDay', 'CandleWeek', 'CandleMonth']
      for splitTable in splitTableList:
        mongoHandle.drop(splitTable)
      for mergeTable in mergeTableList:
        mongoHandle.drop(mergeTable)
      #create index
      for mergeTable in mergeTableList:
        mongoHandle.createIndex(mergeTable, 'data', [('_id.IC', pymongo.ASCENDING)])
        mongoHandle.createIndex(mergeTable, 'data', [('TO', pymongo.DESCENDING)])
      logFile.logInfo('Drop original data from server: ' + mongo['SERVER'] + ' succeed.')
      del mongoHandle
    except:
      logFile.logInfo('Drop original data from server: ' + mongo['SERVER'] + ' failed.')
      print traceback.format_exc()
  def __getAnalysisInfo(self, db, condition, queue):
    '''
    private method
    #获取指定证券代码列表所示分析信息,以单个证券代码为单位由queue传递出去
    #传出消息按如下字典格式:
    {'DB'  :'DAY/WEEK/MONTH',
     'MSG' :[{'INNER_CODE':<INNER_CODE>,
              'LCLOSE':<LCLOSE1>,
              'TOPEN':<TOPEN1>,
              'TCLOSE':<TCLOSE1>,
              'THIGH':<THIGH1>,
              'TLOW':<TLOW1>,
              'TVOLUME':<TVOLUME1>,
              'TVALUE':<TVALUE1>,
              ... ...
             },
             {'INNER_CODE':<INNER_CODE>,
              'LCLOSE':<LCLOSE2>,
              'TOPEN':<TOPEN2>,
              'TCLOSE':<TCLOSE2>,
              'THIGH':<THIGH2>,
              'TLOW':<TLOW2>,
              'TVOLUME':<TVOLUME2>,
              'TVALUE':<TVALUE2>,
              ... ...},
             ... ...]
    }
    '''
    logFile = LogFile(name = 'Indirection')
    try:
      if ((db == 'WEEK') or (db == 'MONTH')):
        field = '''
          [SECURITY_ANALYSIS].[FIRST_TRADE_DATE] AS FDATE,
          [SECURITY_ANALYSIS].[LAST_TRADE_DATE] AS LDATE,
        '''
      else:
        field = '''
          [SECURITY_ANALYSIS].[TRADE_DATE] AS FDATE,
          [SECURITY_ANALYSIS].[TRADE_DATE] AS LDATE,
        ''' 
      #Instruct SQL command 
      sqlCmd = '''
        SELECT
          [SECURITY_ANALYSIS].[INNER_CODE] AS INNER_CODE,
          [SECURITY_ANALYSIS].[LCLOSE] AS LCLOSE,
          [SECURITY_ANALYSIS].[TOPEN] AS TOPEN,
          [SECURITY_ANALYSIS].[TCLOSE] AS TCLOSE,
          [SECURITY_ANALYSIS].[THIGH] AS THIGH,
          [SECURITY_ANALYSIS].[TLOW] AS TLOW,
          [SECURITY_ANALYSIS].[TVOLUME] AS TVOLUME,
          [SECURITY_ANALYSIS].[TVALUE] AS TVALUE,
          [SECURITY_ANALYSIS].[CHNG] AS CHNG,
          [SECURITY_ANALYSIS].[EXCHR] AS EXCHR,
          ''' + field + '''
          ''' + dbDict[db]['TRADEDATE'] + '''
        FROM
          ''' + dbDict[db]['TABLE']['ANA'] + ''' AS [SECURITY_ANALYSIS]
        WHERE
          ''' + condition + '''
        ORDER BY
          INNER_CODE,
          ''' + dbDict[db]['ORDER']
      records = self.mssqlHandle.sqlQuery(sqlCmd)
      #records = self.mssqlHandle.sqlQueryProc('p_list_all', (db,))
      logFile.logInfo('Get analysis info succeed.')

      innerCode = ''
      #securityInfoDict = {} 内存较大适用
      #单个证券多日衍生数据列表
      securityInfoList = list()
      #遍历查询记录
      for rec in records:
        infoDict = {}
        #遍历基础信息字段
        for column in dbDict[db]['COLUMN']['ANA']:
          #查询结果以字段名称为key值存入有序字典
          infoDict[column] = rec[column]
        #if ((db == 'WEEK') or (db == 'MONTH')):
        infoDict['FDATE'] = rec['FDATE']
        infoDict['LDATE'] = rec['LDATE']
        #不同股票写入不同字典键值中
        if ((rec['INNER_CODE'] != innerCode) and (innerCode != '')):
          
          queue.put({'DB':db,'MSG':securityInfoList})
          #securityInfoDict[innerCode] = securityInfoList 内存较大适用
          securityInfoList = list()
          innerCode = rec['INNER_CODE']
        elif (innerCode == ''):
          innerCode = rec['INNER_CODE']
        #将有序字典追加到结果集列表中
        securityInfoList.append(infoDict)
      else:
        #securityInfoDict[innerCode] = securityInfoList 内存较大适用
        queue.put({'DB':db,'MSG':securityInfoList})
        #securityInfoList = list()
      
      #for innerCode in securityInfoDict: 内存较大适用
      #  queue.put({'DB':db,'MSG':securityInfoDict[innerCode]})
      logFile.logInfo('Standardize analysis info succeed.')
    except IndexError:
      logFile.logInfo('Invalid table name.')
      print 'Invalid table name.'
      
Exemple #4
0
def CorrectAllSecurityInfo(mssqlDict, mongoDict, processDict, queueDict):
  startTime = datetime.now()
  print 'Start at:',startTime
  print 'DisposeSplitSecurity at',datetime.now()
  #分表名获取
  splitSecurityDict = DisposeSplitSecurity(mssqlDict, mongoDict)
  print 'DisposeAnalysisInfoProc at',datetime.now()
  #证券分析数据查询
  analysisQueue = Queue(queueDict['ANA'])
  monitorProcessDict = dict()
  analysisInfoProcessList = list()
  for db in dbDict:
    process = Process(target=DisposeAnalysisInfoProc, args=(mssqlDict, db, analysisQueue))
    process.start()
    analysisInfoProcessList.append(process)
  monitorProcessDict['Analysis'] = analysisInfoProcessList
  #print 'DisposeCalculateExtensionProc at',datetime.now()
  #证券衍生数据计算
  #extensionQueue = Queue(queueDict['EXT'])
  mongoQueue = Queue(queueDict['MONGO'])
  excludeRightDividend = ExcludeRightDividend(mssqlDict['SERVER'], mssqlDict['DB'], mssqlDict['USER'], mssqlDict['PWD'])
  excludeRightDividendDict = excludeRightDividend.GetExcludeRightDividendInfo()
  calculateExtensionProcList = DisposeCalculateExtensionProc(processDict, excludeRightDividendDict, analysisQueue, mongoQueue)
  monitorProcessDict['Calculate'] = calculateExtensionProcList
  #print 'DisposeExtensionInfoProc at',datetime.now()
  #启动写入MSSQL衍生数据进程
  #extentionInfoProcList = DisposeExtensionInfoProc(processDict, mssqlDict, extensionQueue, mongoQueue)
  #monitorProcessDict['Extension'] = extensionInfoProcList
  #print 'DisposeInsertInfoIntoMongoDbProc at',datetime.now()
  #启动写入MongoDB数据进程
  insertInfoIntoMongoDbProcList = DisposeInsertInfoIntoMongoDbProc(processDict, mongoDict, mongoQueue, splitSecurityDict)
  monitorProcessDict['Mongo'] = insertInfoIntoMongoDbProcList
  terminateNum = 0
  while True:
    #移除已终止的进程
    for processName in monitorProcessDict:
      for process in monitorProcessDict[processName]:
        if (process.is_alive() == False):
          monitorProcessDict[processName].remove(process)
    #检查计算,SQL写入,MongoDB写入进程,如进程意外终止,则重新启动进程
    while (len(monitorProcessDict['Calculate']) < processDict['CALCULATE']):
      process = Process(target=CalculateExtensionProc, args=(excludeRightDividendDict, analysisQueue, mongoQueue))
      process.start()
      monitorProcessDict['Calculate'].append(process)
    while (len(monitorProcessDict['Mongo']) < processDict['MONGO']):
      process = Process(target=InsertInfoIntoMongoDbProc, args=(mongoDict, mongoQueue, splitSecurityDict))
      process.start()
      monitorProcessDict['Mongo'].append(process)
    print datetime.now()
    for processName in monitorProcessDict:
      print processName,len(monitorProcessDict[processName])
    print 'analysisQueue:',analysisQueue.qsize()
    #print 'extensionQueue:',extensionQueue.qsize()
    print 'mongoQueue:',mongoQueue.qsize()
    if ((len(monitorProcessDict['Analysis']) == 0) and 
        (analysisQueue.qsize() == 0) and 
        (mongoQueue.qsize() == 0)):
      terminateNum += 1
    if (terminateNum >= 10):
      #关闭所有进程
      for processName in monitorProcessDict:
        for process in monitorProcessDict[processName]:
          if (process.is_alive() == True):
            process.terminate()
      break
    sleep(10)
    
  finalTime = datetime.now()
  deltaTime = finalTime - startTime
  totalTime = deltaTime.total_seconds()
  totalHour = totalTime // 3600
  totalMin = (totalTime % 3600) // 60
  totalSec = totalTime % 60
  print("Total time: %d(h)%d(m)%d(s)" % (totalHour, totalMin, totalSec))
  logFile = LogFile(name = 'Indirection')
  logFile.logInfo('Correct all security info succeed, total time: ' + str(totalHour) + '(h)' + str(totalMin) + '(m)' + str(totalSec) + '(s)')