def _insert_log(self, status, msg):
values = [self._job_id]
date = time.strftime('%Y-%m-%d', time.localtime(time.time()))
nowtime = time.strftime('%Y-%m-%d %H:%M:%S',
time.localtime(time.time()))
values.append(date)
count_sql = 'select jobid from joblog where jobid=%s and date = %s'
insert_sql = 'INSERT INTO joblog (jobid, date, jobstatus,msg, createtime) VALUES (%s, %s, %s,%s,%s)'
update_sql = 'update joblog set jobstatus= %s,msg=%s,updatetime=%s where jobid=%s and date=%s'
count = client.execute(count_sql, values).fetchall()
if len(count) < 1:
# 插入
values.append(status)
values.append(msg)
values.append(nowtime)
client.execute(insert_sql, values)
else:
# 更新
updatevalue = list()
updatevalue.append(status)
updatevalue.append(msg)
updatevalue.append(nowtime)
updatevalue.append(self._job_id)
updatevalue.append(date)
client.execute(update_sql, updatevalue)
def insert_stock_index_db(data):
del_sql = "delete from stock_index"
client.execute(del_sql)
insert_sql = "insert into stock_index(code, name, industry, area, updatetime)values(%s, %s,%s,%s,%s)"
nowdate = datetime.datetime.now()
for index in data.index:
temp = data.ix[index]
values = [str(temp['code']), str(temp['name']), '指数', '指数', nowdate]
client.execute(insert_sql, values)
def get_fund_code():
"""
获取所有前十的基金代码
:return:
"""
sql = "select fcode, shortname from topicfund"
ret = client.execute(sql)
return ret.fetchall()
def insert_price_to_db(df, fundcode):
"""
dataFrame 基金收益数据入库操作
:param df: 基金收益数据
:param fundcode: 基金代码
:return:
"""
if df is not None:
client.execute("delete from fundprice where fundcode=%s", [fundcode])
insert_sql = "insert into fundprice(date,fundcode,price, updatetime)values(%s,%s,%s,%s)"
for index, row in df.iterrows():
values = list()
values.append(stamp_time(row['date']))
values.append(fundcode)
values.append(repr(row['price']))
values.append(
time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time())))
client.execute(insert_sql, values)
def insert_transaction(strategyid, data):
"""
插入分析指标
:param strategy: 策略名 不同的策略更新数据到相应的表中
:param data: data={'instrument':'sh000001','date':'2017-09-23'}
:return:
"""
del_sql ="delete from transaction where strategyid = %s"
insert_sql = "insert into transaction(strategyid, date, instrument, action, price, quantity, commission, createtime)" \
"values(%s, %s, %s, %s,%s, %s, %s,%s)"
client.execute(del_sql, [strategyid])
nowtime = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
for index in data.index:
temp = data.ix[index]
values = [strategyid, index, temp['instrument'], temp['action'], str(temp['price']),
str(temp['quantity']), str(temp['commission']), nowtime]
client.execute(insert_sql, values)
def insert_unitprice_to_db(df, code):
"""
基金单位净值入库
:param df:
:return:
"""
del_sql = "delete from fundunitprice where fundcode = %s"
insert_sql = "insert into fundunitprice(fundcode, date, unitprice, updatetime)values(%s, %s, %s, %s)"
nowtime = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
if df is not None:
client.execute(del_sql, [code])
for index, row in df.iterrows():
values = list()
values.append(code)
values.append(stamp_time(row['date']))
values.append(repr(row['unitprice']))
values.append(nowtime)
client.execute(insert_sql, values)
def insert_stock_element_db(element_type, data):
del_sql = "delete from stock_element"
client.execute(del_sql)
insert_sql = "insert into stock_element(type, code, name, weight, createtime)values(%s, %s,%s,%s,%s)"
nowdate = datetime.datetime.now()
for index in data.index:
temp = data.ix[index]
if element_type == "50":
values = [
element_type,
str(temp['code']), temp['name'], 0.00, nowdate
]
else:
values = [
element_type,
str(temp['code']), temp['name'],
str(temp['weight']), nowdate
]
client.execute(insert_sql, values)
def insert_analyzer(strategyid, data):
"""
插入分析指标
:param strategy: 策略名 不同的策略更新数据到相应的表中
:param data: data={'instrument':'sh000001','date':'2017-09-23'}
:return:
"""
del_sql ="delete from analyzer where strategyid = %s"
insert_sql = "insert into analyzer(strategyid, date, current_returns, total_returns," \
"LongestDrawDownDuration, MaxDrawDown, sharpe, beta,alpha, baserate, createtime)" \
"values(%s, %s, %s, %s,%s, %s,%s, %s, %s,%s,%s)"
client.execute(del_sql, [strategyid])
nowtime = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))
for index in data.index:
temp = data.ix[index]
values = [strategyid, index.strftime('%Y-%m-%d'), str(temp['current_returns']), str(temp['total_returns']),
str(temp['LongestDrawDownDuration']), str(temp['MaxDrawDown']), str(temp['sharpe']), str(temp['beta']),
str(temp['alpha']), str(temp['baseRet']), nowtime]
client.execute(insert_sql, values)
def get_job(jobid):
config = dict()
sql = "select jobid,path,class from jobconfig where jobtype ='python' and jobid=%s"
ret = client.execute(sql, [jobid]).fetchall()
if len(ret) < 1:
raise Exception('配置信息不存在')
else:
data = ret[0]
config['jobid'] = str(data[0]).encode('utf-8')
config['pyth'] = str(data[1]).encode('utf-8')
config['class'] = str(data[2]).encode('utf-8')
return config
def insert_top_to_db(data):
"""
基金行情插入操作
:param data: DataFrame 类型
:return:
"""
del_sql = "delete from topicfund"
if data is not None:
client.execute(del_sql)
for index, row in data.iterrows():
sql_str = "insert into topicfund("
colnames = "("
values = []
for col_name in data.columns:
sql_str = sql_str + col_name + ","
colnames = colnames + "%s ,"
values.append(fill(row[col_name]))
values.append(
time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time())))
sql_str = sql_str + "updatetime)" + "values" + colnames + "%s)"
client.execute(sql_str, values)