def get_metadata(self, fact_name: str) -> dict:
conn = Conn(self._db_name)
# 对应的trade_type 没有找到 就去spot类型里面找。
metadata = conn.query_one(
"SELECT * FROM factor_metadata"
" WHERE symbol = ? AND trade_type = ? AND contract_type = ?"
" AND `interval` = ? AND factor_name = ? ",
(self._symbol, self._trade_type, self._contract_type, self._interval, fact_name),
)
if metadata is not None:
return metadata
if self._contract_type != CONTRACT_TYPE_NONE:
metadata = conn.query_one(
"SELECT * FROM factor_metadata"
" WHERE symbol = ? AND trade_type = ? AND contract_type = ?"
" AND `interval` = ? AND factor_name = ? ",
(self._symbol, self._trade_type, CONTRACT_TYPE_NONE, self._interval, fact_name),
)
if metadata is not None:
return metadata
metadata = conn.query_one(
"SELECT * FROM factor_metadata"
" WHERE symbol = ? AND trade_type = ? AND contract_type = ?"
" AND `interval` = ? AND factor_name = ? ",
(self._symbol, TRADE_TYPE_SPOT, CONTRACT_TYPE_NONE, self._interval, fact_name),
)
if metadata is None:
raise RuntimeError("Can not find the meta in database. ")
return metadata
def _get_waiting_instance_id(self) -> int:
query_sql = """
SELECT id FROM {trade_type}_instance_strategy WHERE symbol = ? AND exchange = ? AND strategy = ?
AND status = ? AND wait_start_timestamp = ? ORDER BY id DESC LIMIT 1
"""
params = (
self["symbol"], self["exchange"], self["strategy"],
INSTANCE_STATUS_WAITING, 0,
)
if self["trade_type"] == TRADE_TYPE_FUTURE:
query_sql = """
SELECT id FROM {trade_type}_instance_strategy WHERE symbol = ? AND exchange = ?
AND contract_type = ? AND strategy = ? AND status = ? AND wait_start_timestamp = ? ORDER BY id DESC LIMIT 1
"""
params = (
self["symbol"], self["exchange"], self["contract_type"],
self["strategy"], INSTANCE_STATUS_WAITING, 0,
)
conn = Conn(self["db_name"])
one = conn.query_one(
query_sql.format(trade_type=self["trade_type"]),
params,
)
return one["id"] if one else 0
def _is_opened(self, wait_start_timestamp: int) -> bool:
conn = Conn(self["db_name"])
if self["trade_type"] == TRADE_TYPE_FUTURE:
opened = conn.query(
"SELECT id FROM future_instance_{mode} WHERE symbol = ? AND exchange = ? AND contract_type = ?"
" AND strategy = ? AND wait_start_timestamp = ? AND status > ?".format(
mode=MODE_BACKTEST if self["mode"] == MODE_BACKTEST else MODE_STRATEGY,
),
(
self["symbol"], self["exchange"], self["contract_type"], self["strategy"],
wait_start_timestamp, INSTANCE_STATUS_WAITING,
),
)
return len(opened) > 0
opened = conn.query(
"SELECT id FROM {trade_type}_instance_{mode} WHERE symbol = ? AND exchange = ? AND strategy = ? "
"AND wait_start_timestamp = ? AND status > ?".format(
trade_type=self["trade_type"],
mode=MODE_BACKTEST if self["mode"] == MODE_BACKTEST else MODE_STRATEGY,
),
(
self["symbol"], self["exchange"], self["strategy"],
wait_start_timestamp, INSTANCE_STATUS_WAITING,
),
)
return len(opened) > 0
def _get_risk_level(self, timestamp: int, instance_id: int) -> int:
conn = Conn(self["db_name"])
table_name = "{trade_type}_instance_backtest".format(
trade_type=self["trade_type"])
query_sql = """
SELECT id FROM {} WHERE backtest_id = ? AND symbol = ? AND exchange = ?
AND strategy = ? AND open_start_timestamp < ? AND (liquidate_finish_timestamp > ? OR status in (?,?))
ORDER BY open_start_timestamp, id
"""
params = (
self["backtest_id"],
self["symbol"],
self["exchange"],
self["strategy"],
timestamp,
timestamp,
INSTANCE_STATUS_OPENING,
INSTANCE_STATUS_LIQUIDATING,
)
instances = conn.query(
query_sql.format(table_name),
params,
)
instance_ids = [i["id"] for i in instances]
risk_level = len(instance_ids)
if instance_id in instance_ids:
risk_level = instance_ids.index(instance_id)
return risk_level
def _is_opened(self, wait_start_timestamp: int) -> bool:
conn = Conn(self["db_name"])
if self["trade_type"] == TRADE_TYPE_FUTURE:
opened = conn.query(
"SELECT id FROM future_instance_backtest WHERE backtest_id = ? AND symbol = ? AND exchange = ?"
" AND contract_type = ? AND strategy = ? AND wait_start_timestamp = ? AND status > ?",
(
self["backtest_id"],
self["symbol"],
self["exchange"],
self["contract_type"],
self["strategy"],
wait_start_timestamp,
INSTANCE_STATUS_WAITING,
),
)
return len(opened) > 0
opened = conn.query(
"SELECT id FROM {trade_type}_instance_backtest WHERE backtest_id = ? AND symbol = ? AND exchange = ? AND"
" strategy = ? AND wait_start_timestamp = ? AND status > ?".format(
trade_type=self["trade_type"]),
(
self["backtest_id"],
self["symbol"],
self["exchange"],
self["strategy"],
wait_start_timestamp,
INSTANCE_STATUS_WAITING,
),
)
return len(opened) > 0
def load(self, timestamp: int) -> dict:
sql = """
SELECT * FROM {} WHERE exchange = ? AND settle_mode = ? AND settle_currency = ?
AND timestamp <= ? ORDER BY timestamp DESC, id DESC LIMIT 1""".format(
self._asset_table_name)
params = (
self._exchange,
self._settle_mode,
self._settle_currency,
timestamp,
)
if self._mode == MODE_BACKTEST:
sql = """
SELECT * FROM {} WHERE exchange = ? AND settle_mode = ? AND settle_currency = ?
AND timestamp <= ? AND backtest_id = ? ORDER BY timestamp DESC, id DESC LIMIT 1
""".format(self._asset_table_name)
params = (self._exchange, self._settle_mode, self._settle_currency,
timestamp, self._backtest_id)
conn = Conn(self._db_name)
result = conn.query_one(sql, params)
if result is None:
raise RuntimeError("you must init_amount before load the asset. ")
self["asset_total"] = result["asset_total"]
self["asset_sub"] = result["asset_sub"]
self["asset_freeze"] = result["asset_freeze"]
self["position_total"] = result["position_total"]
self["position_sub"] = result["position_sub"]
self["position_freeze"] = result["position_freeze"]
return self
def load_from_db(self, instance_id):
conn = Conn(self["db_name"])
tmp_instance = conn.query_one(
"SELECT * FROM {trade_type}_instance_{mode} WHERE id = ?".format(
trade_type=self["trade_type"],
mode=MODE_BACKTEST if self["mode"] == MODE_BACKTEST else MODE_STRATEGY,
),
(instance_id,),
)
if tmp_instance is None:
raise RuntimeError("the instance is None. ")
self["id"] = tmp_instance["id"]
self["status"] = tmp_instance["status"]
self["unit_amount"] = tmp_instance.get("unit_amount") or 1
self["asset_total"] = tmp_instance["asset_total"]
self["asset_freeze"] = tmp_instance["asset_freeze"]
self["param_position"] = tmp_instance["param_position"]
self["param_max_abs_loss_ratio"] = tmp_instance["param_max_abs_loss_ratio"]
self["wait_start_timestamp"] = tmp_instance["wait_start_timestamp"]
self["wait_start_datetime"] = tmp_instance["wait_start_datetime"]
self["wait_finish_timestamp"] = tmp_instance["wait_finish_timestamp"]
self["wait_finish_datetime"] = tmp_instance["wait_finish_datetime"]
self["open_times"] = tmp_instance["open_times"]
self["open_start_timestamp"] = tmp_instance["open_start_timestamp"]
self["open_start_datetime"] = tmp_instance["open_start_datetime"]
self["open_finish_timestamp"] = tmp_instance["open_finish_timestamp"]
self["open_finish_datetime"] = tmp_instance["open_finish_datetime"]
self["open_expired_timestamp"] = tmp_instance["open_expired_timestamp"]
self["open_expired_datetime"] = tmp_instance["open_expired_datetime"]
self["liquidate_times"] = tmp_instance["liquidate_times"]
self["liquidate_start_timestamp"] = tmp_instance["liquidate_start_timestamp"]
self["liquidate_start_datetime"] = tmp_instance["liquidate_start_datetime"]
self["liquidate_finish_timestamp"] = tmp_instance["liquidate_finish_timestamp"]
self["liquidate_finish_datetime"] = tmp_instance["liquidate_finish_datetime"]
param = Param(
{},
trade_type=self["trade_type"],
db_name=self["db_name"],
mode=self["mode"],
)
param.load(instance_id)
self["param"] = param
indices = Indices(
{},
trade_type=self["trade_type"],
db_name=self["db_name"],
mode=self["mode"],
)
indices.load(instance_id)
self["indices"] = indices
def _value_by_id(self, signal_id: int, timestamp: int):
conn = Conn(self._db_name)
dataset = conn.query_one(
"SELECT * FROM signal_dataset WHERE signal_id = ? AND start_timestamp <= ? AND finish_timestamp > ?"
" ORDER BY start_timestamp DESC LIMIT 1",
(signal_id, timestamp, timestamp),
)
return dataset
def _values_by_id(self, fact_id: int, start_timestamp: int, finish_timestamp: int) -> List[float]:
conn = Conn(self._db_name)
fact_values = conn.query(
"SELECT factor_value FROM factor_dataset"
" WHERE factor_id = ? AND timestamp >= ? AND timestamp <= ? ORDER BY timestamp",
(fact_id, start_timestamp, finish_timestamp),
)
return [f["factor_value"] for f in fact_values]
def _get_previous_instances(self, start_timestamp=0, finish_timestamp=0):
if start_timestamp == 0:
raise RuntimeError("start_timestamp must bigger than 0. ")
if finish_timestamp == 0:
finish_timestamp = moment.now().millisecond_timestamp
conn = Conn(self["db_name"])
table_name = "{trade_type}_instance_{mode}".format(
trade_type=self["trade_type"],
mode=MODE_BACKTEST if self["mode"] == MODE_BACKTEST else MODE_STRATEGY,
)
if self["trade_type"] == TRADE_TYPE_FUTURE:
query_sql = """
SELECT * FROM {} WHERE symbol = ? AND exchange = ? AND contract_type = ? AND strategy = ?
AND wait_start_timestamp >= ? AND wait_start_timestamp < ? AND status != ? ORDER BY wait_start_timestamp
""".format(table_name)
query_param = (
self["symbol"], self["exchange"], self["contract_type"], self["strategy"],
start_timestamp, finish_timestamp, INSTANCE_STATUS_WAITING,
)
if self["mode"] == MODE_BACKTEST:
query_sql = """
SELECT * FROM {} WHERE symbol = ? AND exchange = ? AND contract_type = ?
AND strategy = ? AND wait_start_timestamp >= ? AND wait_start_timestamp < ?
AND backtest_id = ? AND status != ? ORDER BY wait_start_timestamp
""".format(table_name)
query_param = (
self["symbol"], self["exchange"], self["contract_type"], self["strategy"],
start_timestamp, finish_timestamp, self["backtest_id"], INSTANCE_STATUS_WAITING,
)
else:
query_sql = """
SELECT * FROM {} WHERE symbol = ? AND exchange = ? AND strategy = ?
AND wait_start_timestamp >= ? AND wait_start_timestamp < ? AND status != ?
ORDER BY wait_start_timestamp
""".format(table_name)
query_param = (
self["symbol"], self["exchange"], self["strategy"],
start_timestamp, finish_timestamp, INSTANCE_STATUS_WAITING,
)
if self["mode"] == MODE_BACKTEST:
query_sql = """
SELECT * FROM {} WHERE symbol = ? AND exchange = ? AND strategy = ?
AND wait_start_timestamp >= ? AND wait_start_timestamp < ?
AND backtest_id = ? AND status != ? ORDER BY wait_start_timestamp
""".format(table_name)
query_param = (
self["symbol"], self["exchange"], self["strategy"],
start_timestamp, finish_timestamp, self["backtest_id"], INSTANCE_STATUS_WAITING,
)
return conn.query(query_sql, query_param)
def previous_signals(self, signal_name: str, timestamp: int) -> List[dict]:
meta = self.get_metadata(signal_name)
signal_id = meta["signal_id"]
conn = Conn(self._db_name)
dataset = conn.query(
"SELECT * FROM signal_dataset WHERE signal_id = ? AND finish_timestamp < ?"
" ORDER BY finish_timestamp DESC LIMIT 5",
(signal_id, timestamp),
)
return dataset
def _value_by_id(self, fact_id: int, timestamp: int) -> float:
conn = Conn(self._db_name)
fact_data = conn.query_one(
"SELECT factor_value FROM factor_dataset"
" WHERE factor_id = ? AND timestamp <= ? ORDER BY timestamp DESC LIMIT 1",
(fact_id, timestamp),
)
if fact_data is None:
raise RuntimeError("Can not find the factor value, there have no value in database. ")
return fact_data["factor_value"]
def in_signal(self, signal_name: str, timestamp: int) -> bool:
meta = self.get_metadata(signal_name)
signal_id = meta["signal_id"]
conn = Conn(self._db_name)
dataset = conn.query_one(
"SELECT * FROM signal_dataset WHERE signal_id = ? AND start_timestamp >= ? AND finish_timestamp < ?"
" ORDER BY start_timestamp DESC LIMIT 1",
(signal_id, timestamp, timestamp),
)
return not (dataset is None)
def get_metadata(self, signal_name: str) -> dict:
conn = Conn(self._db_name)
metadata = conn.query_one(
"SELECT * FROM signal_metadata"
" WHERE symbol = ? AND exchange = ? AND trade_type = ? AND contract_type = ? AND signal_name = ? ",
(self._symbol, self._exchange, self._trade_type,
self._contract_type, signal_name),
)
if metadata is None:
raise RuntimeError("Can not find the meta in database. ")
return metadata
def _get_waiting_instance_id(self) -> int:
query_sql = """
SELECT id FROM {trade_type}_instance_backtest WHERE backtest_id = ? AND symbol = ? AND exchange = ?
AND strategy = ? AND status = ? AND wait_start_timestamp = ?
ORDER BY id DESC LIMIT 1
"""
insert_sql = """
INSERT INTO {trade_type}_instance_backtest
(backtest_id, symbol, exchange, strategy, status, wait_start_timestamp)
VALUES (?, ?, ?, ?, ?, ?)
"""
params = (
self["backtest_id"],
self["symbol"],
self["exchange"],
self["strategy"],
INSTANCE_STATUS_WAITING,
0,
)
if self["trade_type"] == TRADE_TYPE_FUTURE:
query_sql = """
SELECT id FROM {trade_type}_instance_backtest WHERE backtest_id = ? AND symbol = ? AND exchange = ?
AND contract_type = ? AND strategy = ? AND status = ? AND wait_start_timestamp = ?
ORDER BY id DESC LIMIT 1
"""
insert_sql = """
INSERT INTO {trade_type}_instance_backtest (backtest_id, symbol, exchange, contract_type, strategy, status,
wait_start_timestamp)
VALUES (?, ?, ?, ?, ?, ?, ?)
"""
params = (
self["backtest_id"],
self["symbol"],
self["exchange"],
self["contract_type"],
self["strategy"],
INSTANCE_STATUS_WAITING,
0,
)
conn = Conn(self["db_name"])
one = conn.query_one(
query_sql.format(trade_type=self["trade_type"]),
params,
)
if one:
return one["id"]
insert_id = conn.insert(
insert_sql.format(trade_type=self["trade_type"]),
params,
)
return insert_id
def _get_orders(self) -> List:
conn = Conn(self["db_name"])
orders = conn.query(
"SELECT * FROM {trade_type}_order_{mode} WHERE instance_id = ?"
" ORDER BY sequence".format(
trade_type=self["trade_type"],
mode=MODE_BACKTEST if self["mode"] == MODE_BACKTEST else MODE_STRATEGY,
),
(self["id"])
)
return orders
def load(self, instance_id):
conn = Conn(self._db_name)
results = conn.query(
"SELECT * FROM {} WHERE instance_id = ?".format(self._table_name),
(instance_id,),
)
for result in results:
if result["indices_type"] == INDICES_TYPE_INTEGER:
self[result["indices_name"]] = int(result["indices_value"])
elif result["indices_type"] == INDICES_TYPE_FLOAT:
self[result["indices_name"]] = float(result["indices_value"])
else:
self[result["indices_name"]] = result["indices_value"]
def __insert_asset_item(self, timestamp, datetime):
position_total, asset_total = self.__calculate_total(timestamp)
position_freeze, asset_freeze = self.__calculate_freeze(timestamp)
position_sub, asset_sub = self.__calculate_sub(timestamp)
conn = Conn(self._db_name)
one = conn.query_one(
"""SELECT * FROM {} WHERE exchange = ? AND settle_mode = ? AND settle_currency = ?
AND timestamp = ? AND backtest_id = ?""".format(
self._asset_table_name),
(self._exchange, self._settle_mode, self._settle_currency,
timestamp, self._backtest_id))
if one:
conn.execute(
"""
UPDATE {} SET asset_total = ?, asset_sub = ?, asset_freeze = ?,
position_total = ?, position_sub = ?, position_freeze = ? WHERE id = ?
""".format(self._asset_table_name),
(
asset_total,
asset_sub,
asset_freeze,
position_total,
position_sub,
position_freeze,
one["id"],
),
)
else:
conn.insert(
"""
INSERT INTO {} (exchange, settle_mode, settle_currency, backtest_id,
asset_total, asset_sub, asset_freeze,
position_total, position_sub, position_freeze,
timestamp, datetime) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
""".format(self._asset_table_name),
(
self._exchange,
self._settle_mode,
self._settle_currency,
self._backtest_id,
asset_total,
asset_sub,
asset_freeze,
position_total,
position_sub,
position_freeze,
timestamp,
datetime,
),
)
one = conn.query_one(
"""SELECT * FROM {} WHERE exchange = ? AND settle_mode = ? AND settle_currency = ?
AND timestamp > ? AND backtest_id = ? ORDER BY timestamp LIMIT 1"""
.format(self._asset_table_name),
(self._exchange, self._settle_mode, self._settle_currency,
timestamp, self._backtest_id))
if one:
self.__insert_asset_item(one["timestamp"], one["datetime"])
def __insert_account_flow_item(self, **kwargs):
"""
add record in account flow table
:param
subject: the item of account flow
amount: the amount of flow, the real amount * 100000000
position: the position of the flow
timestamp: the item of account flow
:return: None
"""
if self._mode != MODE_BACKTEST:
raise RuntimeError(
"Only backtest mode can insert data into table. ")
validate(instance=kwargs, schema=account_flow_input)
conn = Conn(self._db_name)
item = conn.query_one(
"""SELECT * FROM {} WHERE symbol = ? AND exchange = ? AND settle_mode = ? AND settle_currency = ?
AND subject = ? AND timestamp = ? AND backtest_id = ?""".format(
self._account_flow_table_name),
(
self._symbol,
self._exchange,
self._settle_mode,
self._settle_currency,
kwargs.get("subject"),
kwargs.get("timestamp"),
self._backtest_id,
),
)
conn.insert(
"""INSERT INTO {} (symbol, exchange, settle_mode, settle_currency, backtest_id,
subject, amount, position, timestamp, datetime) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
""".format(self._account_flow_table_name),
(
self._symbol,
self._exchange,
self._settle_mode,
self._settle_currency,
self._backtest_id,
kwargs.get("subject"),
kwargs.get("amount"),
kwargs.get("position"),
kwargs.get("timestamp") +
1 if item else kwargs.get("timestamp"),
kwargs.get("datetime"),
),
)
def __init__(self, kw):
super().__init__(kw)
validate(instance=kw, schema=strategy_input)
# 初始化各个组件
self["asset"] = Asset(
trade_type=self.get("trade_type"),
symbol=self.get("symbol"),
exchange=self.get("exchange"),
db_name=self.get("db_name_asset") or self.get("db_name"),
mode=self.get("mode"),
settle_mode=self.get("settle_mode") or SETTLE_MODE_BASIS,
backtest_id=self.get("backtest_id"),
)
self["indices"] = Indices(
self.get("indices") or {},
mode=self.get("mode"),
trade_type=self.get("trade_type"),
db_name=self.get("db_name"),
)
self["param"] = Param(
self["param"],
db_name=self.get("db_name_param") or self.get("db_name"),
mode=self.get("mode"),
trade_type=self.get("trade_type"),
)
instance_id = kw.get("id")
if instance_id is not None:
conn = Conn(self["db_name"])
instance = conn.query_one(
"SELECT * FROM {trade_type}_instance_{mode} WHERE id = ?".format(
trade_type=self["trade_type"],
mode=MODE_BACKTEST if self["mode"] == MODE_BACKTEST else MODE_STRATEGY,
),
(instance_id,),
)
self["strategy"] = instance["strategy"]
self["interval"] = instance["interval"]
self["unit_amount"] = instance["unit_amount"]
self["param"] = self["param"].load(instance_id)
self["indices"] = self["indices"].load(instance_id)
if self["trade_type"] == TRADE_TYPE_FUTURE:
self["lever"] = instance["lever"]
self["status"] = instance["status"]
def get_spot_value(self, factor_name: str, timestamp: int):
symbol = self._symbol
currencies = symbol.split("_")
if currencies[-1] in ("usdt", "usdc", "busd"):
symbol = currencies[0] + "_usd"
conn = Conn(self._db_name)
meta = conn.query_one(
"SELECT * FROM factor_metadata"
" WHERE symbol = ? AND trade_type = ? AND contract_type = ?"
" AND `interval` = ? AND factor_name = ? ",
(symbol, TRADE_TYPE_SPOT, CONTRACT_TYPE_NONE, self._interval, factor_name),
)
if meta is None:
return None
return self._value_by_id(meta["factor_id"], timestamp)
def save(self, instance_id):
if self._mode != MODE_BACKTEST:
raise RuntimeError("You only can save data in backtest mode")
# 入库前保证属性没有被篡改
validate(instance=self, schema=indices_input)
for name in self:
if isinstance(self[name], int):
sql_param = (PARAM_TYPE_INTEGER, str(self[name]), instance_id, name)
elif isinstance(self[name], float):
sql_param = (PARAM_TYPE_FLOAT, str(self[name]), instance_id, name)
else:
sql_param = (PARAM_TYPE_STRING, self[name], instance_id, name)
conn = Conn(self._db_name)
one = conn.query_one(
"SELECT * FROM {} WHERE instance_id = ? AND indices_name = ?".format(self._table_name),
(instance_id, name)
)
if one:
conn.execute(
"UPDATE {} SET indices_type = ?, indices_value = ?"
" WHERE instance_id = ? AND indices_name = ?".format(self._table_name),
sql_param,
)
else:
conn.insert(
"INSERT INTO {} (indices_type, indices_value, instance_id, indices_name)"
" VALUES (?, ?, ?, ?)".format(self._table_name),
sql_param,
)
def query_range(
self,
start_timestamp: int,
finish_timestamp: int,
interval: str,
standard: bool = False
):
conn = Conn(self.db_name)
params = (self.symbol, self.exchange, interval, start_timestamp, finish_timestamp)
if self.trade_type == TRADE_TYPE_FUTURE:
params = (self.symbol, self.exchange, self.contract_type, interval, start_timestamp, finish_timestamp)
candles = conn.query(self.sql, params)
conn.close() # 手动关闭链接。
for candle in candles:
yield self.__standard_candle(candle) if standard else candle
if len(candles) == 100:
yield from self.query_range(candles[-1]["timestamp"] + 1000, finish_timestamp, interval, standard=standard)
def _get_risk_level(self, timestamp: int, instance_id: int) -> int:
conn = Conn(self["db_name"])
table_name = "{trade_type}_instance_{mode}".format(
trade_type=self["trade_type"],
mode=MODE_BACKTEST if self["mode"] == MODE_BACKTEST else MODE_STRATEGY,
)
m = moment.get(timestamp).to(self.get("timezone") or "Asia/Shanghai").floor("day")
query_sql = """
SELECT id FROM {} WHERE symbol = ? AND exchange = ? AND strategy = ?
AND (status IN (?, ?, ?) OR ( status = ? AND liquidate_finish_timestamp > ? )) ORDER BY open_start_timestamp, id
"""
params = (
self["symbol"], self["exchange"], self["strategy"],
INSTANCE_STATUS_OPENING, INSTANCE_STATUS_LIQUIDATING, INSTANCE_STATUS_ERROR,
INSTANCE_STATUS_FINISHED, m.millisecond_timestamp,
)
if self["mode"] == MODE_BACKTEST:
query_sql = """
SELECT id FROM {} WHERE backtest_id = ? AND symbol = ? AND exchange = ?
AND strategy = ? AND open_start_timestamp < ? AND (liquidate_finish_timestamp > ? OR status in (?,?))
ORDER BY open_start_timestamp, id
"""
params = (
self["backtest_id"],
self["symbol"],
self["exchange"],
self["strategy"],
timestamp,
timestamp,
INSTANCE_STATUS_OPENING,
INSTANCE_STATUS_LIQUIDATING,
)
instances = conn.query(
query_sql.format(table_name),
params,
)
instance_ids = [i["id"] for i in instances]
risk_level = len(instance_ids)
if instance_id in instance_ids:
risk_level = instance_ids.index(instance_id)
return risk_level
def _values_by_sequence(
self,
fact_id: int,
start_timestamp: int,
finish_timestamp: int,
limit: int,
is_desc: bool,
) -> List[float]:
conn = Conn(self._db_name)
fact_values = conn.query(
"SELECT factor_value FROM factor_dataset"
" WHERE factor_id = ? AND timestamp >= ? AND timestamp <= ? ORDER BY factor_value {} LIMIT {}".format(
"DESC" if is_desc else "",
limit,
),
(fact_id, start_timestamp, finish_timestamp),
)
return [f["factor_value"] for f in fact_values]
def __calculate_total(self, timestamp: int) -> (float, float):
query_sql = """
SELECT SUM(position) AS position, SUM(amount)/100000000 AS amount FROM {} WHERE exchange = ?
AND settle_mode = ? AND settle_currency = ? AND backtest_id = ? AND timestamp <= ?
AND subject IN (?, ?, ?)""".format(self._account_flow_table_name)
query_param = (
self._exchange,
self._settle_mode,
self._settle_currency,
self._backtest_id,
timestamp,
SUBJECT_INVEST,
SUBJECT_DIVEST,
SUBJECT_SETTLE,
)
conn = Conn(self._db_name)
result = conn.query_one(query_sql, query_param)
position_total = result.get("position") or 20
asset_total = result.get("amount") or 0.0
return position_total, asset_total
def __calculate_sub(self, timestamp: int) -> (float, float):
query_sql = """
SELECT SUM(amount)/100000000 AS asset_sub, SUM(position) AS position_sub FROM {}
WHERE exchange = ? AND settle_mode = ? AND settle_currency = ? AND backtest_id = ?
AND timestamp <= ? AND subject IN (?, ?, ?)
""".format(self._account_flow_table_name)
query_param = (
self._exchange,
self._settle_mode,
self._settle_currency,
self._backtest_id,
timestamp,
SUBJECT_TRANSFER_IN,
SUBJECT_TRANSFER_OUT,
SUBJECT_SETTLE,
)
conn = Conn(self._db_name)
result = conn.query_one(query_sql, query_param)
position_sub, asset_sub = result["position_sub"], result["asset_sub"]
return position_sub, asset_sub
def __calculate_freeze(self, timestamp: int) -> (float, float):
query_sql = """
SELECT SUM(amount)/100000000 AS asset_freeze, SUM(position) AS position_freeze FROM {}
WHERE exchange = ? AND settle_mode = ? AND settle_currency = ? AND backtest_id = ?
AND timestamp <= ? AND subject IN (?, ?)
""".format(self._account_flow_table_name)
query_param = (
self._exchange,
self._settle_mode,
self._settle_currency,
self._backtest_id,
timestamp,
SUBJECT_FREEZE,
SUBJECT_UNFREEZE,
)
conn = Conn(self._db_name)
result = conn.query_one(query_sql, query_param)
position_freeze = -(result.get("position_freeze") or 0.0)
asset_freeze = -(result.get("asset_freeze") or 0.0)
return position_freeze, asset_freeze
def query_range_contracts(
self,
start_timestamp: int,
finish_timestamp: int,
interval: str,
standard: bool = False,
due_timestamp: int = 0, # 辅助参数
):
conn = Conn(self.db_name)
params = (start_timestamp, finish_timestamp, self.symbol, self.exchange, interval)
candles = conn.query(
"SELECT * FROM {} WHERE timestamp >= ? AND timestamp < ? AND symbol = ? AND exchange = ?"
" AND `interval` = ? ORDER BY timestamp, due_timestamp LIMIT 100".format(self.table_name),
params,
)
if due_timestamp:
tmp_candles = conn.query(
"SELECT * FROM {} WHERE timestamp = ? AND symbol = ? AND exchange = ? AND `interval` = ?"
" AND due_timestamp > ? ORDER BY due_timestamp".format(self.table_name),
(start_timestamp, self.symbol, self.exchange, interval, due_timestamp)
)
candles = tmp_candles + candles
conn.close() # 手动关闭链接。
for candle in candles:
yield self.__standard_candle(candle) if standard else candle
if len(candles) >= 100:
yield from self.query_range_contracts(
candles[-1]["timestamp"] + 1000,
finish_timestamp,
interval,
standard=standard,
due_timestamp=candles[-1].get("due_timestamp") or 0,
)
def first_invest(
self,
asset_total: float,
position_total: float,
position_sub: float,
) -> None:
m = moment.get(BIRTHDAY_BTC).to(
self.get("timezone") or "Asia/Shanghai")
query_sql = "SELECT * FROM {} WHERE exchange = ? AND settle_mode = ? AND settle_currency = ? AND subject = ?" \
" AND timestamp <= ? AND backtest_id = ? LIMIT 1".format(self._account_flow_table_name)
query_param = (
self._exchange,
self._settle_mode,
self._settle_currency,
SUBJECT_INVEST,
m.millisecond_timestamp,
self._backtest_id,
)
conn = Conn(self._db_name)
one = conn.query_one(query_sql, query_param)
if one:
return
self.__invest(
standard_number(asset_total),
position_total,
m.millisecond_timestamp,
m.format("YYYY-MM-DD HH:mm:ss"),
)
self.__transfer_in(
standard_number(asset_total * position_sub / position_total),
position_sub,
m.millisecond_timestamp,
m.format("YYYY-MM-DD HH:mm:ss"),
)
self.__insert_asset_item(
m.millisecond_timestamp,
m.format("YYYY-MM-DD HH:mm:ss"),
)