def add_trade_agent_status_detail():
info = InitTest.add_stg_run_info()
init_cash = 1000000
timestamp_curr = pd.Timestamp(str_2_datetime('2018-12-14 13:24:35'))
status = TradeAgentStatusDetail.create_t_1(info.stg_run_id, ExchangeName.DataIntegration, init_cash, timestamp_curr=timestamp_curr)
with with_db_session(engine_ibats, expire_on_commit=False) as session:
session.add(status)
session.commit()
return info, status
def test_update_by_pos_status_detail(self):
info, status = TradeAgentStatusDetailTest.add_trade_agent_status_detail()
pos_status = TradeAgentStatusDetailTest.add_pos_status_detail(info)
timestamp_curr = pd.Timestamp(str_2_datetime('2018-12-15 13:24:35'))
status2 = status.update_by_pos_status_detail({pos_status.symbol: pos_status}, timestamp_curr=timestamp_curr)
self.assertIsInstance(status2, TradeAgentStatusDetail)
self.assertEqual(status2.stg_run_id, info.stg_run_id)
self.assertGreater(status2.trade_agent_status_detail_idx, status.trade_agent_status_detail_idx)
self.assertEqual(status2.cash_available, status2.cash_init - pos_status.margin - pos_status.commission)
self.assertEqual(status2.position_value, pos_status.position_value)
self.assertEqual(status2.curr_margin, pos_status.margin)
self.assertEqual(status2.close_profit, 0)
self.assertEqual(status2.position_profit, pos_status.floating_pl)
self.assertEqual(status2.floating_pl_cum, pos_status.floating_pl_cum)
self.assertEqual(status2.commission_tot, pos_status.commission)
self.assertEqual(status2.cash_init, status.cash_init)
self.assertEqual(status2.cash_and_margin, status2.cash_available + status2.curr_margin)
self.assertEqual(status2.cashflow_daily, - pos_status.margin - pos_status.commission)
self.assertEqual(status2.cashflow_cum, status.cashflow_cum + status2.cashflow_daily)
def get_tdx_tick(code, date_str):
"""
调用pytdx接口获取股票tick数据
:param code:
:param date_str:
:return:
"""
position, data_list = 0, []
with api.connect(primary_ip, hot_backup_ip):
if code[0] == '6':
df = api.to_df(api.get_history_transaction_data(TDXParams.MARKET_SH, code, position, 30000, int(date_str)))
else:
df = api.to_df(api.get_history_transaction_data(TDXParams.MARKET_SZ, code, position, 30000, int(date_str)))
data_list.insert(0, df)
datetime0925 = str_2_datetime(date_str + '09:25', '%Y%m%d%H:%M')
datetime0930 = str_2_datetime(date_str + '09:30', '%Y%m%d%H:%M')
while len(df) > 0 and str_2_datetime(date_str + df.time[0], '%Y%m%d%H:%M') > datetime0925:
position = position + len(df)
if code[0] == '6':
df = api.to_df(api.get_history_transaction_data(TDXParams.MARKET_SH, code, position, 30000, int(date_str)))
else:
df = api.to_df(api.get_history_transaction_data(TDXParams.MARKET_SZ, code, position, 30000, int(date_str)))
if df is not None and len(df) > 0:
data_list.insert(0, df)
if str_2_datetime(date_str + df.time[0], '%Y%m%d%H:%M') == datetime0925 or \
str_2_datetime(date_str + df.time[0], '%Y%m%d%H:%M') == datetime0930:
break
else:
break
if code[0] == 6:
code = code + '.SH'
else:
code = code + '.SZ'
if len(data_list) == 0:
return None
data_df = pd.concat(data_list)
if data_df.shape[0] == 0:
return None
trade_date = data_df.time.apply(lambda x: str_2_datetime(date_str + x, '%Y%m%d%H:%M'))
data_df.insert(0, 'ts_code', code)
data_df.insert(1, 'date', date_str)
data_df.insert(2, 'trade_date', trade_date)
data_df.index = range(len(data_df))
data_df.index.rename('index', inplace=True)
data_df.reset_index(inplace=True)
return data_df
def import_coinbar_on_freq_min(freq, code_set=None, base_begin_time=None):
"""
抓取 日级别以上数据[ 60min, 30min, 15min, 5min, 1min ]级别
:param freq:
:param code_set:
:param base_begin_time:
:return:
"""
if base_begin_time is not None and not isinstance(base_begin_time, date):
base_begin_time = str_2_date(base_begin_time)
table_name = 'tushare_coin_md_' + freq
info_table_name = 'tushare_coin_pair_info'
has_table = engine_md.has_table(table_name)
if has_table:
sql_str = """SELECT exchange, exchange_pair, date_frm,
if(delist_date<end_date, delist_date, end_date) date_to
FROM
(
SELECT info.exchange, info.exchange_pair,
ifnull(trade_date_max_1, adddate(trade_date_latest,1)) date_frm,
delist_date,
if(hour(now())<8, subdate(curdate(),2), subdate(curdate(),1)) end_date
FROM
(
select exchange, exchange_pair,
ifnull(trade_date_latest_{freq},'2010-01-01') trade_date_latest,
delist_date_{freq} delist_date
from {info_table_name}
) info
LEFT OUTER JOIN
(SELECT exchange, symbol, adddate(max(`date`),1) trade_date_max_1
FROM {table_name} GROUP BY exchange, symbol) daily
ON info.exchange = daily.exchange
AND info.exchange_pair = daily.symbol
) tt
WHERE date_frm <= if(delist_date<end_date, delist_date, end_date)
ORDER BY exchange, exchange_pair""".format(
table_name=table_name, info_table_name=info_table_name, freq=freq)
else:
sql_str = """SELECT exchange, exchange_pair, date_frm,
if(delist_date<end_date, delist_date, end_date) date_to
FROM
(
SELECT exchange, exchange_pair,
ifnull(trade_date_latest_{freq},date('2010-01-01')) date_frm,
delist_date_{freq} delist_date,
if(hour(now())<8, subdate(curdate(),2), subdate(curdate(),1)) end_date
FROM {info_table_name} info
ORDER BY exchange, exchange_pair
) tt
WHERE date_frm <= if(delist_date<end_date, delist_date, end_date)
ORDER BY exchange, exchange_pair""".format(
info_table_name=info_table_name, freq=freq)
logger.warning('%s 不存在，仅使用 %s 表进行计算日期范围', table_name, info_table_name)
with with_db_session(engine_md) as session:
# 获取每只股票需要获取日线数据的日期区间
table = session.execute(sql_str)
# 获取每只股票需要获取日线数据的日期区间
code_date_range_dic = {
(exchange, symbol): (date_from if base_begin_time is None else min(
[date_from, base_begin_time]), date_to)
for exchange, symbol, date_from, date_to in table.fetchall()
if code_set is None or (exchange, symbol) in code_set
}
# 设置 dtype
dtype = {
'exchange': String(60),
'symbol': String(60),
'date': Date,
'datetime': DateTime,
'open': DOUBLE,
'high': DOUBLE,
'low': DOUBLE,
'close': DOUBLE,
'vol': DOUBLE,
}
# 更新 info 表 trade_date_latest 字段
trade_date_latest_list = []
update_trade_date_latest_str = """UPDATE tushare_coin_pair_info info
SET info.trade_date_latest_daily = :trade_date_latest
WHERE info.exchange = :exchange AND exchange_pair=:exchange_pair"""
data_df_list, data_count, tot_data_count, code_count = [], 0, 0, len(
code_date_range_dic)
try:
for num, ((exchange, exchange_pair),
(begin_time,
end_time)) in enumerate(code_date_range_dic.items(),
start=1):
begin_time_str = date_2_str(begin_time, DATE_FORMAT_STR)
end_time_str = date_2_str(end_time, DATE_FORMAT_STR)
logger.debug('%d/%d) %s %s [%s - %s]', num, code_count, exchange,
exchange_pair, begin_time, end_time)
try:
# data_df = pro.coinbar(exchange='huobi', symbol='gxsbtc', freq='1min', start_date='20180701', end_date='20180801')
data_df = pro.coinbar(exchange=exchange,
symbol=exchange_pair,
freq=freq,
start_date=begin_time_str,
end_date=end_time_str)
except Exception as exp:
if len(exp.args) >= 1 and exp.args[0] == '系统内部错误':
trade_date_latest_list.append({
'exchange':
exchange,
'exchange_pair':
exchange_pair,
'trade_date_latest':
'2020-02-02',
})
logger.warning(
"coinbar(exchange='%s', symbol='%s', freq='%s', start_date='%s', end_date='%s') 系统内部错误",
exchange, exchange_pair, freq, begin_time_str,
end_time_str)
continue
logger.exception(
"coinbar(exchange='%s', symbol='%s', freq='%s', start_date='%s', end_date='%s')",
exchange, exchange_pair, freq, begin_time_str,
end_time_str)
raise exp from exp
if data_df is not None and data_df.shape[0] > 0:
data_count += data_df.shape[0]
data_df['exchange'] = exchange
data_df['datetime'] = data_df['date']
data_df['date'] = data_df['date'].apply(
lambda x: str_2_datetime(x).date())
data_df_list.append(data_df)
# 记录最新交易日变化
trade_date_latest_list.append({
'exchange': exchange,
'exchange_pair': exchange_pair,
'trade_date_latest': end_time_str,
})
# 大于阀值有开始插入
if data_count >= 10000:
data_df_all = pd.concat(data_df_list)
# data_df_all.to_sql(table_name, engine_md, if_exists='append', index=False, dtype=dtype)
data_count = bunch_insert_on_duplicate_update(
data_df_all, table_name, engine_md, dtype)
tot_data_count += data_count
data_df_list, data_count = [], 0
# 更新 info 表 trade_date_latest 字段
with with_db_session(engine_md) as session:
result = session.execute(update_trade_date_latest_str,
params=trade_date_latest_list)
update_count = result.rowcount
session.commit()
logger.info('更新 %d 条交易对的最新交易 %s 信息', update_count, freq)
trade_date_latest_list = []
# 仅调试使用
if DEBUG and len(data_df_list) > 1:
break
finally:
if data_count > 0:
data_df_all = pd.concat(data_df_list)
# data_df_all.to_sql(table_name, engine_md, if_exists='append', index=False, dtype=dtype)
data_count = bunch_insert_on_duplicate_update(
data_df_all, table_name, engine_md, dtype)
tot_data_count += data_count
# 更新 info 表 trade_date_latest 字段
if len(trade_date_latest_list) > 0:
with with_db_session(engine_md) as session:
result = session.execute(update_trade_date_latest_str,
params=trade_date_latest_list)
update_count = result.rowcount
session.commit()
logger.info('更新 %d 条交易对的最新交易日信息', update_count)
if not has_table and engine_md.has_table(table_name):
alter_table_2_myisam(engine_md, [table_name])
# build_primary_key([table_name])
create_pk_str = """ALTER TABLE {table_name}
CHANGE COLUMN `exchange` `exchange` VARCHAR(60) NOT NULL FIRST,
CHANGE COLUMN `symbol` `symbol` VARCHAR(60) NOT NULL AFTER `exchange`,
CHANGE COLUMN `datetime` `datetime` DATETIME NOT NULL AFTER `symbol`,
ADD PRIMARY KEY (`exchange`, `symbol`, `datetime`)""".format(
table_name=table_name)
with with_db_session(engine_md) as session:
session.execute(create_pk_str)
logging.info("更新 %s 完成 新增数据 %d 条", table_name, tot_data_count)
def import_future_min(chain_param=None,
wind_code_set=None,
begin_time=None,
recent_n_years=2):
"""
更新期货合约分钟级别行情信息
请求语句类似于：
THS_HF('CU2105.SHF','open;high;low;close;volume;amount;change;changeRatio;sellVolume;buyVolume;openInterest',
'Fill:Original','2021-01-18 09:15:00','2021-01-18 15:15:00')
:param chain_param: 在celery 中將前面結果做爲參數傳給後面的任務
:param wind_code_set: 只道 ths_code 集合
:param begin_time: 最早的起始日期
:param recent_n_years: 忽略n年前的合约
:return:
"""
# global DEBUG
# DEBUG = True
table_name = "ifind_future_min"
logger.info("更新 %s 开始", table_name)
has_table = engine_md.has_table(table_name)
param_list = [
("open", DOUBLE),
("high", DOUBLE),
("low", DOUBLE),
("close", DOUBLE),
("volume", DOUBLE),
("amount", DOUBLE),
("change", DOUBLE),
("changeRatio", DOUBLE),
("sellVolume", DOUBLE),
("buyVolume", DOUBLE),
("openInterest", DOUBLE),
]
ifind_indicator_str = ";".join([key for key, _ in param_list])
if has_table:
sql_str = f"""
select ths_code, date_frm, if(lasttrade_date<end_date, lasttrade_date, end_date) date_to
FROM
(
select fi.ths_code,
ifnull(trade_date_max_1, addtime(ths_start_trade_date_future,'09:00:00')) date_frm,
addtime(ths_last_td_date_future,'15:00:00') lasttrade_date,
case
when hour(now())>=23 then DATE_FORMAT(now(),'%Y-%m-%d 23:00:00')
when hour(now())>=15 then DATE_FORMAT(now(),'%Y-%m-%d 15:00:00')
when hour(now())>=12 then DATE_FORMAT(now(),'%Y-%m-%d 12:00:00')
else DATE_FORMAT(now(),'%Y-%m-%d 03:00:00')
end end_date
from ifind_future_info fi
left outer join
(
select ths_code, addtime(max(trade_datetime),'00:00:01') trade_date_max_1
from {table_name} group by ths_code
) wfd
on fi.ths_code = wfd.ths_code
) tt
where date_frm <= if(lasttrade_date<end_date, lasttrade_date, end_date)
-- and subdate(curdate(), 360) < if(lasttrade_date<end_date, lasttrade_date, end_date)
order by date_to desc, date_frm"""
else:
sql_str = """
SELECT ths_code, date_frm,
if(lasttrade_date<end_date,lasttrade_date, end_date) date_to
FROM
(
SELECT info.ths_code,
addtime(ths_start_trade_date_future,'09:00:00') date_frm,
addtime(ths_last_td_date_future,'15:00:00') lasttrade_date,
case
when hour(now())>=23 then DATE_FORMAT(now(),'%Y-%m-%d 23:00:00')
when hour(now())>=15 then DATE_FORMAT(now(),'%Y-%m-%d 15:00:00')
when hour(now())>=12 then DATE_FORMAT(now(),'%Y-%m-%d 12:00:00')
else DATE_FORMAT(now(),'%Y-%m-%d 03:00:00')
end end_date
FROM ifind_future_info info
) tt
WHERE date_frm <= if(lasttrade_date<end_date, lasttrade_date, end_date)
ORDER BY date_to desc, date_frm"""
logger.warning('%s 不存在，仅使用 wind_future_info 表进行计算日期范围', table_name)
with with_db_session(engine_md) as session:
table = session.execute(sql_str)
# 获取date_from,date_to，将date_from,date_to做为value值
future_date_dic = {
ths_code: (str_2_datetime(date_from) if begin_time is None else
min([str_2_datetime(date_from), begin_time]),
str_2_datetime(date_to))
for ths_code, date_from, date_to in table.fetchall()
if wind_code_set is None or ths_code in wind_code_set
}
# 设置 dtype
dtype = {key: val for key, val in param_list}
dtype['ths_code'] = String(20)
dtype['instrument_id'] = String(20)
dtype['trade_date'] = Date
dtype['trade_datetime'] = DateTime
# 定义统一的插入函数
def insert_db(df: pd.DataFrame):
insert_data_count = bunch_insert_on_duplicate_update(
df,
table_name,
engine_md,
dtype=dtype,
primary_keys=['ths_code', 'trade_datetime'],
schema=config.DB_SCHEMA_MD)
return insert_data_count
data_df_list = []
future_count = len(future_date_dic)
bulk_data_count, tot_data_count = 0, 0
# 忽略更早的历史合约
ignore_before = pd.to_datetime(date.today() -
timedelta(days=int(365 * recent_n_years))
) if recent_n_years is not None else None
try:
logger.info("%d future instrument will be handled", future_count)
for num, (ths_code, (date_frm,
date_to)) in enumerate(future_date_dic.items(),
start=1):
# 暂时只处理 RU 期货合约信息
# if ths_code.find('RU') == -1:
# continue
if not (0 <= (date_to - date_frm).days < 800):
continue
if ignore_before is not None and pd.to_datetime(
date_frm) < ignore_before:
# 忽略掉 n 年前的合约
continue
if isinstance(date_frm, datetime):
date_frm_str = date_frm.strftime(STR_FORMAT_DATETIME)
elif isinstance(date_frm, str):
date_frm_str = date_frm
else:
date_frm_str = date_frm.strftime(STR_FORMAT_DATE) + ' 09:00:00'
# 结束时间到次日的凌晨5点
if isinstance(date_frm, datetime):
date_to_str = date_to.strftime(STR_FORMAT_DATETIME)
elif isinstance(date_to, str):
date_to_str = date_to
else:
date_to += timedelta(days=1)
date_to_str = date_to.strftime(STR_FORMAT_DATE) + ' 03:00:00'
logger.info('%d/%d) get %s between %s and %s', num, future_count,
ths_code, date_frm_str, date_to_str)
try:
data_df = invoker.THS_HighFrequenceSequence(
ths_code, ifind_indicator_str, 'Fill:Original',
date_frm_str, date_to_str)
except APIError as exp:
from tasks.ifind import ERROR_CODE_MSG_DIC, NO_BREAK_ERROR_CODE
error_code = exp.ret_dic.setdefault('error_code', 0)
if error_code in ERROR_CODE_MSG_DIC:
logger.warning("%d/%d) %s 执行异常 error_code=%d, %s", num,
future_count, ths_code, error_code,
ERROR_CODE_MSG_DIC[error_code])
else:
logger.exception("%d/%d) %s 执行异常 error_code=%d", num,
future_count, ths_code, error_code)
if error_code in NO_BREAK_ERROR_CODE:
continue
else:
break
if data_df is None:
logger.warning('%d/%d) %s has no data during %s %s', num,
future_count, ths_code, date_frm_str, date_to)
continue
logger.info('%d/%d) %d data of %s between %s and %s', num,
future_count, data_df.shape[0], ths_code, date_frm_str,
date_to)
# data_df['ths_code'] = ths_code
data_df.rename(columns={
'time': 'trade_datetime',
'thscode': 'ths_code',
},
inplace=True)
data_df['trade_date'] = pd.to_datetime(
data_df['trade_datetime']).apply(lambda x: x.date())
data_df.rename(columns={c: str.lower(c)
for c in data_df.columns},
inplace=True)
data_df['instrument_id'] = ths_code.split('.')[0]
data_df_list.append(data_df)
bulk_data_count += data_df.shape[0]
# 仅仅调试时使用
if DEBUG and len(data_df_list) >= 1:
break
if bulk_data_count > 50000:
logger.info('merge data with %d df %d data', len(data_df_list),
bulk_data_count)
data_df = pd.concat(data_df_list)
tot_data_count = insert_db(data_df)
logger.info("更新 %s，累计 %d 条记录被更新", table_name, tot_data_count)
data_df_list = []
bulk_data_count = 0
finally:
data_df_count = len(data_df_list)
if data_df_count > 0:
logger.info('merge data with %d df %d data', len(data_df_list),
bulk_data_count)
data_df = pd.concat(data_df_list)
tot_data_count += insert_db(data_df)
logger.info("更新 %s 结束 累计 %d 条记录被更新", table_name, tot_data_count)
def import_coin_latest(chain_param=None, ):
"""插入最新价格数据到 cmc_coin_pro_latest """
table_name = 'cmc_coin_pro_latest'
has_table = engine_md.has_table(table_name)
# 设置 dtype
dtype = {
'id': Integer,
'name': String(60),
'slug': String(60),
'symbol': String(20),
'date_added': DATETIME,
'last_updated': DATETIME,
'market_cap': DOUBLE,
'circulating_supply': DOUBLE,
'max_supply': DOUBLE,
'num_market_pairs': DOUBLE,
'percent_change_1h': DOUBLE,
'percent_change_24h': DOUBLE,
'percent_change_7d': DOUBLE,
'price': DOUBLE,
'total_supply': DOUBLE,
'volume_24h': DOUBLE,
'cmc_rank': DOUBLE,
}
header = {
'Content-Type': 'application/json',
'X-CMC_PRO_API_KEY': config.CMC_PRO_API_KEY
}
params = {
# 'CMC_PRO_API_KEY': config.CMC_PRO_API_KEY,
'limit': 5000,
'start': 1
}
# https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest?sort=market_cap&start=0&limit=10&cryptocurrency_type=tokens&convert=USD,BTC
url = "https://pro-api.coinmarketcap.com/v1/cryptocurrency/listings/latest"
rsp = requests.get(url=url, params=params, headers=header)
if rsp.status_code != 200:
logger.error('获取数据异常[%d] %s', rsp.status_code, rsp.content)
return
ret_dic = rsp.json()
data_list = ret_dic['data']
data_dic_list = []
for dic in data_list:
data_dic = {}
for key, val in dic.items():
if key == 'quote':
for sub_key, sub_val in val['USD'].items():
data_dic[sub_key] = sub_val
else:
data_dic[key] = val
data_dic_list.append(data_dic)
data_df = pd.DataFrame(data_dic_list)
# 数据整理
data_df['date_added'] = data_df['date_added'].apply(lambda x: str_2_datetime(x, DATETIME_FORMAT_STR))
data_df['last_updated'] = data_df['last_updated'].apply(lambda x: str_2_datetime(x, DATETIME_FORMAT_STR))
data_count = bunch_insert_on_duplicate_update(data_df, table_name, engine_md, dtype=dtype)
logging.info("更新 %s 结束 %d 条信息被更新", table_name, data_count)
if not has_table and engine_md.has_table(table_name):
alter_table_2_myisam(engine_md, [table_name])
# build_primary_key([table_name])
create_pk_str = """ALTER TABLE {table_name}
CHANGE COLUMN `id` `id` VARCHAR(60) NOT NULL FIRST ,
CHANGE COLUMN `last_updated` `last_updated` DATETIME NOT NULL AFTER `id`,
ADD PRIMARY KEY (`id`, `last_updated`)""".format(table_name=table_name)
execute_sql(create_pk_str, engine_md)
