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()
def __init__(self, mssqlConnDict, mongoConnDict):
'''
Constructor MSSQL handle.
'''
self.mssqlHandle = MssqlAPI(mssqlConnDict['SERVER'], mssqlConnDict['DB'], mssqlConnDict['USER'], mssqlConnDict['PWD'])
self.mongoHandle = list()
for mongo in mongoConnDict:
self.mongoHandle.append(MongoAPI(mongo['SERVER'], mongo['PORT']))
class ExtensionInfo(object):
'''
Security extension info
'''
def __init__(self, server, db, user, pwd):
'''
Constructor
'''
self.mssqlHandle = MssqlAPI(server, db, user, pwd)
def __del__(self):
self.mssqlHandle.sqlCommit()
del self.mssqlHandle
def SetExtensionInfo(self, msg):
columnStr = ''
valueStr = ''
for key in msg['EXT']:
columnStr += key + ','
if (key in ('MA5', 'MA10', 'MA13', 'MA14', 'MA20', 'MA25', 'MA43', 'VOL5', 'VOL10', 'VOL30', 'VOL60', 'VOL135')):
valueStr += 'convert(numeric(18,4),' + str(msg['EXT'][key]) + '),'
elif (key in ('RSV9', 'K9', 'D3', 'J3', 'RSV34', 'K34', 'D9', 'J9', 'EMA5', 'EMA6', 'EMA12', 'EMA13', 'EMA26', 'EMA35', \
'DIF1', 'DEA1', 'MACD1', 'DIF2', 'DEA2', 'MACD2', 'DIF3', 'DEA3', 'MACD3')):
valueStr += 'convert(numeric(9,4),' + str(msg['EXT'][key]) + '),'
else:
valueStr += "'" + str(msg['EXT'][key]) + "',"
columnStr = columnStr[0:-1]
valueStr = valueStr[0:-1]
sqlCmd = '''
INSERT INTO
''' + dbDict[msg['DB']]['TABLE']['EXT'] + '''
(''' + columnStr + ''')
VALUES
('''+ valueStr +''');
'''
self.mssqlHandle.sqlAppend(sqlCmd)
def __init__(self, server, db, user, pwd): ''' Constructor ''' self.mssqlHandle = MssqlAPI(server, db, user, pwd)
class ExcludeRightDividend(object):
'''
classdocs
'''
def __init__(self, server, db, user, pwd):
'''
Constructor
'''
self.mssqlHandle = MssqlAPI(server, db, user, pwd)
def __del__(self):
del self.mssqlHandle
def GetExcludeRightDividendInfo(self):
sqlCmd = self.__ExcludeRightDividendSqlCmd()
records = self.mssqlHandle.sqlQuery(sqlCmd)
excludeRightDividendDict = dict()
excludeRightDividendList = list()
innerCode = ''
recDict = dict()
for rec in records:
#同一证券以列表方式按时间升序存放到以证券代码为键值的字典中
recDict['date'] = rec['DATE']
recDict['type'] = rightType(rec['CASH_BT'], rec['CIRCULATION_CHANGE_RATIO'])
if ((rec['INNER_CODE'] != innerCode) and (innerCode != '')):
excludeRightDividendDict[innerCode] = excludeRightDividendList
excludeRightDividendList = list()
innerCode = rec['INNER_CODE']
elif (innerCode == ''):
innerCode = rec['INNER_CODE']
excludeRightDividendList.append(recDict.copy())
recDict.clear()
else:
excludeRightDividendDict[innerCode] = excludeRightDividendList
return excludeRightDividendDict
def GetRecentExcludeRightDividendInfo(self):
sqlCmd = self.__ExcludeRightDividendSqlCmd()
records = self.mssqlHandle.sqlQuery(sqlCmd)
excludeRightDividendDict = dict()
innerCode = ''
recDict = dict()
excludeRightDividend = dict()
for rec in records:
#同一证券最新除权除息信息存放到以证券代码为键值的字典中
recDict['date'] = rec['DATE']
recDict['type'] = rightType(rec['CASH_BT'], rec['CIRCULATION_CHANGE_RATIO'])
if ((rec['INNER_CODE'] != innerCode) and (innerCode != '')):
excludeRightDividendDict[innerCode] = excludeRightDividend.copy()
excludeRightDividend.clear()
innerCode = rec['INNER_CODE']
elif (innerCode == ''):
innerCode = rec['INNER_CODE']
excludeRightDividend = recDict.copy()
recDict.clear()
else:
excludeRightDividendDict[innerCode] = excludeRightDividend.copy()
return excludeRightDividendDict
def __ExcludeRightDividendSqlCmd(self):
'''
SELECT * FROM (
SELECT
RTRIM(
CASE
WHEN [PUB_SEC_CODE].[SEC_TYPE] = '5' THEN
CASE
WHEN [PUB_SEC_CODE].[MKT_TYPE] = '2' THEN '21' + [PUB_SEC_CODE].[SEC_CODE]
WHEN [PUB_SEC_CODE].[MKT_TYPE] = '1' THEN '22' + [PUB_SEC_CODE].[SEC_CODE]
END
WHEN [PUB_SEC_CODE].[SEC_TYPE] = '1' THEN
CASE
WHEN [PUB_SEC_CODE].[MKT_TYPE] = '2' THEN '11' + [PUB_SEC_CODE].[SEC_CODE]
WHEN [PUB_SEC_CODE].[MKT_TYPE] = '1' THEN '12' + [PUB_SEC_CODE].[SEC_CODE]
END
END) AS INNER_CODE,
[STK_ALLOT_RESULT].[A_EXRGT_DATE] AS [DATE],
0 AS [CASH_BT],
[STK_ALLOT_RESULT].[ALLOT_PCT] AS [ALLOT_PCT],
[STK_ALLOT_RESULT].[ALLOT_PRC] AS [ALLOT_PRC],
0 AS CIRCULATION_CHANGE_RATIO
FROM
[Genius].[dbo].[STK_ALLOT_RESULT] AS [STK_ALLOT_RESULT],
[Genius].[dbo].[PUB_SEC_CODE] AS [PUB_SEC_CODE]
WHERE
[STK_ALLOT_RESULT].[COMCODE] = [PUB_SEC_CODE].[COMCODE] AND
[STK_ALLOT_RESULT].[A_EXRGT_DATE] IS NOT NULL AND
[PUB_SEC_CODE].[SEC_STYPE] IN ('101', '501') AND
[PUB_SEC_CODE].[SEC_CODE] IS NOT NULL AND
[PUB_SEC_CODE].[SEC_SNAME] IS NOT NULL AND
[PUB_SEC_CODE].[MKT_TYPE] IS NOT NULL
UNION ALL
SELECT
RTRIM(
CASE
WHEN [PUB_SEC_CODE].[SEC_TYPE] = '5' THEN
CASE
WHEN [PUB_SEC_CODE].[MKT_TYPE] = '2' THEN '21' + [PUB_SEC_CODE].[SEC_CODE]
WHEN [PUB_SEC_CODE].[MKT_TYPE] = '1' THEN '22' + [PUB_SEC_CODE].[SEC_CODE]
END
WHEN [PUB_SEC_CODE].[SEC_TYPE] = '1' THEN
CASE
WHEN [PUB_SEC_CODE].[MKT_TYPE] = '2' THEN '11' + [PUB_SEC_CODE].[SEC_CODE]
WHEN [PUB_SEC_CODE].[MKT_TYPE] = '1' THEN '12' + [PUB_SEC_CODE].[SEC_CODE]
END
END) AS INNER_CODE,
[STK_DIV_INFO].[EX_DIVI_DATE] AS DATE,
CASE
WHEN [STK_DIV_INFO].[CASH_BT] IS NULL THEN 0
ELSE [STK_DIV_INFO].[CASH_BT]
END AS [CASH_BT],
0 AS [ALLOT_PCT],
0 AS [ALLOT_PRC],
CASE
WHEN [STK_DIV_INFO].[BONUS_SHR] IS NULL THEN
CASE
WHEN [STK_DIV_INFO].[CAP_SHR] IS NULL THEN 0
ELSE [STK_DIV_INFO].[CAP_SHR] / 10
END
ELSE [STK_DIV_INFO].[BONUS_SHR] / 10
END AS CIRCULATION_CHANGE_RATIO
FROM
[Genius].[dbo].[STK_DIV_INFO] AS [STK_DIV_INFO],
[Genius].[dbo].[PUB_SEC_CODE] AS [PUB_SEC_CODE]
WHERE
[STK_DIV_INFO].[COMCODE] = [PUB_SEC_CODE].[COMCODE] AND
[STK_DIV_INFO].[EX_DIVI_DATE] IS NOT NULL AND
[PUB_SEC_CODE].[SEC_STYPE] IN ('101', '501') AND
[PUB_SEC_CODE].[SEC_CODE] IS NOT NULL AND
[PUB_SEC_CODE].[SEC_SNAME] IS NOT NULL AND
[PUB_SEC_CODE].[MKT_TYPE] IS NOT NULL AND
[STK_DIV_INFO].[PRG_CODE] = '29' AND
[STK_DIV_INFO].[STK_TYPE_CODE] = '1'
) t
ORDER BY
[t].[INNER_CODE],
[t].[DATE]
'''
sqlCmd = '''
SELECT
[INNER_CODE],
[EX_DIVI_DATE] as DATE,
[CASH_BT],
[ALLOT_PCT],
[ALLOT_PRC],
CASE
WHEN [CAP_SHR] IS NULL THEN
CASE
WHEN [ALLOT_PCT] IS NULL THEN
CASE
WHEN [BONUS_SHR] IS NULL THEN 0
ELSE [BONUS_SHR] / 10
END
ELSE
CASE
WHEN [BONUS_SHR] IS NULL THEN [ALLOT_PCT] / 10
ELSE ([ALLOT_PCT] + [BONUS_SHR]) / 10
END
END
ELSE
CASE
WHEN [ALLOT_PCT] IS NULL THEN
CASE
WHEN [BONUS_SHR] IS NULL THEN [CAP_SHR] / 10
ELSE ([CAP_SHR] + [BONUS_SHR]) / 10
END
ELSE
CASE
WHEN [BONUS_SHR] IS NULL THEN ([CAP_SHR] + [ALLOT_PCT]) / 10
ELSE ([CAP_SHR] + [ALLOT_PCT] + [BONUS_SHR]) / 10
END
END
END AS CIRCULATION_CHANGE_RATIO
FROM
[QN_Quotation].[dbo].[QN_DIV_INFO]
ORDER BY
INNER_CODE,
DATE
'''
return sqlCmd
class AnalysisInfo(object):
'''
Security analysis info
'''
def __init__(self, server, db, user, pwd):
'''
Constructor
'''
self.mssqlHandle = MssqlAPI(server, db, user, pwd)
def __del__(self):
del self.mssqlHandle
def GetSpecifyAnalysisInfo(self, db, codeList, queue):
condition = 'INNER_CODE IN ('
for code in codeList:
condition += "'" + str(code) + "',"
condition = condition[0:-1]
condition += ')'
if ((db == 'WEEK') and (db == 'MONTH')):
condition += ' AND [SECURITY_ANALYSIS].[TRADE_DAYS] > 0'
self.__getAnalysisInfo(db, condition, queue)
def GetAllAnalysisInfo(self, db, queue):
if ((db == 'WEEK') and (db == 'MONTH')):
condition = '[SECURITY_ANALYSIS].[TRADE_DAYS] > 0'
else:
condition = '1 = 1'
self.__getAnalysisInfo(db, condition, queue)
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.'
class SplitSecurity(object):
'''
Split security index and insert into mongoDB.
'''
def __init__(self, mssqlConnDict, mongoConnDict):
'''
Constructor MSSQL handle.
'''
self.mssqlHandle = MssqlAPI(mssqlConnDict['SERVER'], mssqlConnDict['DB'], mssqlConnDict['USER'], mssqlConnDict['PWD'])
self.mongoHandle = list()
for mongo in mongoConnDict:
self.mongoHandle.append(MongoAPI(mongo['SERVER'], mongo['PORT']))
def __del__(self):
'''
Destructor, delete class variable.
'''
try:
del self.mssqlHandle
except:
pass
try:
for mongoHandle in self.mongoHandle:
del mongoHandle
except:
pass
def GetSecurityCodeList(self, db):
'''
Get security code list from Qianniu database.
#从千牛基础库信息中读取包含证券列表信息。
'''
sqlCmd = '''
SELECT
INNER_CODE
FROM
''' + dbDict[db]['TABLE']['ANA'] + '''
GROUP BY
INNER_CODE
ORDER BY
INNER_CODE
'''
records = self.mssqlHandle.sqlQuery(sqlCmd)
securityCodeList = list()
for rec in records:
securityCodeList.append(rec['INNER_CODE'])
return securityCodeList
def SplitSecuritySubarea(self, securityCodeList, num):
'''
Split security index, return split list.
'''
securityNum = len(securityCodeList)
splitNum = securityNum // num
if (securityNum % num > 0):
splitNum += 1
indexList = list()
minValue = int(securityCodeList[0])
maxValue = 0
for sec in securityCodeList:
index = securityCodeList.index(sec)
maxValue = int(sec)
if (index % splitNum == splitNum-1):
indexList.append({'min':minValue,'max':maxValue})
minValue = maxValue + 1
else:
indexList.append({'min':minValue,'max':maxValue})
return indexList
def SplitSecurityIndex(self, securityCodeList, num):
indexList = self.SplitSecuritySubarea(securityCodeList, num)
splitDict = dict()
for sec in securityCodeList:
secCode = int(sec)
for index in indexList:
if ((secCode >= index['min']) and (secCode <= index['max'])):
idxStr = str(indexList.index(index))
while (len(idxStr) < 3):
idxStr = '0' + idxStr
splitDict[sec] = idxStr
return splitDict