def insertToOracle(self,db,assetDF):
for i in assetDF.index:
insertList = []
fund_id = assetDF.iloc[i]['fund_id']
port_id = assetDF.iloc[i]['port_id']
l_date = assetDF.iloc[i]['l_date']
selectConstraint = [fund_id,port_id,l_date]
self.checkReload(db,selectConstraint)
for j in assetDF.columns:
#print(i)
#print(j)
value = assetDF.iloc[i][j]
if j != 'fund_id' and j != 'port_id' and j != 'l_date' and j != 'futures_asset':
if type(value) == type(np.float64(0)):
value = value.item()
if math.isnan(value):
value = 'null'
if type(value) == type(u''):
value = float(value.replace(',',''))
if j == 'futures_asset':
value = 'null'
if type(value) == type(u''):
value = str(value)
insertList.append(value)
sql='''insert into portAsset
(id,accumulate_profit,accumulate_unit_value,allocatble_profit,begin_cash,bond_asset,credit_value,current_cash_eod,deposit_asset,fund_asset,fund_id,futures_asset,l_date,net_assets,other_currency_asset,port_id,repo_asset,security_settlement_cash,stock_asset,total_assets,unit_value,unit_value_yesterday,FCU,LCU)
VALUES(S_portAsset.Nextval,{0[0]},{0[1]},{0[2]},{0[3]},{0[4]},{0[5]},{0[6]},{0[7]},{0[8]},'{0[9]}',{0[10]},{0[11]},{0[12]},{0[13]},'{0[14]}',{0[15]},{0[16]},{0[17]},{0[18]},{0[19]},{0[20]},ora_login_user,ora_login_user)'''.format(insertList)
tool.sqlDML(sql,db)
def insertToOracle(self, db, holdingDF):
for i in holdingDF.index:
fund_id = holdingDF.iloc[i]['FUND_ID']
port_id = holdingDF.iloc[i]['PORT_ID']
l_date = holdingDF.iloc[i]['L_DATE']
account_id = holdingDF.iloc[i]['ACCOUNT_ID']
selectConstraint = [fund_id, port_id, l_date, account_id]
self.checkReload(db, selectConstraint)
insertList = []
for j in holdingDF.columns:
value = holdingDF.iloc[i][j]
if isinstance(value, unicode):
value = value.encode('UTF-8')
if type(value) == type(np.float64(0)):
value = value.item()
if math.isnan(value):
value = 'null'
if type(value) == type(None):
value = 'null'
insertList.append(value)
sql = '''insert into portHolding
(id,ACCOUNT_ID,ACCOUNT_NAME,AMOUNT,BEGIN_AMOUNT,BUY_AMOUNT,BUY_CASH,BUY_FEE,FUND_ID,L_DATE,MARKET_NO,MARKET_PRICE,MARKET_VALUE,NETASSET_PERCENT,NETTOTALASSET_PERCENT,PANDL,PORT_ID,POSITION_FLAG,SALE_AMOUNT,SALE_CASH,SALE_FEE,SECURITY_CODE,SECURITY_TYPE,SUBMARKET_NO,TOTAL_COST,UNIT_COST,WINDSECURITY_CODE,FCU,LCU)
VALUES(S_portHolding.Nextval,'{0[0]}','{0[1]}',{0[2]},{0[3]},{0[4]},{0[5]},{0[6]},'{0[7]}','{0[8]}','{0[9]}',{0[10]},{0[11]},{0[12]},{0[13]},{0[14]},'{0[15]}','{0[16]}',{0[17]},{0[18]},{0[19]},'{0[20]}','{0[21]}','{0[22]}',{0[23]},{0[24]},'{0[25]}',ora_login_user,ora_login_user)'''.format(
insertList)
tool.sqlDML(sql, db)
def deleteReload(self,db,deleteConstraint):
sql = '''insert into portAsset_bak
select * from portAsset
where fund_id = '{0[0]}' AND port_id = '{0[1]}' AND l_date = {0[2]}'''.format(deleteConstraint)
tool.sqlDML(sql,db)
sql='''delete from portAsset
where fund_id = '{0[0]}' AND port_id = '{0[1]}' AND l_date = {0[2]}'''.format(deleteConstraint)
tool.sqlDML(sql,db)
print('Delete Finish')
def deleteReload(self,db,deleteConstraint):
sql = '''insert into performance_statics_bak
select * from performance_statics
where fund_id = '{0[0]}' AND port_id = '{0[1]}' AND start_date = {0[2]} AND end_date = {0[3]}'''.format(deleteConstraint)
tool.sqlDML(sql,db)
sql='''delete from performance_statics
where fund_id = '{0[0]}' AND port_id = '{0[1]}' AND start_date = {0[2]} AND end_date = {0[3]}'''.format(deleteConstraint)
tool.sqlDML(sql,db)
print('Delete Finish')
def saveToDb(self):
#engine0=create_engine('oracle://*****:*****@T-wuc:1521/orcl')
#engine2=create_engine('oracle://*****:*****@localhost:1521/orcl')
#engine3=create_engine('oracle://*****:*****@virtualPC:1521/orcl')
#self.engine=engine3
db = tool.connectDB()
statsDF=pd.DataFrame()
for x in self.perf:
statsDF[x]=self.perf[x].stats
print statsDF
DOMTree = xml.dom.minidom.parse("fund_port_rel.xml")
mapping = DOMTree.documentElement
funds = mapping.getElementsByTagName("fund")
for col in statsDF.columns:
for index in statsDF.index:
if index != 'start' and index != 'end' :
insertList = []
FundID_PortID = tool.getFundID_PortID(funds,col)
insertList.append(FundID_PortID[0])
insertList.append(FundID_PortID[1])
insertList.append(tool.getDate(str(statsDF.loc['start'][col]),'(\d{4}-\d{2}-\d{2}).*'))
insertList.append(tool.getDate(str(statsDF.loc['end'][col]),'(\d{4}-\d{2}-\d{2}).*'))
insertList.append(index)
selectConstraint = [insertList[0],insertList[1],insertList[2],insertList[3],insertList[4]]
#self.checkReload(db,selectConstraint)
DOMTree = xml.dom.minidom.parse("viewConfig.xml")
performance = DOMTree.documentElement
indexs = performance.getElementsByTagName("index")
for i in indexs:
if index == i.getAttribute("content"):
language = i.getElementsByTagName('language')[0]
chinese = language.getElementsByTagName('chinese')[0]
insertList.append((chinese.childNodes[0].data).encode("utf-8"))
#print((chinese.childNodes[0].data).decode('utf-8'))
statics_value = statsDF.loc[index][col]
if type(statics_value) == type(np.float64(0)):
statics_value = statics_value.item()
if math.isnan(statics_value):
statics_value = 'null'
insertList.append(statics_value)
sql='''insert into performance_statics
(id,fund_id,port_id,start_date,end_date,indicator_name,chinese_name,statics_value,FCU,LCU)
VALUES(S_performance_statics.Nextval,'{0[0]}','{0[1]}','{0[2]}','{0[3]}','{0[4]}','{0[5]}',{0[6]},ora_login_user,ora_login_user)'''.format(insertList)
tool.sqlDML(sql,db)
#i = statsDF.loc[index][col]
#i = statsDF[statsDF.index == index][col]
#print(list(i)[0])
#self.deleteReload(db,['F00001','P00001','20150521','20150529'])
db.close()
def checkReload(self,db,selectConstraint):
sql='''select * from portHolding
where fund_id = '{0[0]}' AND port_id = '{0[1]}' AND l_date = {0[2]} AND ACCOUNT_ID = '{0[3]}' '''.format(selectConstraint)
rs = tool.sqlSelect(sql,db)
if rs != []:
sql='''update portHolding set LCD = sysdate, LCU = ora_login_user, DATA_STATUS = \'0\'
where fund_id = '{0[0]}' AND port_id = '{0[1]}' AND l_date = {0[2]} AND ACCOUNT_ID = '{0[3]}' '''.format(selectConstraint)
tool.sqlDML(sql,db)
#print('Exist')
else:
pass
def deleteReload(self, db, deleteConstraint):
sql = '''insert into performance_statics_bak
select * from performance_statics
where fund_id = '{0[0]}' AND port_id = '{0[1]}' AND start_date = {0[2]} AND end_date = {0[3]}'''.format(
deleteConstraint)
tool.sqlDML(sql, db)
sql = '''delete from performance_statics
where fund_id = '{0[0]}' AND port_id = '{0[1]}' AND start_date = {0[2]} AND end_date = {0[3]}'''.format(
deleteConstraint)
tool.sqlDML(sql, db)
print('Delete Finish')
def checkReload(self,db,selectConstraint):
sql='''select * from portAsset
where fund_id = '{0[0]}' AND port_id = '{0[1]}' AND l_date = {0[2]}'''.format(selectConstraint)
rs = tool.sqlSelect(sql,db)
#print(rs)
if rs != []:
sql='''update portAsset set LCD = sysdate, LCU = ora_login_user, DATA_STATUS = \'0\'
where fund_id = '{0[0]}' AND port_id = '{0[1]}' AND l_date = {0[2]}'''.format(selectConstraint)
tool.sqlDML(sql,db)
print('Exist')
else:
print('Not Exist')
print('Check Finish')
def checkReload(self, db, selectConstraint):
sql = '''select * from portHolding
where fund_id = '{0[0]}' AND port_id = '{0[1]}' AND l_date = {0[2]} AND ACCOUNT_ID = '{0[3]}' '''.format(
selectConstraint)
rs = tool.sqlSelect(sql, db)
if rs != []:
sql = '''update portHolding set LCD = sysdate, LCU = ora_login_user, DATA_STATUS = \'0\'
where fund_id = '{0[0]}' AND port_id = '{0[1]}' AND l_date = {0[2]} AND ACCOUNT_ID = '{0[3]}' '''.format(
selectConstraint)
tool.sqlDML(sql, db)
#print('Exist')
else:
pass
def checkReload(self,db,selectConstraint):
sql='''select * from performance_statics
where fund_id = '{0[0]}' AND port_id = '{0[1]}' AND start_date = {0[2]} AND end_date = {0[3]} AND indicator_name = '{0[4]}' '''.format(selectConstraint)
rs = tool.sqlSelect(sql,db)
#print(rs)
if rs != []:
sql='''update performance_statics set LCD = sysdate, LCU = ora_login_user, DATA_STATUS = \'0\'
where fund_id = '{0[0]}' AND port_id = '{0[1]}' AND start_date = {0[2]} AND end_date = {0[3]} AND indicator_name = '{0[4]}' '''.format(selectConstraint)
tool.sqlDML(sql,db)
print('Exist')
else:
print('Not Exist')
print('Check Finish')
def checkReload(self, db, selectConstraint):
sql = '''select * from performance_statics
where fund_id = '{0[0]}' AND port_id = '{0[1]}' AND start_date = {0[2]} AND end_date = {0[3]} AND indicator_name = '{0[4]}' '''.format(
selectConstraint)
rs = tool.sqlSelect(sql, db)
#print(rs)
if rs != []:
sql = '''update performance_statics set LCD = sysdate, LCU = ora_login_user, DATA_STATUS = \'0\'
where fund_id = '{0[0]}' AND port_id = '{0[1]}' AND start_date = {0[2]} AND end_date = {0[3]} AND indicator_name = '{0[4]}' '''.format(
selectConstraint)
tool.sqlDML(sql, db)
print('Exist')
else:
print('Not Exist')
print('Check Finish')
def insertToOracle(self,db,holdingDF):
for i in holdingDF.index:
fund_id = holdingDF.iloc[i]['FUND_ID']
port_id = holdingDF.iloc[i]['PORT_ID']
l_date = holdingDF.iloc[i]['L_DATE']
account_id = holdingDF.iloc[i]['ACCOUNT_ID']
selectConstraint = [fund_id,port_id,l_date,account_id]
self.checkReload(db,selectConstraint)
insertList = []
for j in holdingDF.columns:
value = holdingDF.iloc[i][j]
if isinstance(value,unicode):
value = value.encode('UTF-8')
if type(value) == type(np.float64(0)):
value = value.item()
if math.isnan(value):
value = 'null'
if type(value) == type(None):
value = 'null'
insertList.append(value)
sql='''insert into portHolding
(id,ACCOUNT_ID,ACCOUNT_NAME,AMOUNT,BEGIN_AMOUNT,BUY_AMOUNT,BUY_CASH,BUY_FEE,FUND_ID,L_DATE,MARKET_NO,MARKET_PRICE,MARKET_VALUE,NETASSET_PERCENT,NETTOTALASSET_PERCENT,PANDL,PORT_ID,POSITION_FLAG,SALE_AMOUNT,SALE_CASH,SALE_FEE,SECURITY_CODE,SECURITY_TYPE,SUBMARKET_NO,TOTAL_COST,UNIT_COST,WINDSECURITY_CODE,FCU,LCU)
VALUES(S_portHolding.Nextval,'{0[0]}','{0[1]}',{0[2]},{0[3]},{0[4]},{0[5]},{0[6]},'{0[7]}','{0[8]}','{0[9]}',{0[10]},{0[11]},{0[12]},{0[13]},{0[14]},'{0[15]}','{0[16]}',{0[17]},{0[18]},{0[19]},'{0[20]}','{0[21]}','{0[22]}',{0[23]},{0[24]},'{0[25]}',ora_login_user,ora_login_user)'''.format(insertList)
tool.sqlDML(sql,db)
def saveToDb(self):
#engine0=create_engine('oracle://*****:*****@T-wuc:1521/orcl')
#engine2=create_engine('oracle://*****:*****@localhost:1521/orcl')
#engine3=create_engine('oracle://*****:*****@virtualPC:1521/orcl')
#self.engine=engine3
db = tool.connectDB()
statsDF = pd.DataFrame()
for x in self.perf:
statsDF[x] = self.perf[x].stats
print statsDF
DOMTree = xml.dom.minidom.parse("fund_port_rel.xml")
mapping = DOMTree.documentElement
funds = mapping.getElementsByTagName("fund")
for col in statsDF.columns:
for index in statsDF.index:
if index != 'start' and index != 'end':
insertList = []
FundID_PortID = tool.getFundID_PortID(funds, col)
insertList.append(FundID_PortID[0])
insertList.append(FundID_PortID[1])
insertList.append(
tool.getDate(str(statsDF.loc['start'][col]),
'(\d{4}-\d{2}-\d{2}).*'))
insertList.append(
tool.getDate(str(statsDF.loc['end'][col]),
'(\d{4}-\d{2}-\d{2}).*'))
insertList.append(index)
selectConstraint = [
insertList[0], insertList[1], insertList[2],
insertList[3], insertList[4]
]
#self.checkReload(db,selectConstraint)
DOMTree = xml.dom.minidom.parse("viewConfig.xml")
performance = DOMTree.documentElement
indexs = performance.getElementsByTagName("index")
for i in indexs:
if index == i.getAttribute("content"):
language = i.getElementsByTagName('language')[0]
chinese = language.getElementsByTagName(
'chinese')[0]
insertList.append(
(chinese.childNodes[0].data).encode("utf-8"))
#print((chinese.childNodes[0].data).decode('utf-8'))
statics_value = statsDF.loc[index][col]
if type(statics_value) == type(np.float64(0)):
statics_value = statics_value.item()
if math.isnan(statics_value):
statics_value = 'null'
insertList.append(statics_value)
sql = '''insert into performance_statics
(id,fund_id,port_id,start_date,end_date,indicator_name,chinese_name,statics_value,FCU,LCU)
VALUES(S_performance_statics.Nextval,'{0[0]}','{0[1]}','{0[2]}','{0[3]}','{0[4]}','{0[5]}',{0[6]},ora_login_user,ora_login_user)'''.format(
insertList)
tool.sqlDML(sql, db)
#i = statsDF.loc[index][col]
#i = statsDF[statsDF.index == index][col]
#print(list(i)[0])
#self.deleteReload(db,['F00001','P00001','20150521','20150529'])
db.close()
