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"), )