def migrate_database(database: Database): """Creates database tables. Args: database: The database to create tables for. """ # Connect database.connect() # Create tables database.create_tables([ DateData, ]) # Close connection database.close()
def init_models(db: Database, driver: Driver): class DbBarData(ModelBase): """ Candlestick bar data for database storage. Index is defined unique with datetime, interval, symbol """ id = AutoField() symbol: str = CharField() exchange: str = CharField() datetime: datetime = DateTimeField() interval: str = CharField() volume: float = FloatField() open_interest: float = FloatField() open_price: float = FloatField() high_price: float = FloatField() low_price: float = FloatField() close_price: float = FloatField() class Meta: database = db indexes = ((("symbol", "exchange", "interval", "datetime"), True), ) @staticmethod def from_bar(bar: BarData): """ Generate DbBarData object from BarData. """ db_bar = DbBarData() db_bar.symbol = bar.symbol db_bar.exchange = bar.exchange.value db_bar.datetime = bar.datetime db_bar.interval = bar.interval.value db_bar.volume = bar.volume db_bar.open_interest = bar.open_interest db_bar.open_price = bar.open_price db_bar.high_price = bar.high_price db_bar.low_price = bar.low_price db_bar.close_price = bar.close_price return db_bar def to_bar(self): """ Generate BarData object from DbBarData. """ bar = BarData( symbol=self.symbol, exchange=Exchange(self.exchange), datetime=self.datetime, interval=Interval(self.interval), volume=self.volume, open_price=self.open_price, high_price=self.high_price, open_interest=self.open_interest, low_price=self.low_price, close_price=self.close_price, gateway_name="DB", ) return bar @staticmethod def save_all(objs: List["DbBarData"]): """ save a list of objects, update if exists. """ dicts = [i.to_dict() for i in objs] with db.atomic(): if driver is Driver.POSTGRESQL: for bar in dicts: DbBarData.insert(bar).on_conflict( update=bar, conflict_target=( DbBarData.symbol, DbBarData.exchange, DbBarData.interval, DbBarData.datetime, ), ).execute() else: for c in chunked(dicts, 50): DbBarData.insert_many( c).on_conflict_replace().execute() class DbTickData(ModelBase): """ Tick data for database storage. Index is defined unique with (datetime, symbol) """ id = AutoField() symbol: str = CharField() exchange: str = CharField() datetime: datetime = DateTimeField() name: str = CharField() volume: float = FloatField() open_interest: float = FloatField() last_price: float = FloatField() last_volume: float = FloatField() limit_up: float = FloatField() limit_down: float = FloatField() open_price: float = FloatField() high_price: float = FloatField() low_price: float = FloatField() pre_close: float = FloatField() bid_price_1: float = FloatField() bid_price_2: float = FloatField(null=True) bid_price_3: float = FloatField(null=True) bid_price_4: float = FloatField(null=True) bid_price_5: float = FloatField(null=True) ask_price_1: float = FloatField() ask_price_2: float = FloatField(null=True) ask_price_3: float = FloatField(null=True) ask_price_4: float = FloatField(null=True) ask_price_5: float = FloatField(null=True) bid_volume_1: float = FloatField() bid_volume_2: float = FloatField(null=True) bid_volume_3: float = FloatField(null=True) bid_volume_4: float = FloatField(null=True) bid_volume_5: float = FloatField(null=True) ask_volume_1: float = FloatField() ask_volume_2: float = FloatField(null=True) ask_volume_3: float = FloatField(null=True) ask_volume_4: float = FloatField(null=True) ask_volume_5: float = FloatField(null=True) class Meta: database = db indexes = ((("symbol", "exchange", "datetime"), True), ) @staticmethod def from_tick(tick: TickData): """ Generate DbTickData object from TickData. """ db_tick = DbTickData() db_tick.symbol = tick.symbol db_tick.exchange = tick.exchange.value db_tick.datetime = tick.datetime db_tick.name = tick.name db_tick.volume = tick.volume db_tick.open_interest = tick.open_interest db_tick.last_price = tick.last_price db_tick.last_volume = tick.last_volume db_tick.limit_up = tick.limit_up db_tick.limit_down = tick.limit_down db_tick.open_price = tick.open_price db_tick.high_price = tick.high_price db_tick.low_price = tick.low_price db_tick.pre_close = tick.pre_close db_tick.bid_price_1 = tick.bid_price_1 db_tick.ask_price_1 = tick.ask_price_1 db_tick.bid_volume_1 = tick.bid_volume_1 db_tick.ask_volume_1 = tick.ask_volume_1 if tick.bid_price_2: db_tick.bid_price_2 = tick.bid_price_2 db_tick.bid_price_3 = tick.bid_price_3 db_tick.bid_price_4 = tick.bid_price_4 db_tick.bid_price_5 = tick.bid_price_5 db_tick.ask_price_2 = tick.ask_price_2 db_tick.ask_price_3 = tick.ask_price_3 db_tick.ask_price_4 = tick.ask_price_4 db_tick.ask_price_5 = tick.ask_price_5 db_tick.bid_volume_2 = tick.bid_volume_2 db_tick.bid_volume_3 = tick.bid_volume_3 db_tick.bid_volume_4 = tick.bid_volume_4 db_tick.bid_volume_5 = tick.bid_volume_5 db_tick.ask_volume_2 = tick.ask_volume_2 db_tick.ask_volume_3 = tick.ask_volume_3 db_tick.ask_volume_4 = tick.ask_volume_4 db_tick.ask_volume_5 = tick.ask_volume_5 return db_tick def to_tick(self): """ Generate TickData object from DbTickData. """ tick = TickData( symbol=self.symbol, exchange=Exchange(self.exchange), datetime=self.datetime, name=self.name, volume=self.volume, open_interest=self.open_interest, last_price=self.last_price, last_volume=self.last_volume, limit_up=self.limit_up, limit_down=self.limit_down, open_price=self.open_price, high_price=self.high_price, low_price=self.low_price, pre_close=self.pre_close, bid_price_1=self.bid_price_1, ask_price_1=self.ask_price_1, bid_volume_1=self.bid_volume_1, ask_volume_1=self.ask_volume_1, gateway_name="DB", ) if self.bid_price_2: tick.bid_price_2 = self.bid_price_2 tick.bid_price_3 = self.bid_price_3 tick.bid_price_4 = self.bid_price_4 tick.bid_price_5 = self.bid_price_5 tick.ask_price_2 = self.ask_price_2 tick.ask_price_3 = self.ask_price_3 tick.ask_price_4 = self.ask_price_4 tick.ask_price_5 = self.ask_price_5 tick.bid_volume_2 = self.bid_volume_2 tick.bid_volume_3 = self.bid_volume_3 tick.bid_volume_4 = self.bid_volume_4 tick.bid_volume_5 = self.bid_volume_5 tick.ask_volume_2 = self.ask_volume_2 tick.ask_volume_3 = self.ask_volume_3 tick.ask_volume_4 = self.ask_volume_4 tick.ask_volume_5 = self.ask_volume_5 return tick @staticmethod def save_all(objs: List["DbTickData"]): dicts = [i.to_dict() for i in objs] with db.atomic(): if driver is Driver.POSTGRESQL: for tick in dicts: DbTickData.insert(tick).on_conflict( update=tick, conflict_target=( DbTickData.symbol, DbTickData.exchange, DbTickData.datetime, ), ).execute() else: for c in chunked(dicts, 50): DbTickData.insert_many( c).on_conflict_replace().execute() db.connect() db.create_tables([DbBarData, DbTickData]) return DbBarData, DbTickData
def init_models(db: Database, driver: Driver): class DbBarData(ModelBase): """ Candlestick bar data for database storage. Index is defined unique with datetime, interval, symbol """ id = AutoField() symbol: str = CharField() exchange: str = CharField() datetime: datetime = DateTimeField() interval: str = CharField() volume: float = FloatField() open_interest: float = FloatField() open_price: float = FloatField() high_price: float = FloatField() low_price: float = FloatField() close_price: float = FloatField() class Meta: database = db indexes = ((("symbol", "exchange", "interval", "datetime"), True), ) @staticmethod def from_bar(bar: BarData): """ Generate DbBarData object from BarData. """ db_bar = DbBarData() db_bar.symbol = bar.symbol db_bar.exchange = bar.exchange.value db_bar.datetime = bar.datetime db_bar.interval = bar.interval.value db_bar.volume = bar.volume db_bar.open_interest = bar.open_interest db_bar.open_price = bar.open_price db_bar.high_price = bar.high_price db_bar.low_price = bar.low_price db_bar.close_price = bar.close_price return db_bar def to_bar(self): """ Generate BarData object from DbBarData. """ bar = BarData( symbol=self.symbol, exchange=Exchange(self.exchange), datetime=self.datetime, interval=Interval(self.interval), volume=self.volume, open_price=self.open_price, high_price=self.high_price, open_interest=self.open_interest, low_price=self.low_price, close_price=self.close_price, gateway_name="DB", ) return bar @staticmethod def save_all(objs: List["DbBarData"]): """ save a list of objects, update if exists. """ dicts = [i.to_dict() for i in objs] with db.atomic(): if driver is Driver.POSTGRESQL: for bar in dicts: DbBarData.insert(bar).on_conflict( update=bar, conflict_target=( DbBarData.symbol, DbBarData.exchange, DbBarData.interval, DbBarData.datetime, ), ).execute() else: for c in chunked(dicts, 50): DbBarData.insert_many( c).on_conflict_replace().execute() class DbTickData(ModelBase): """ Tick data for database storage. Index is defined unique with (datetime, symbol) """ id = AutoField() symbol: str = CharField() exchange: str = CharField() datetime: datetime = DateTimeField() name: str = CharField() volume: float = FloatField() open_interest: float = FloatField() last_price: float = FloatField() last_volume: float = FloatField() limit_up: float = FloatField() limit_down: float = FloatField() open_price: float = FloatField() high_price: float = FloatField() low_price: float = FloatField() pre_close: float = FloatField() bid_price_1: float = FloatField() bid_price_2: float = FloatField(null=True) bid_price_3: float = FloatField(null=True) bid_price_4: float = FloatField(null=True) bid_price_5: float = FloatField(null=True) ask_price_1: float = FloatField() ask_price_2: float = FloatField(null=True) ask_price_3: float = FloatField(null=True) ask_price_4: float = FloatField(null=True) ask_price_5: float = FloatField(null=True) bid_volume_1: float = FloatField() bid_volume_2: float = FloatField(null=True) bid_volume_3: float = FloatField(null=True) bid_volume_4: float = FloatField(null=True) bid_volume_5: float = FloatField(null=True) ask_volume_1: float = FloatField() ask_volume_2: float = FloatField(null=True) ask_volume_3: float = FloatField(null=True) ask_volume_4: float = FloatField(null=True) ask_volume_5: float = FloatField(null=True) class Meta: database = db indexes = ((("symbol", "exchange", "datetime"), True), ) @staticmethod def from_tick(tick: TickData): """ Generate DbTickData object from TickData. """ db_tick = DbTickData() db_tick.symbol = tick.symbol db_tick.exchange = tick.exchange.value db_tick.datetime = tick.datetime db_tick.name = tick.name db_tick.volume = tick.volume db_tick.open_interest = tick.open_interest db_tick.last_price = tick.last_price db_tick.last_volume = tick.last_volume db_tick.limit_up = tick.limit_up db_tick.limit_down = tick.limit_down db_tick.open_price = tick.open_price db_tick.high_price = tick.high_price db_tick.low_price = tick.low_price db_tick.pre_close = tick.pre_close db_tick.bid_price_1 = tick.bid_price_1 db_tick.ask_price_1 = tick.ask_price_1 db_tick.bid_volume_1 = tick.bid_volume_1 db_tick.ask_volume_1 = tick.ask_volume_1 if tick.bid_price_2: db_tick.bid_price_2 = tick.bid_price_2 db_tick.bid_price_3 = tick.bid_price_3 db_tick.bid_price_4 = tick.bid_price_4 db_tick.bid_price_5 = tick.bid_price_5 db_tick.ask_price_2 = tick.ask_price_2 db_tick.ask_price_3 = tick.ask_price_3 db_tick.ask_price_4 = tick.ask_price_4 db_tick.ask_price_5 = tick.ask_price_5 db_tick.bid_volume_2 = tick.bid_volume_2 db_tick.bid_volume_3 = tick.bid_volume_3 db_tick.bid_volume_4 = tick.bid_volume_4 db_tick.bid_volume_5 = tick.bid_volume_5 db_tick.ask_volume_2 = tick.ask_volume_2 db_tick.ask_volume_3 = tick.ask_volume_3 db_tick.ask_volume_4 = tick.ask_volume_4 db_tick.ask_volume_5 = tick.ask_volume_5 return db_tick def to_tick(self): """ Generate TickData object from DbTickData. """ tick = TickData( symbol=self.symbol, exchange=Exchange(self.exchange), datetime=self.datetime, name=self.name, volume=self.volume, open_interest=self.open_interest, last_price=self.last_price, last_volume=self.last_volume, limit_up=self.limit_up, limit_down=self.limit_down, open_price=self.open_price, high_price=self.high_price, low_price=self.low_price, pre_close=self.pre_close, bid_price_1=self.bid_price_1, ask_price_1=self.ask_price_1, bid_volume_1=self.bid_volume_1, ask_volume_1=self.ask_volume_1, gateway_name="DB", ) if self.bid_price_2: tick.bid_price_2 = self.bid_price_2 tick.bid_price_3 = self.bid_price_3 tick.bid_price_4 = self.bid_price_4 tick.bid_price_5 = self.bid_price_5 tick.ask_price_2 = self.ask_price_2 tick.ask_price_3 = self.ask_price_3 tick.ask_price_4 = self.ask_price_4 tick.ask_price_5 = self.ask_price_5 tick.bid_volume_2 = self.bid_volume_2 tick.bid_volume_3 = self.bid_volume_3 tick.bid_volume_4 = self.bid_volume_4 tick.bid_volume_5 = self.bid_volume_5 tick.ask_volume_2 = self.ask_volume_2 tick.ask_volume_3 = self.ask_volume_3 tick.ask_volume_4 = self.ask_volume_4 tick.ask_volume_5 = self.ask_volume_5 return tick @staticmethod def save_all(objs: List["DbTickData"]): dicts = [i.to_dict() for i in objs] with db.atomic(): if driver is Driver.POSTGRESQL: for tick in dicts: DbTickData.insert(tick).on_conflict( update=tick, conflict_target=( DbTickData.symbol, DbTickData.exchange, DbTickData.datetime, ), ).execute() else: for c in chunked(dicts, 50): DbTickData.insert_many( c).on_conflict_replace().execute() class DbContractData(ModelBase): id: str = CharField() symbol: str = CharField() exchange: str = CharField() name: str = CharField() product: str = CharField() size: int = IntegerField() pricetick: float = FloatField() min_volume: float = FloatField( ) # minimum trading volume of the contract stop_supported: bool = BooleanField( ) # whether server supports stop order net_position: bool = BooleanField( ) # whether gateway uses net position volume history_data: bool = BooleanField( ) # whether gateway provides bar history data option_strike: float = FloatField() option_underlying: str = CharField( ) # vt_symbol of underlying contract option_type: str = CharField(null=True) option_expiry: datetime = DateTimeField(null=True) class Meta: database = db indexes = ((("symbol", "symbol", "exchange"), True), ) @staticmethod def from_contract(contract: ContractData): """ Generate DbContractData object from ContractData. """ db_contract = DbContractData() db_contract.id = contract.symbol + "." + contract.exchange.value db_contract.symbol = contract.symbol db_contract.exchange = contract.exchange.value db_contract.name = contract.name db_contract.product = contract.product.value db_contract.size = contract.size db_contract.pricetick = contract.pricetick db_contract.min_volume = contract.min_volume db_contract.stop_supported = contract.stop_supported db_contract.net_position = contract.net_position db_contract.history_data = contract.history_data db_contract.option_strike = contract.option_strike db_contract.option_underlying = contract.option_underlying db_contract.option_type = contract.option_type db_contract.option_expiry = contract.option_expiry return db_contract def to_contract(self): """ Generate ContractData object from DbContractData. """ contract = ContractData( symbol=self.symbol, exchange=Exchange(self.exchange), name=self.name, product=Product(self.product), size=self.size, pricetick=self.pricetick, min_volume=self.min_volume, stop_supported=self.stop_supported, net_position=self.net_position, history_data=self.history_data, option_strike=self.option_strike, option_expiry=self.option_expiry, # option_type=OptionType(self.option_type), # 因为option_type没有值,会实例化失败,暂时to_contract过程中不实例化 option_type=self.option_type, option_underlying=self.option_underlying, gateway_name="DB", ) return contract @staticmethod def save_all(objs: List["DbContractData"]): """ save a list of objects, update if exists. """ dicts = [i.to_dict() for i in objs] with db.atomic(): if driver is Driver.POSTGRESQL: for contract in dicts: DbContractData.insert(contract).on_conflict( update=contract, conflict_target=(DbContractData.id, ), ).execute() else: for c in chunked(dicts, 50): DbContractData.insert_many( c).on_conflict_ignore().execute() class DbTradeData(ModelBase): id: str = CharField() accountid: str = CharField() symbol: str = CharField() exchange: str = CharField() orderid: str = CharField() tradeid: str = CharField() direction: str = CharField() offset: str = CharField() price: float = FloatField() volume: int = IntegerField() datetime: datetime = DateTimeField() #time: datetime = DateTimeField() class Meta: database = db indexes = ((("id", "orderid"), True), ) @staticmethod def from_trade(trade: TradeData): """ Generate DbContractData object from ContractData. """ db_trade = DbTradeData() # db_trade.id = trade.orderid db_trade.accountid = trade.accountid db_trade.symbol = trade.symbol db_trade.exchange = trade.exchange.value db_trade.orderid = trade.orderid db_trade.tradeid = trade.tradeid db_trade.direction = trade.direction.value db_trade.offset = trade.offset.value db_trade.price = trade.price db_trade.volume = trade.volume db_trade.datetime = datetime.now().strftime("%Y%m%d %H:%M:%S") return db_trade def to_trade(self): """ Generate ContractData object from DbContractData. """ trade = TradeData(accountid=self.accountid, symbol=self.symbol, exchange=Exchange(self.exchange), orderid=self.orderid, tradeid=self.tradeid, direction=Direction(self.direction), offset=Offset(self.offset), price=self.price, volume=self.volume, datetime=self.datetime, gateway_name="DB") return trade @staticmethod def save_all(objs: List["DbTradeData"]): """ save a list of objects, update if exists. """ dicts = [i.to_dict() for i in objs] with db.atomic(): if driver is Driver.POSTGRESQL: for trade in dicts: DbTradeData.insert(trade).on_conflict( update=trade, conflict_target=(DbTradeData.id, ), ).execute() else: for c in chunked(dicts, 50): DbTradeData.insert_many( c).on_conflict_ignore().execute() # @Author : LongWenHao # @Time : 2020.3.9 # @Dec : OrderData class DbOrderData(ModelBase): id: str = CharField() accountid: str = CharField() symbol: str = CharField() exchange: str = CharField() orderid: str = CharField() type: str = CharField() direction: str = CharField() offset: str = CharField() price: float = FloatField() volume: int = IntegerField() traded: float = FloatField() status: str = CharField() time: datetime = DateTimeField() class Meta: database = db indexes = ((("id", "orderid"), True), ) @staticmethod def from_order(order: OrderData): """ Generate DbContractData object from ContractData. """ db_order = DbOrderData() # db_order.id = order.orderid db_order.accountid = order.accountid db_order.symbol = order.symbol db_order.exchange = order.exchange.value db_order.orderid = order.orderid db_order.type = order.type db_order.direction = order.direction.value db_order.offset = order.offset.value db_order.price = order.price db_order.volume = order.volume db_order.traded = order.traded db_order.status = order.status db_order.time = datetime.now().strftime("%Y%m%d %H:%M:%S") return db_order def to_order(self): """ Generate ContractData object from DbContractData. """ order = OrderData(accountid=self.accountid, symbol=self.symbol, exchange=Exchange(self.exchange), orderid=self.orderid, type=self.type, direction=Direction(self.direction), offset=Offset(self.offset), price=self.price, volume=self.volume, traded=self.traded, status=self.status, time=self.time, gateway_name="DB") return order @staticmethod def save_all(objs: List["DbOrderData"]): """ save a list of objects, update if exists. """ dicts = [i.to_dict() for i in objs] with db.atomic(): if driver is Driver.POSTGRESQL: for order in dicts: DbOrderData.insert(order).on_conflict( update=order, conflict_target=(DbOrderData.id, ), ).execute() else: for c in chunked(dicts, 50): DbOrderData.insert_many( c).on_conflict_ignore().execute() db.connect() db.create_tables( [DbBarData, DbTickData, DbContractData, DbTradeData, DbOrderData]) return DbBarData, DbTickData, DbContractData, DbTradeData, DbOrderData
def init_models(db: Database, driver: Driver): class DbBarData(ModelBase): """ Candlestick bar data for database storage. Index is defined unique with datetime, interval, symbol """ id = AutoField() symbol: str = CharField() exchange: str = CharField() datetime: datetime = DateTimeField() interval: str = CharField() volume: float = FloatField() open_price: float = FloatField() high_price: float = FloatField() low_price: float = FloatField() close_price: float = FloatField() class Meta: database = db indexes = ((("symbol", "exchange", "interval", "datetime"), True),) @staticmethod def from_bar(bar: BarData): """ Generate DbBarData object from BarData. """ db_bar = DbBarData() db_bar.symbol = bar.symbol db_bar.exchange = bar.exchange.value db_bar.datetime = bar.datetime db_bar.interval = bar.interval.value db_bar.volume = bar.volume db_bar.open_price = bar.open_price db_bar.high_price = bar.high_price db_bar.low_price = bar.low_price db_bar.close_price = bar.close_price return db_bar def to_bar(self): """ Generate BarData object from DbBarData. """ bar = BarData( symbol=self.symbol, exchange=Exchange(self.exchange), datetime=self.datetime, interval=Interval(self.interval), volume=self.volume, open_price=self.open_price, high_price=self.high_price, low_price=self.low_price, close_price=self.close_price, gateway_name="DB", ) return bar @staticmethod def save_all(objs: List["DbBarData"]): """ save a list of objects, update if exists. """ dicts = [i.to_dict() for i in objs] with db.atomic(): if driver is Driver.POSTGRESQL: for bar in dicts: DbBarData.insert(bar).on_conflict( update=bar, conflict_target=( DbBarData.datetime, DbBarData.interval, DbBarData.symbol, DbBarData.exchange, ), ).execute() else: for c in chunked(dicts, 50): DbBarData.insert_many( c).on_conflict_replace().execute() class DbTickData(ModelBase): """ Tick data for database storage. Index is defined unique with (datetime, symbol) 数据库里的tick数据结构 """ # id id = AutoField() # 交易对 symbol: str = CharField() # 交易所 exchange: str = CharField() # 时间戳 timestamp: float = DoubleField() # 日期时间 datetime: datetime = DateTimeField() # 交易对 ??? name: str = CharField() # 按交易货币统计 volume: float = FloatField() # 最新成交价 last_price: float = FloatField() # 最新成交量 last_volume: float = FloatField() # 现价 高 limit_up: float = FloatField() # 现价 低 limit_down: float = FloatField() # 开 open_price: float = FloatField() # 高 high_price: float = FloatField() # 低 low_price: float = FloatField() # 前一个收盘价 pre_close: float = FloatField() # 买 bid_price_1: float = FloatField() bid_price_2: float = FloatField(null=True) bid_price_3: float = FloatField(null=True) bid_price_4: float = FloatField(null=True) bid_price_5: float = FloatField(null=True) # 卖 ask_price_1: float = FloatField() ask_price_2: float = FloatField(null=True) ask_price_3: float = FloatField(null=True) ask_price_4: float = FloatField(null=True) ask_price_5: float = FloatField(null=True) # 买 量 bid_volume_1: float = FloatField() bid_volume_2: float = FloatField(null=True) bid_volume_3: float = FloatField(null=True) bid_volume_4: float = FloatField(null=True) bid_volume_5: float = FloatField(null=True) # 卖 的量 ask_volume_1: float = FloatField() ask_volume_2: float = FloatField(null=True) ask_volume_3: float = FloatField(null=True) ask_volume_4: float = FloatField(null=True) ask_volume_5: float = FloatField(null=True) class Meta: database = db indexes = ((("symbol", "exchange", "timestamp", "datetime"), True),) @staticmethod def from_tick(tick: TickData): """ Generate DbTickData object from TickData. 从tickData对象生成数据库tick对象 """ db_tick = DbTickData() db_tick.symbol = tick.symbol db_tick.exchange = tick.exchange.value db_tick.timestamp = tick.timestamp db_tick.datetime = tick.datetime db_tick.name = tick.name db_tick.volume = tick.volume db_tick.last_price = tick.last_price db_tick.last_volume = tick.last_volume db_tick.limit_up = tick.limit_up db_tick.limit_down = tick.limit_down db_tick.open_price = tick.open_price db_tick.high_price = tick.high_price db_tick.low_price = tick.low_price db_tick.pre_close = tick.pre_close db_tick.bid_price_1 = tick.bid_price_1 db_tick.ask_price_1 = tick.ask_price_1 db_tick.bid_volume_1 = tick.bid_volume_1 db_tick.ask_volume_1 = tick.ask_volume_1 if tick.bid_price_2: db_tick.bid_price_2 = tick.bid_price_2 db_tick.bid_price_3 = tick.bid_price_3 db_tick.bid_price_4 = tick.bid_price_4 db_tick.bid_price_5 = tick.bid_price_5 db_tick.ask_price_2 = tick.ask_price_2 db_tick.ask_price_3 = tick.ask_price_3 db_tick.ask_price_4 = tick.ask_price_4 db_tick.ask_price_5 = tick.ask_price_5 db_tick.bid_volume_2 = tick.bid_volume_2 db_tick.bid_volume_3 = tick.bid_volume_3 db_tick.bid_volume_4 = tick.bid_volume_4 db_tick.bid_volume_5 = tick.bid_volume_5 db_tick.ask_volume_2 = tick.ask_volume_2 db_tick.ask_volume_3 = tick.ask_volume_3 db_tick.ask_volume_4 = tick.ask_volume_4 db_tick.ask_volume_5 = tick.ask_volume_5 return db_tick def to_tick(self): """ Generate TickData object from DbTickData. 从数据库获取的Tick数据生成 tickData数据对象。 """ tick = TickData( symbol=self.symbol, exchange=Exchange(self.exchange), timestamp=self.timestamp, datetime=self.datetime, name=self.name, volume=self.volume, last_price=self.last_price, last_volume=self.last_volume, limit_up=self.limit_up, limit_down=self.limit_down, open_price=self.open_price, high_price=self.high_price, low_price=self.low_price, pre_close=self.pre_close, bid_price_1=self.bid_price_1, ask_price_1=self.ask_price_1, bid_volume_1=self.bid_volume_1, ask_volume_1=self.ask_volume_1, gateway_name="DB", ) if self.bid_price_2: tick.bid_price_2 = self.bid_price_2 tick.bid_price_3 = self.bid_price_3 tick.bid_price_4 = self.bid_price_4 tick.bid_price_5 = self.bid_price_5 tick.ask_price_2 = self.ask_price_2 tick.ask_price_3 = self.ask_price_3 tick.ask_price_4 = self.ask_price_4 tick.ask_price_5 = self.ask_price_5 tick.bid_volume_2 = self.bid_volume_2 tick.bid_volume_3 = self.bid_volume_3 tick.bid_volume_4 = self.bid_volume_4 tick.bid_volume_5 = self.bid_volume_5 tick.ask_volume_2 = self.ask_volume_2 tick.ask_volume_3 = self.ask_volume_3 tick.ask_volume_4 = self.ask_volume_4 tick.ask_volume_5 = self.ask_volume_5 return tick @staticmethod def save_all(objs: List["DbTickData"]): dicts = [i.to_dict() for i in objs] with db.atomic(): if driver is Driver.POSTGRESQL: for tick in dicts: DbTickData.insert(tick).on_conflict( update=tick, conflict_target=( # DbTickData.timestamp, DbTickData.datetime, DbTickData.symbol, DbTickData.exchange, ), ).execute() else: for c in chunked(dicts, 50): DbTickData.insert_many(c).on_conflict_replace().execute() DbTickData.insert_many( c).on_conflict_replace().execute() db.connect() # 如何在这里指定数据库表名, 或者根据日期新建数据库,每个数据库的表一致。 DbBarData._meta.table_name = "DbBarData" + str(datetime.date.today()) DbTickData._meta.table_name = "DbTickData" + str(datetime.date.today()) # DbBarData._meta.table_name = "DbBarData" + str(datetime.date.today()) + \ # str(time.strftime('%H', time.localtime())) # DbTickData._meta.table_name = "DbTickData" + str(datetime.date.today()) + \ # str(time.strftime('%H', time.localtime())) db.create_tables([DbBarData, DbTickData]) return DbBarData, DbTickData
def init_models(db: Database, driver: Driver): class DbBarData(ModelBase): """ Candlestick bar data for database storage. Index is defined unique with datetime, interval, symbol """ id = AutoField() symbol: str = CharField() exchange: str = CharField() datetime: datetime = DateTimeField() interval: str = CharField() volume: float = FloatField() open_interest: float = FloatField() open_price: float = FloatField() high_price: float = FloatField() low_price: float = FloatField() close_price: float = FloatField() class Meta: database = db indexes = ((("symbol", "exchange", "interval", "datetime"), True),) @staticmethod def from_bar(bar: BarData): """ Generate DbBarData object from BarData. """ # Change datetime to database timezone, then # remove tzinfo since not supported by SQLite. dt = bar.datetime.astimezone(DB_TZ) dt = dt.replace(tzinfo=None) db_bar = DbBarData() db_bar.symbol = bar.symbol db_bar.exchange = bar.exchange.value db_bar.datetime = dt db_bar.interval = bar.interval.value db_bar.volume = bar.volume db_bar.open_interest = bar.open_interest db_bar.open_price = bar.open_price db_bar.high_price = bar.high_price db_bar.low_price = bar.low_price db_bar.close_price = bar.close_price return db_bar def to_bar(self): """ Generate BarData object from DbBarData. """ bar = BarData( symbol=self.symbol, exchange=Exchange(self.exchange), datetime=self.datetime.replace(tzinfo=DB_TZ), interval=Interval(self.interval), volume=self.volume, open_price=self.open_price, high_price=self.high_price, open_interest=self.open_interest, low_price=self.low_price, close_price=self.close_price, gateway_name="DB", ) return bar @staticmethod def save_all(objs: List["DbBarData"], progress_bar_dict=None): """ save a list of objects, update if exists. """ dicts = [i.to_dict() for i in objs] with db.atomic(): if driver is Driver.POSTGRESQL: for bar in dicts: DbBarData.insert(bar).on_conflict( update=bar, conflict_target=( DbBarData.symbol, DbBarData.exchange, DbBarData.interval, DbBarData.datetime, ), ).execute() else: total_sz = len(dicts) loaded = 0 for c in chunked(dicts, 50): DbBarData.insert_many(c).on_conflict_replace().execute() if 'save_progress_bar' in progress_bar_dict: loaded += 50 percent_saved = min(round(100 * loaded / total_sz, 2), 100) QApplication.processEvents() progress_bar_dict['save_progress_bar'].setValue(percent_saved) elif 'web_progress' in progress_bar_dict: loaded += 50 percent_saved = min(round(100 * loaded / total_sz, 2), 100) progress_bar_dict['web_progress'].write_message({'progress': percent_saved}) print(f"web progress: {percent_saved}") class DbTickData(ModelBase): """ Tick data for database storage. Index is defined unique with (datetime, symbol) """ id = AutoField() symbol: str = CharField() exchange: str = CharField() datetime: datetime = DateTimeField() name: str = CharField() volume: float = FloatField() open_interest: float = FloatField() last_price: float = FloatField() last_volume: float = FloatField() limit_up: float = FloatField() limit_down: float = FloatField() open_price: float = FloatField() high_price: float = FloatField() low_price: float = FloatField() pre_close: float = FloatField() bid_price_1: float = FloatField() bid_price_2: float = FloatField(null=True) bid_price_3: float = FloatField(null=True) bid_price_4: float = FloatField(null=True) bid_price_5: float = FloatField(null=True) ask_price_1: float = FloatField() ask_price_2: float = FloatField(null=True) ask_price_3: float = FloatField(null=True) ask_price_4: float = FloatField(null=True) ask_price_5: float = FloatField(null=True) bid_volume_1: float = FloatField() bid_volume_2: float = FloatField(null=True) bid_volume_3: float = FloatField(null=True) bid_volume_4: float = FloatField(null=True) bid_volume_5: float = FloatField(null=True) ask_volume_1: float = FloatField() ask_volume_2: float = FloatField(null=True) ask_volume_3: float = FloatField(null=True) ask_volume_4: float = FloatField(null=True) ask_volume_5: float = FloatField(null=True) class Meta: database = db indexes = ((("symbol", "exchange", "datetime"), True),) @staticmethod def from_tick(tick: TickData): """ Generate DbTickData object from TickData. """ # Change datetime to database timezone, then # remove tzinfo since not supported by SQLite. dt = tick.datetime.astimezone(DB_TZ) dt = dt.replace(tzinfo=None) db_tick = DbTickData() db_tick.symbol = tick.symbol db_tick.exchange = tick.exchange.value db_tick.datetime = dt db_tick.name = tick.name db_tick.volume = tick.volume db_tick.open_interest = tick.open_interest db_tick.last_price = tick.last_price db_tick.last_volume = tick.last_volume db_tick.limit_up = tick.limit_up db_tick.limit_down = tick.limit_down db_tick.open_price = tick.open_price db_tick.high_price = tick.high_price db_tick.low_price = tick.low_price db_tick.pre_close = tick.pre_close db_tick.bid_price_1 = tick.bid_price_1 db_tick.ask_price_1 = tick.ask_price_1 db_tick.bid_volume_1 = tick.bid_volume_1 db_tick.ask_volume_1 = tick.ask_volume_1 if tick.bid_price_2: db_tick.bid_price_2 = tick.bid_price_2 db_tick.bid_price_3 = tick.bid_price_3 db_tick.bid_price_4 = tick.bid_price_4 db_tick.bid_price_5 = tick.bid_price_5 db_tick.ask_price_2 = tick.ask_price_2 db_tick.ask_price_3 = tick.ask_price_3 db_tick.ask_price_4 = tick.ask_price_4 db_tick.ask_price_5 = tick.ask_price_5 db_tick.bid_volume_2 = tick.bid_volume_2 db_tick.bid_volume_3 = tick.bid_volume_3 db_tick.bid_volume_4 = tick.bid_volume_4 db_tick.bid_volume_5 = tick.bid_volume_5 db_tick.ask_volume_2 = tick.ask_volume_2 db_tick.ask_volume_3 = tick.ask_volume_3 db_tick.ask_volume_4 = tick.ask_volume_4 db_tick.ask_volume_5 = tick.ask_volume_5 return db_tick def to_tick(self): """ Generate TickData object from DbTickData. """ tick = TickData( symbol=self.symbol, exchange=Exchange(self.exchange), datetime=self.datetime.replace(tzinfo=DB_TZ), name=self.name, volume=self.volume, open_interest=self.open_interest, last_price=self.last_price, last_volume=self.last_volume, limit_up=self.limit_up, limit_down=self.limit_down, open_price=self.open_price, high_price=self.high_price, low_price=self.low_price, pre_close=self.pre_close, bid_price_1=self.bid_price_1, ask_price_1=self.ask_price_1, bid_volume_1=self.bid_volume_1, ask_volume_1=self.ask_volume_1, gateway_name="DB", ) if self.bid_price_2: tick.bid_price_2 = self.bid_price_2 tick.bid_price_3 = self.bid_price_3 tick.bid_price_4 = self.bid_price_4 tick.bid_price_5 = self.bid_price_5 tick.ask_price_2 = self.ask_price_2 tick.ask_price_3 = self.ask_price_3 tick.ask_price_4 = self.ask_price_4 tick.ask_price_5 = self.ask_price_5 tick.bid_volume_2 = self.bid_volume_2 tick.bid_volume_3 = self.bid_volume_3 tick.bid_volume_4 = self.bid_volume_4 tick.bid_volume_5 = self.bid_volume_5 tick.ask_volume_2 = self.ask_volume_2 tick.ask_volume_3 = self.ask_volume_3 tick.ask_volume_4 = self.ask_volume_4 tick.ask_volume_5 = self.ask_volume_5 return tick @staticmethod def save_all(objs: List["DbTickData"]): dicts = [i.to_dict() for i in objs] with db.atomic(): if driver is Driver.POSTGRESQL: for tick in dicts: DbTickData.insert(tick).on_conflict( update=tick, conflict_target=( DbTickData.symbol, DbTickData.exchange, DbTickData.datetime, ), ).execute() else: for c in chunked(dicts, 50): DbTickData.insert_many(c).on_conflict_replace().execute() db.connect() db.create_tables([DbBarData, DbTickData]) return DbBarData, DbTickData
def init_models(db: Database, driver: Driver): class DbTradeData(ModelBase): """ 交易记录 """ id = AutoField() symbol: str = CharField() product_code: str = CharField() exchange: str = CharField() datetime: datetime = DateTimeField() direction: str = CharField() offset: str = CharField() price: float = FloatField() volume: float = FloatField() rest_volume: float = FloatField() strategy: str = CharField() engine_type: str = CharField() class Meta: database = db db_table = "dbtrade" indexes = ((("datetime", "engine_type", "symbol", "exchange"), False), ) @staticmethod def from_trade(trade: TradeDataExt): """ Generate DbTradeData object from TradeData. """ db_trade = DbTradeData() db_trade.product_code = trade.product_code db_trade.symbol = trade.symbol db_trade.exchange = trade.exchange.value db_trade.datetime = trade.datetime db_trade.offset = trade.offset.value db_trade.direction = trade.direction.value db_trade.volume = trade.volume db_trade.rest_volume = trade.volume # 剩余需要开平配对的数量 db_trade.price = trade.price db_trade.strategy = trade.strategy db_trade.engine_type = trade.engine_type.value return db_trade def to_trade(self): trade = TradeDataExt(id=self.id, product_code=self.product_code, symbol=self.symbol, exchange=Exchange(self.exchange), datetime=self.datetime, direction=Direction(self.direction), offset=Offset(self.offset), price=self.price, volume=self.volume, rest_volume=self.rest_volume, strategy=self.strategy, engine_type=EngineType(self.engine_type)) return trade @staticmethod def save_one(product: "DbTradeData") -> int: record = product.to_dict() return DbTradeData.insert(record).execute() class DbInvestmentData(ModelBase): """ 投资记录 """ id = AutoField() product_code: str = CharField() symbol: str = CharField() exchange: str = CharField() start_datetime: datetime = DateTimeField() end_datetime: datetime = DateTimeField(null=True) direction: str = CharField() volume: float = FloatField() close_volume: float = FloatField() open_price: float = FloatField() finish_price: float = FloatField(null=True) # 多笔平仓价的均价 money_lock: float = FloatField(null=True) # 资金占用 profit: float = FloatField(null=True) # 毛利润 cost_fee: float = FloatField(null=True) # 手续费 net_profit: float = FloatField(null=True) # 净利润 = 毛利润 - 手续费 profit_rate: float = FloatField(null=True) # 利润率 = 净利润 / open_trade_id: int = CharField() close_trade_ids: str = CharField(null=True) strategy: str = CharField() state: int = CharField() engine_type: str = CharField() class Meta: database = db db_table = "dbinvestment" indexes = ((("start_datetime", "engine_type", "state", "symbol", "exchange"), False), ) @staticmethod def from_investment(investment: InvestmentData): db_investment = DbInvestmentData() db_investment.id = investment.id if investment.id is not None and investment.id > 0 else None db_investment.product_code = investment.product_code db_investment.symbol = investment.symbol db_investment.exchange = investment.exchange.value db_investment.open_price = investment.open_price db_investment.finish_price = investment.finish_price db_investment.volume = investment.volume db_investment.close_volume = investment.close_volume db_investment.direction = investment.direction.value db_investment.money_lock = investment.money_lock db_investment.profit = investment.profit db_investment.profit_rate = investment.profit_rate db_investment.net_profit = investment.net_profit db_investment.cost_fee = investment.cost_fee db_investment.strategy = investment.strategy db_investment.start_datetime = investment.start_datetime db_investment.end_datetime = investment.end_datetime db_investment.open_trade_id = investment.open_trade_id if len(investment.close_trade_ids) > 0: db_investment.close_trade_ids = ",".join( [str(x) for x in investment.close_trade_ids]) db_investment.state = investment.state.value db_investment.engine_type = investment.engine_type.value return db_investment def to_investment(self): investment = InvestmentData( id=self.id, exchange=Exchange(self.exchange), product_code=self.product_code, symbol=self.symbol, open_price=self.open_price, finish_price=self.finish_price, volume=self.volume, close_volume=self.close_volume, direction=Direction(self.direction), money_lock=self.money_lock, profit=self.profit, profit_rate=self.profit_rate, net_profit=self.net_profit, cost_fee=self.cost_fee, strategy=self.strategy, start_datetime=self.start_datetime, end_datetime=self.end_datetime, open_trade_id=self.open_trade_id, close_trade_ids=self.close_trade_ids.split(",") if self.close_trade_ids is not None else None, state=InvestmentState(self.state), engine_type=EngineType(self.engine_type)) return investment @staticmethod def save_one(product: "DbInvestmentData"): record = product.to_dict() DbInvestmentData.insert(record).execute() class DbProductData(ModelBase): """ 期货品种配置 """ id = AutoField() exchange: str = CharField() product_code: str = CharField() product_name: str = CharField() contract_size: float = FloatField() margin_percent: float = FloatField() commission_unit: str = CharField() commission: float = FloatField() class Meta: database = db db_table = "dbproduct" indexes = ((("exchange", "product_code"), True), ) @staticmethod def from_product(product: ProductData): db_product = DbProductData() db_product.exchange = product.exchange.value db_product.product_code = product.product_code db_product.product_name = product.product_name db_product.contract_size = product.contract_size db_product.margin_percent = product.margin_percent db_product.commission_unit = product.commission_unit.value db_product.commission = product.commission return db_product def to_product(self): product = ProductData(exchange=Exchange(self.exchange), product_code=self.product_code, product_name=self.product_name, contract_size=self.contract_size, margin_percent=self.margin_percent, commission_unit=CommissionUnit( self.commission_unit), commission=self.commission) return product @staticmethod def save_one(product: "DbProductData"): record = product.to_dict() if driver is Driver.POSTGRESQL: return DbProductData.insert(record).on_conflict( action="IGNORE", update=record, conflict_target=(DbProductData.exchange, DbProductData.product_code), ).execute() else: return DbProductData.insert( record).on_conflict_ignore().execute() db.connect() db.create_tables([DbTradeData, DbInvestmentData, DbProductData]) return DbTradeData, DbInvestmentData, DbProductData
def init_models(db: Database, driver: Driver): class DBFactorRetData(ModelBase): """ Candlestick bar data for database storage. Index is defined unique with datetime, interval, symbol """ id = AutoField() date: datetime = DateTimeField() factor_return: float = FloatField() holding_period: int = IntegerField() factor_T: float = FloatField(null=True) factor_name: str = CharField() factor_name_chinese: str = CharField() ret_type: str = CharField() datetime_update: datetime = DateTimeField() class Meta: database = db indexes = ((("date", "factor_name", "ret_type", "holding_period"), True),) @staticmethod def from_ret(ret: FactorRetData): """ Generate DbBarData object from BarData. """ db_bar = DBFactorRetData() db_bar.date = ret.date db_bar.factor_return = ret.factor_return db_bar.factor_T = ret.factor_T db_bar.holding_period = ret.holding_period db_bar.factor_name = ret.factor_name db_bar.factor_name_chinese = ret.factor_name_chinese db_bar.ret_type = ret.ret_type db_bar.datetime_update = datetime.now() return db_bar def to_bar(self): """ Generate GroupData object from DbGroupData. """ Ret = FactorRetData() return Ret @staticmethod def save_all(objs: List["DBFactorRetData"]): """ save a list of objects, update if exists. """ dicts = [i.to_dict() for i in objs] with db.atomic(): if driver is Driver.POSTGRESQL: for bar in dicts: DBFactorRetData.insert(bar).on_conflict( update=bar, conflict_target=( DBFactorRetData.stock_id, DBFactorRetData.date, ), ).execute() else: for c in chunked(dicts, 1000): DBFactorRetData.insert_many(c).on_conflict_replace().execute() def query_data(self, factor_names: tuple, ret_type: str = 'Pearson', hp: int = 1, sta_date: str = '2013-01-01', end_date: str = '2020-04-01'): factor_sql = f"SELECT DATE_FORMAT(`date`,'%Y-%m-%d') as `date`, factor_return, factor_name " \ f"FROM dbfactorretdata " \ f"WHERE factor_name IN {factor_names} " \ f"AND ret_type = '{ret_type}' " \ f"AND holding_period = '{hp}' " \ f"AND `date` BETWEEN str_to_date('{sta_date}', '%Y-%m-%d') " \ f"AND str_to_date('{end_date}', '%Y-%m-%d') " res = pd.read_sql(factor_sql, con=MySQL_con) return None if res.empty else res class DbFactorGroupData(ModelBase): """ Candlestick bar data for database storage. Index is defined unique with datetime, interval, symbol """ id = AutoField() stock_id: str = CharField() date: datetime = DateTimeField() industry: str = CharField() group: int = IntegerField() stock_return: float = FloatField() factor_name: str = CharField() holding_period: int = IntegerField() factor_name_chinese: str = CharField() factor_value: float = FloatField(null=True) factor_type: str = CharField() datetime_update: datetime = DateTimeField() class Meta: database = db indexes = ((("stock_id", "date", "factor_name", "holding_period"), True),) @staticmethod def from_group(group: GroupData): """ Generate DbBarData object from BarData. """ db_bar = DbFactorGroupData() db_bar.stock_id = group.stock_id db_bar.date = group.date db_bar.industry = group.industry db_bar.group = group.group db_bar.stock_return = group.stock_return db_bar.holding_period = group.holding_period db_bar.factor_name = group.factor_name db_bar.factor_value = group.factor_value db_bar.factor_name_chinese = group.factor_name_chinese db_bar.factor_type = group.factor_type db_bar.datetime_update = datetime.now() return db_bar def to_bar(self): """ Generate GroupData object from DbGroupData. """ group = GroupData() return group @staticmethod def save_all(objs: List["DbFactorGroupData"]): """ save a list of objects, update if exists. """ dicts = [i.to_dict() for i in objs] with db.atomic(): if driver is Driver.POSTGRESQL: for bar in dicts: DbFactorGroupData.insert(bar).on_conflict( update=bar, conflict_target=( DbFactorGroupData.stock_id, DbFactorGroupData.date, ), ).execute() else: for c in chunked(dicts, 5000): DbFactorGroupData.insert_many(c).on_conflict_replace().execute() class DbFactFinData(ModelBase): """ Candlestick bar data for database storage. Index is defined unique with datetime, interval, symbol """ id = AutoField() stock_id: str = CharField(max_length=10) date: datetime = DateTimeField() date_report: datetime = DateTimeField() factor_category: str = CharField(max_length=50) factor_name: str = CharField(max_length=50) factor_name_chinese: str = CharField() factor_value: float = FloatField(null=True) factor_type: str = CharField(max_length=20) datetime_update: datetime = DateTimeField() class Meta: database = db indexes = ((("stock_id", "date", "factor_category", "factor_name", "factor_type"), True),) @staticmethod def from_factor(factor: FactorData, DataClass: type) -> "ModelBase": """ Generate DbFactorData object from FactorData. """ db_bar = DataClass() db_bar.stock_id = factor.stock_id db_bar.date = factor.date # 公布期 db_bar.date_report = factor.date_report # 报告期 db_bar.factor_category = factor.factor_category db_bar.factor_name = factor.factor_name db_bar.factor_name_chinese = factor.factor_name_chinese db_bar.factor_value = factor.factor_value db_bar.factor_type = factor.factor_type db_bar.datetime_update = datetime.now() return db_bar def to_bar(self): """ Generate GroupData object from DbGroupData. """ factor = FactorData() return factor @staticmethod def save_all(objs: List[ModelBase], DataClass: ModelBase): """ save a list of objects, update if exists. """ dicts = map(lambda x: x.to_dict(), objs) with db.atomic(): if driver is Driver.POSTGRESQL: for bar in dicts: DataClass.insert(bar).on_conflict( update=bar, conflict_target=( DataClass.stock_id, DataClass.date, ), ).execute() else: i = 1 num = 5000 for c in chunked(dicts, num): sta = time.time() print(f"Insert data to database {DataClass.__name__}: {i}-{i + num - 1}") DataClass.insert_many(c).on_conflict_replace().execute() print(time.time() - sta) i += num def query_data(self, factor_name: str): factor_sql = f"SELECT DATE_FORMAT(`date`,'%Y-%m-%d') as `date`, stock_id, factor_value as {factor_name} " \ f"FROM dbfactfindata " \ f"WHERE factor_name = '{factor_name}' " # TODO 名称 res = pd.read_sql(factor_sql, con=MySQL_con) return None if res.empty else res class DBFactMTMData(DbFactFinData): pass class DBFactGenProData(DbFactFinData): pass if not db.autoconnect: db.connect() db.create_tables([DBFactorRetData]) db.create_tables([DbFactorGroupData]) db.create_tables([DbFactFinData]) db.create_tables([DBFactMTMData]) db.create_tables([DBFactGenProData]) mapping = {"Ret": DBFactorRetData, "Group": DbFactorGroupData, "Fin": DbFactFinData, "MTM": DBFactMTMData, "GenPro": DBFactGenProData} return mapping
def init_models(db: Database, driver: Driver): class DbBarData(ModelBase): """ Candlestick bar data for database storage. Index is defined unique with datetime, interval, symbol """ id = AutoField() symbol: str = CharField() exchange: str = CharField() datetime: datetime = DateTimeField() interval: str = CharField() volume: float = FloatField() open_interest: float = FloatField() open_price: float = FloatField() high_price: float = FloatField() low_price: float = FloatField() close_price: float = FloatField() class Meta: database = db indexes = ((("symbol", "exchange", "interval", "datetime"), True), ) @staticmethod def from_bar(bar: BarData): """ Generate DbBarData object from BarData. """ db_bar = DbBarData() db_bar.symbol = bar.symbol db_bar.exchange = bar.exchange.value db_bar.datetime = bar.datetime db_bar.interval = bar.interval.value db_bar.volume = bar.volume db_bar.open_interest = bar.open_interest db_bar.open_price = bar.open_price db_bar.high_price = bar.high_price db_bar.low_price = bar.low_price db_bar.close_price = bar.close_price return db_bar def to_bar(self): """ Generate BarData object from DbBarData. """ bar = BarData( symbol=self.symbol, exchange=Exchange(self.exchange), datetime=self.datetime, interval=Interval(self.interval), volume=self.volume, open_price=self.open_price, high_price=self.high_price, open_interest=self.open_interest, low_price=self.low_price, close_price=self.close_price, gateway_name="DB", ) return bar @staticmethod def save_all(objs: List["DbBarData"]): """ save a list of objects, update if exists. """ dicts = [i.to_dict() for i in objs] with db.atomic(): if driver is Driver.POSTGRESQL: for bar in dicts: DbBarData.insert(bar).on_conflict( update=bar, conflict_target=( DbBarData.symbol, DbBarData.exchange, DbBarData.interval, DbBarData.datetime, ), ).execute() else: for c in chunked(dicts, 50): DbBarData.insert_many( c).on_conflict_replace().execute() @staticmethod def db_close(): if not db.is_closed(): print('DB connection is to be closed...') db.close() class DbTickData(ModelBase): """ Tick data for database storage. Index is defined unique with (datetime, symbol) """ id = AutoField() symbol: str = CharField() exchange: str = CharField() datetime: datetime = DateTimeField() name: str = CharField() volume: float = FloatField() open_interest: float = FloatField() last_price: float = FloatField() last_volume: float = FloatField() limit_up: float = FloatField() limit_down: float = FloatField() open_price: float = FloatField() high_price: float = FloatField() low_price: float = FloatField() pre_close: float = FloatField() bid_price_1: float = FloatField() bid_price_2: float = FloatField(null=True) bid_price_3: float = FloatField(null=True) bid_price_4: float = FloatField(null=True) bid_price_5: float = FloatField(null=True) ask_price_1: float = FloatField() ask_price_2: float = FloatField(null=True) ask_price_3: float = FloatField(null=True) ask_price_4: float = FloatField(null=True) ask_price_5: float = FloatField(null=True) bid_volume_1: float = FloatField() bid_volume_2: float = FloatField(null=True) bid_volume_3: float = FloatField(null=True) bid_volume_4: float = FloatField(null=True) bid_volume_5: float = FloatField(null=True) ask_volume_1: float = FloatField() ask_volume_2: float = FloatField(null=True) ask_volume_3: float = FloatField(null=True) ask_volume_4: float = FloatField(null=True) ask_volume_5: float = FloatField(null=True) class Meta: database = db indexes = ((("symbol", "exchange", "datetime"), True), ) @staticmethod def from_tick(tick: TickData): """ Generate DbTickData object from TickData. """ db_tick = DbTickData() db_tick.symbol = tick.symbol db_tick.exchange = tick.exchange.value db_tick.datetime = tick.datetime db_tick.name = tick.name db_tick.volume = tick.volume db_tick.open_interest = tick.open_interest db_tick.last_price = tick.last_price db_tick.last_volume = tick.last_volume db_tick.limit_up = tick.limit_up db_tick.limit_down = tick.limit_down db_tick.open_price = tick.open_price db_tick.high_price = tick.high_price db_tick.low_price = tick.low_price db_tick.pre_close = tick.pre_close db_tick.bid_price_1 = tick.bid_price_1 db_tick.ask_price_1 = tick.ask_price_1 db_tick.bid_volume_1 = tick.bid_volume_1 db_tick.ask_volume_1 = tick.ask_volume_1 if tick.bid_price_2: db_tick.bid_price_2 = tick.bid_price_2 db_tick.bid_price_3 = tick.bid_price_3 db_tick.bid_price_4 = tick.bid_price_4 db_tick.bid_price_5 = tick.bid_price_5 db_tick.ask_price_2 = tick.ask_price_2 db_tick.ask_price_3 = tick.ask_price_3 db_tick.ask_price_4 = tick.ask_price_4 db_tick.ask_price_5 = tick.ask_price_5 db_tick.bid_volume_2 = tick.bid_volume_2 db_tick.bid_volume_3 = tick.bid_volume_3 db_tick.bid_volume_4 = tick.bid_volume_4 db_tick.bid_volume_5 = tick.bid_volume_5 db_tick.ask_volume_2 = tick.ask_volume_2 db_tick.ask_volume_3 = tick.ask_volume_3 db_tick.ask_volume_4 = tick.ask_volume_4 db_tick.ask_volume_5 = tick.ask_volume_5 return db_tick def to_tick(self): """ Generate TickData object from DbTickData. """ tick = TickData( symbol=self.symbol, exchange=Exchange(self.exchange), datetime=self.datetime, name=self.name, volume=self.volume, open_interest=self.open_interest, last_price=self.last_price, last_volume=self.last_volume, limit_up=self.limit_up, limit_down=self.limit_down, open_price=self.open_price, high_price=self.high_price, low_price=self.low_price, pre_close=self.pre_close, bid_price_1=self.bid_price_1, ask_price_1=self.ask_price_1, bid_volume_1=self.bid_volume_1, ask_volume_1=self.ask_volume_1, gateway_name="DB", ) if self.bid_price_2: tick.bid_price_2 = self.bid_price_2 tick.bid_price_3 = self.bid_price_3 tick.bid_price_4 = self.bid_price_4 tick.bid_price_5 = self.bid_price_5 tick.ask_price_2 = self.ask_price_2 tick.ask_price_3 = self.ask_price_3 tick.ask_price_4 = self.ask_price_4 tick.ask_price_5 = self.ask_price_5 tick.bid_volume_2 = self.bid_volume_2 tick.bid_volume_3 = self.bid_volume_3 tick.bid_volume_4 = self.bid_volume_4 tick.bid_volume_5 = self.bid_volume_5 tick.ask_volume_2 = self.ask_volume_2 tick.ask_volume_3 = self.ask_volume_3 tick.ask_volume_4 = self.ask_volume_4 tick.ask_volume_5 = self.ask_volume_5 return tick @staticmethod def save_all(objs: List["DbTickData"]): dicts = [i.to_dict() for i in objs] with db.atomic(): if driver is Driver.POSTGRESQL: for tick in dicts: DbTickData.insert(tick).on_conflict( update=tick, conflict_target=( DbTickData.symbol, DbTickData.exchange, DbTickData.datetime, ), ).execute() else: for c in chunked(dicts, 50): DbTickData.insert_many( c).on_conflict_replace().execute() class DbOptionsBasic(ModelBase): """ Options basic data for database storage. Index is defined unique with symbol, exchange """ id = AutoField() symbol: str = CharField() exchange: str = CharField() name: str = CharField() #合约名称 per_unit: str = CharField() #合约单位 opt_code: str = CharField() #标准合约代码 opt_type: str = CharField() #合约类型 call_put: str = CharField() #期权类型 exercise_type: str = CharField() #行权方式 exercise_price: float = FloatField() #行权价格 s_month: str = CharField() #结算月 maturity_date: datetime = DateTimeField() #到期日 list_price: float = FloatField() #挂牌基准价 list_date: datetime = DateTimeField() #开始交易日期 delist_date: datetime = DateTimeField() #最后交易日期 last_edate: datetime = DateTimeField() #最后行权日期 last_ddate: datetime = DateTimeField() #最后交割日期 quote_unit: str = CharField() #报价单位 min_price_chg: str = CharField() #最小价格波幅 class Meta: database = db indexes = ((("symbol", "exchange"), True), ) @staticmethod def from_opbasic(opbasic: OptionsBasic): """ Generate DbBarData object from BarData. """ db_opbasic = DbOptionsBasic db_opbasic.symbol = opbasic.symbol db_opbasic.exchange = opbasic.exchange.value db_opbasic.name = opbasic.name db_opbasic.per_unit = opbasic.per_unit db_opbasic.opt_code = opbasic.opt_code db_opbasic.opt_type = opbasic.opt_type db_opbasic.call_put = opbasic.call_put db_opbasic.exercise_type = opbasic.exercise_type db_opbasic.exercise_price = opbasic.exercise_price db_opbasic.s_month = opbasic.s_month db_opbasic.maturity_date = opbasic.maturity_date db_opbasic.list_price = opbasic.list_price db_opbasic.list_date = opbasic.list_date db_opbasic.delist_date = opbasic.delist_date db_opbasic.last_edate = opbasic.last_edate db_opbasic.last_ddate = opbasic.last_ddate db_opbasic.quote_unit = opbasic.quote_unit db_opbasic.min_price_chg = opbasic.min_price_chg return db_opbasic def to_opbasic(self): """ Generate OptionsBasic object from DbOpitonsBasic. """ opbasic = OptionsBasic( symbol=self.symbol, exchange=Exchange(self.exchange), name=self.name, per_unit=self.per_unit, opt_code=self.opt_code, opt_type=self.opt_type, call_put=self.call_put, exercise_type=self.exercise_type, exercise_price=self.exercise_price, s_month=self.s_month, maturity_date=self.maturity_date, list_price=self.list_price, list_date=self.list_date, delist_date=self.delist_date, last_edate=self.last_edate, last_ddate=self.last_ddate, quote_unit=quote_unit, min_price_chg=self.min_price_chg, gateway_name="DB", ) return opbasic @staticmethod def save_all(objs: List["DbOptionsBasic"]): """ save a list of objects, update if exists. """ dicts = [i.to_dict() for i in objs] with db.atomic(): if driver is Driver.POSTGRESQL: for opbasic in dicts: DbOptionsBasic.insert(opbasic).on_conflict( update=opbasic, conflict_target=( DbOptionsBasic.symbol, DbOptionsBasic.exchange, ), ).execute() else: for c in chunked(dicts, 50): DbOptionsBasic.insert_many( c).on_conflict_replace().execute() db.connect() db.create_tables([DbBarData, DbTickData, DbOptionsBasic]) return DbBarData, DbTickData, DbOptionsBasic
def init_models(db: Database, driver: Driver): class BcolzMeta(ModelBase): sid = AutoField() symbol = CharField(null=False) exchange = CharField(null=False) year = IntegerField(null=False) ohlc_ratio = IntegerField(null=False) minutes_per_day = IntegerField(null=False) calendar_name = CharField(null=False) start_session = DateTimeField(null=False) end_session = DateTimeField(null=False) version = IntegerField(null=False) class Meta: database = db table_name = 'bcolz_meta' indexes = ( # create a unique on from/to/date (('symbol', 'exchange', 'year'), True), ) class DbBarData: """ Candlestick bar data for database storage. Index is defined unique with datetime, interval, symbol """ fields = ['open', 'open_int', 'high', 'low', 'close', 'volume'] @staticmethod def from_bar(bar: BarData): """ Generate DbBarData object from BarData. """ db_bar = dict( index = bar.datetime, volume = bar.volume, open_int = bar.open_interest, open = bar.open_price, high = bar.high_price, low = bar.low_price, close = bar.close_price ) return db_bar def to_bar(self, symbol, exchange, data): """ Generate BarData object from DbBarData. """ bar = BarData( symbol=self.symbol, exchange=Exchange(self.exchange), datetime=data.datetime, interval=Interval(Interval.MINUTE), volume=data.volume, open_price=data.open, high_price=data.high, open_interest=data.open_int, low_price=data.low, close_price=data.close, gateway_name="DB", ) return bar # @staticmethod # def save_all(objs: List["DbBarData"]): # """ # save a list of objects, update if exists. # """ # dicts = [i.to_dict() for i in objs] # with db.atomic(): # if driver is Driver.POSTGRESQL: # for bar in dicts: # DbBarData.insert(bar).on_conflict( # update=bar, # conflict_target=( # DbBarData.symbol, # DbBarData.exchange, # DbBarData.interval, # DbBarData.datetime, # ), # ).execute() # else: # for c in chunked(dicts, 50): # DbBarData.insert_many( # c).on_conflict_replace().execute() class DbTickData: """ Tick data for database storage. Index is defined unique with (datetime, symbol) """ id = AutoField() symbol: str = CharField() exchange: str = CharField() datetime: datetime = DateTimeField() name: str = CharField() volume: float = FloatField() open_interest: float = FloatField() last_price: float = FloatField() last_volume: float = FloatField() limit_up: float = FloatField() limit_down: float = FloatField() open_price: float = FloatField() high_price: float = FloatField() low_price: float = FloatField() pre_close: float = FloatField() bid_price_1: float = FloatField() bid_price_2: float = FloatField(null=True) bid_price_3: float = FloatField(null=True) bid_price_4: float = FloatField(null=True) bid_price_5: float = FloatField(null=True) ask_price_1: float = FloatField() ask_price_2: float = FloatField(null=True) ask_price_3: float = FloatField(null=True) ask_price_4: float = FloatField(null=True) ask_price_5: float = FloatField(null=True) bid_volume_1: float = FloatField() bid_volume_2: float = FloatField(null=True) bid_volume_3: float = FloatField(null=True) bid_volume_4: float = FloatField(null=True) bid_volume_5: float = FloatField(null=True) ask_volume_1: float = FloatField() ask_volume_2: float = FloatField(null=True) ask_volume_3: float = FloatField(null=True) ask_volume_4: float = FloatField(null=True) ask_volume_5: float = FloatField(null=True) # class Meta: # database = db # indexes = ((("symbol", "exchange", "datetime"), True),) @staticmethod def from_tick(tick: TickData): """ Generate DbTickData object from TickData. """ db_tick = DbTickData() db_tick.symbol = tick.symbol db_tick.exchange = tick.exchange.value db_tick.datetime = tick.datetime db_tick.name = tick.name db_tick.volume = tick.volume db_tick.open_interest = tick.open_interest db_tick.last_price = tick.last_price db_tick.last_volume = tick.last_volume db_tick.limit_up = tick.limit_up db_tick.limit_down = tick.limit_down db_tick.open_price = tick.open_price db_tick.high_price = tick.high_price db_tick.low_price = tick.low_price db_tick.pre_close = tick.pre_close db_tick.bid_price_1 = tick.bid_price_1 db_tick.ask_price_1 = tick.ask_price_1 db_tick.bid_volume_1 = tick.bid_volume_1 db_tick.ask_volume_1 = tick.ask_volume_1 if tick.bid_price_2: db_tick.bid_price_2 = tick.bid_price_2 db_tick.bid_price_3 = tick.bid_price_3 db_tick.bid_price_4 = tick.bid_price_4 db_tick.bid_price_5 = tick.bid_price_5 db_tick.ask_price_2 = tick.ask_price_2 db_tick.ask_price_3 = tick.ask_price_3 db_tick.ask_price_4 = tick.ask_price_4 db_tick.ask_price_5 = tick.ask_price_5 db_tick.bid_volume_2 = tick.bid_volume_2 db_tick.bid_volume_3 = tick.bid_volume_3 db_tick.bid_volume_4 = tick.bid_volume_4 db_tick.bid_volume_5 = tick.bid_volume_5 db_tick.ask_volume_2 = tick.ask_volume_2 db_tick.ask_volume_3 = tick.ask_volume_3 db_tick.ask_volume_4 = tick.ask_volume_4 db_tick.ask_volume_5 = tick.ask_volume_5 return db_tick def to_tick(self): """ Generate TickData object from DbTickData. """ tick = TickData( symbol=self.symbol, exchange=Exchange(self.exchange), datetime=self.datetime, name=self.name, volume=self.volume, open_interest=self.open_interest, last_price=self.last_price, last_volume=self.last_volume, limit_up=self.limit_up, limit_down=self.limit_down, open_price=self.open_price, high_price=self.high_price, low_price=self.low_price, pre_close=self.pre_close, bid_price_1=self.bid_price_1, ask_price_1=self.ask_price_1, bid_volume_1=self.bid_volume_1, ask_volume_1=self.ask_volume_1, gateway_name="DB", ) if self.bid_price_2: tick.bid_price_2 = self.bid_price_2 tick.bid_price_3 = self.bid_price_3 tick.bid_price_4 = self.bid_price_4 tick.bid_price_5 = self.bid_price_5 tick.ask_price_2 = self.ask_price_2 tick.ask_price_3 = self.ask_price_3 tick.ask_price_4 = self.ask_price_4 tick.ask_price_5 = self.ask_price_5 tick.bid_volume_2 = self.bid_volume_2 tick.bid_volume_3 = self.bid_volume_3 tick.bid_volume_4 = self.bid_volume_4 tick.bid_volume_5 = self.bid_volume_5 tick.ask_volume_2 = self.ask_volume_2 tick.ask_volume_3 = self.ask_volume_3 tick.ask_volume_4 = self.ask_volume_4 tick.ask_volume_5 = self.ask_volume_5 return tick @staticmethod def save_all(objs: List["DbTickData"]): dicts = [i.to_dict() for i in objs] with db.atomic(): if driver is Driver.POSTGRESQL: for tick in dicts: DbTickData.insert(tick).on_conflict( update=tick, conflict_target=( DbTickData.symbol, DbTickData.exchange, DbTickData.datetime, ), ).execute() else: for c in chunked(dicts, 50): DbTickData.insert_many(c).on_conflict_replace().execute() db.connect() db.create_tables([BcolzMeta]) return BcolzMeta, DbBarData, DbTickData
def init_models(db: Database, driver: Driver): class DbBarData(ModelBase): """ Candlestick bar data for database storage. Index is defined unique with datetime, interval, symbol """ id = AutoField() symbol: str = CharField(max_length=32) exchange: str = CharField(max_length=32) datetime: datetime = DateTimeField() interval: str = CharField(max_length=32) volume: float = FloatField() open_interest: float = FloatField() open_price: float = FloatField() high_price: float = FloatField() low_price: float = FloatField() close_price: float = FloatField() class Meta: database = db indexes = ((("symbol", "exchange", "interval", "datetime"), True), ) @staticmethod def from_bar(bar: BarData): """ Generate DbBarData object from BarData. """ db_bar = DbBarData() db_bar.symbol = bar.symbol db_bar.exchange = bar.exchange.value db_bar.datetime = bar.datetime db_bar.interval = bar.interval.value db_bar.volume = bar.volume db_bar.open_interest = bar.open_interest db_bar.open_price = bar.open_price db_bar.high_price = bar.high_price db_bar.low_price = bar.low_price db_bar.close_price = bar.close_price return db_bar def to_bar(self): """ Generate BarData object from DbBarData. """ bar = BarData( symbol=self.symbol, exchange=Exchange(self.exchange), datetime=self.datetime, interval=Interval(self.interval), volume=self.volume, open_price=self.open_price, high_price=self.high_price, open_interest=self.open_interest, low_price=self.low_price, close_price=self.close_price, gateway_name="DB", ) return bar @staticmethod def save_all(objs: List["DbBarData"]): """ save a list of objects, update if exists. """ dicts = [i.to_dict() for i in objs] with db.atomic(): if driver is Driver.POSTGRESQL: for bar in dicts: DbBarData.insert(bar).on_conflict( update=bar, conflict_target=( DbBarData.symbol, DbBarData.exchange, DbBarData.interval, DbBarData.datetime, ), ).execute() else: for c in chunked(dicts, 50): DbBarData.insert_many( c).on_conflict_replace().execute() class DbTickData(ModelBase): """ Tick data for database storage. Index is defined unique with (datetime, symbol) """ id = AutoField() symbol: str = CharField(max_length=32) exchange: str = CharField(max_length=32) datetime: datetime = DateTimeField() name: str = CharField(max_length=32) volume: float = FloatField() open_interest: float = FloatField() last_price: float = FloatField() last_volume: float = FloatField() limit_up: float = FloatField() limit_down: float = FloatField() open_price: float = FloatField() high_price: float = FloatField() low_price: float = FloatField() pre_close: float = FloatField() bid_price_1: float = FloatField() bid_price_2: float = FloatField(null=True) bid_price_3: float = FloatField(null=True) bid_price_4: float = FloatField(null=True) bid_price_5: float = FloatField(null=True) ask_price_1: float = FloatField() ask_price_2: float = FloatField(null=True) ask_price_3: float = FloatField(null=True) ask_price_4: float = FloatField(null=True) ask_price_5: float = FloatField(null=True) bid_volume_1: float = FloatField() bid_volume_2: float = FloatField(null=True) bid_volume_3: float = FloatField(null=True) bid_volume_4: float = FloatField(null=True) bid_volume_5: float = FloatField(null=True) ask_volume_1: float = FloatField() ask_volume_2: float = FloatField(null=True) ask_volume_3: float = FloatField(null=True) ask_volume_4: float = FloatField(null=True) ask_volume_5: float = FloatField(null=True) class Meta: database = db indexes = ((("symbol", "exchange", "datetime"), True), ) @staticmethod def from_tick(tick: TickData): """ Generate DbTickData object from TickData. """ db_tick = DbTickData() db_tick.symbol = tick.symbol db_tick.exchange = tick.exchange.value db_tick.datetime = tick.datetime db_tick.name = tick.name db_tick.volume = tick.volume db_tick.open_interest = tick.open_interest db_tick.last_price = tick.last_price db_tick.last_volume = tick.last_volume db_tick.limit_up = tick.limit_up db_tick.limit_down = tick.limit_down db_tick.open_price = tick.open_price db_tick.high_price = tick.high_price db_tick.low_price = tick.low_price db_tick.pre_close = tick.pre_close db_tick.bid_price_1 = tick.bid_price_1 db_tick.ask_price_1 = tick.ask_price_1 db_tick.bid_volume_1 = tick.bid_volume_1 db_tick.ask_volume_1 = tick.ask_volume_1 if tick.bid_price_2: db_tick.bid_price_2 = tick.bid_price_2 db_tick.bid_price_3 = tick.bid_price_3 db_tick.bid_price_4 = tick.bid_price_4 db_tick.bid_price_5 = tick.bid_price_5 db_tick.ask_price_2 = tick.ask_price_2 db_tick.ask_price_3 = tick.ask_price_3 db_tick.ask_price_4 = tick.ask_price_4 db_tick.ask_price_5 = tick.ask_price_5 db_tick.bid_volume_2 = tick.bid_volume_2 db_tick.bid_volume_3 = tick.bid_volume_3 db_tick.bid_volume_4 = tick.bid_volume_4 db_tick.bid_volume_5 = tick.bid_volume_5 db_tick.ask_volume_2 = tick.ask_volume_2 db_tick.ask_volume_3 = tick.ask_volume_3 db_tick.ask_volume_4 = tick.ask_volume_4 db_tick.ask_volume_5 = tick.ask_volume_5 return db_tick def to_tick(self): """ Generate TickData object from DbTickData. """ tick = TickData( symbol=self.symbol, exchange=Exchange(self.exchange), datetime=self.datetime, name=self.name, volume=self.volume, open_interest=self.open_interest, last_price=self.last_price, last_volume=self.last_volume, limit_up=self.limit_up, limit_down=self.limit_down, open_price=self.open_price, high_price=self.high_price, low_price=self.low_price, pre_close=self.pre_close, bid_price_1=self.bid_price_1, ask_price_1=self.ask_price_1, bid_volume_1=self.bid_volume_1, ask_volume_1=self.ask_volume_1, gateway_name="DB", ) if self.bid_price_2: tick.bid_price_2 = self.bid_price_2 tick.bid_price_3 = self.bid_price_3 tick.bid_price_4 = self.bid_price_4 tick.bid_price_5 = self.bid_price_5 tick.ask_price_2 = self.ask_price_2 tick.ask_price_3 = self.ask_price_3 tick.ask_price_4 = self.ask_price_4 tick.ask_price_5 = self.ask_price_5 tick.bid_volume_2 = self.bid_volume_2 tick.bid_volume_3 = self.bid_volume_3 tick.bid_volume_4 = self.bid_volume_4 tick.bid_volume_5 = self.bid_volume_5 tick.ask_volume_2 = self.ask_volume_2 tick.ask_volume_3 = self.ask_volume_3 tick.ask_volume_4 = self.ask_volume_4 tick.ask_volume_5 = self.ask_volume_5 return tick @staticmethod def save_all(objs: List["DbTickData"]): dicts = [i.to_dict() for i in objs] with db.atomic(): if driver is Driver.POSTGRESQL: for tick in dicts: DbTickData.insert(tick).on_conflict( update=tick, conflict_target=( DbTickData.symbol, DbTickData.exchange, DbTickData.datetime, ), ).execute() else: for c in chunked(dicts, 50): DbTickData.insert_many( c).on_conflict_replace().execute() class DbTradeData(ModelBase): id = AutoField() symbol: str = CharField(max_length=32) exchange: str = CharField(max_length=32) orderid: str = CharField(max_length=32) tradeid: str = CharField(max_length=32) direction: str = CharField(max_length=32) offset: str = CharField(max_length=32) price: float = FloatField() volume: float = FloatField() time: str = CharField(max_length=32) v0: float = CharField(max_length=32) v1: float = CharField(max_length=32) v2: float = CharField(max_length=32) v3: float = CharField(max_length=32) v4: float = CharField(max_length=32) v5: float = CharField(max_length=32) v6: float = CharField(max_length=32) v7: float = CharField(max_length=32) v8: float = CharField(max_length=32) v9: float = CharField(max_length=32) v10: float = CharField(max_length=32) v11: float = CharField(max_length=32) v12: float = CharField(max_length=32) v13: float = CharField(max_length=32) v14: float = CharField(max_length=32) v15: float = CharField(max_length=32) v16: float = CharField(max_length=32) v17: float = CharField(max_length=32) v18: float = CharField(max_length=32) v19: float = CharField(max_length=32) # def init(self): # self.database_db = g_db # self.database_db.connect() # self.database_db.create_tables([DbTradeData]) # @classmethod # def get_db(cls): # return cls.database_db class Meta: database = db indexes = ((("symbol", "exchange", "time"), False), ) def from_trade(self, trade: TradeData): """ Generate DbTradeData object from TradeData. """ self.symbol = trade.symbol self.exchange = trade.exchange.value self.orderid = trade.orderid self.tradeid = trade.tradeid self.direction = trade.direction.value self.offset = trade.offset.value self.price = trade.price self.volume = trade.volume self.time = trade.time def from_var(self, var: dict): for n, (k, v) in enumerate(var.items()): exec('self.v{} = {}'.format(n, str(v))) def save_trade(self): data = self.to_dict() self.insert(data).execute() class DBBarCalcData(ModelBase): id = AutoField() symbol: str = CharField(max_length=32) exchange: str = CharField(max_length=32) datetime: datetime = DateTimeField() interval: str = CharField(max_length=32) volume: float = FloatField() open_interest: float = FloatField() open_price: float = FloatField() high_price: float = FloatField() low_price: float = FloatField() close_price: float = FloatField() time: str = CharField(max_length=32) v0: float = CharField(max_length=32) v1: float = CharField(max_length=32) v2: float = CharField(max_length=32) v3: float = CharField(max_length=32) v4: float = CharField(max_length=32) v5: float = CharField(max_length=32) v6: float = CharField(max_length=32) v7: float = CharField(max_length=32) v8: float = CharField(max_length=32) v9: float = CharField(max_length=32) v10: float = CharField(max_length=32) v11: float = CharField(max_length=32) v12: float = CharField(max_length=32) v13: float = CharField(max_length=32) v14: float = CharField(max_length=32) v15: float = CharField(max_length=32) v16: float = CharField(max_length=32) v17: float = CharField(max_length=32) v18: float = CharField(max_length=32) v19: float = CharField(max_length=32) class Meta: database = db indexes = ((("symbol", "exchange", "interval", "datetime"), True), ) def from_bar(self, bar: BarData): self.symbol = bar.symbol self.exchange = bar.exchange.value self.datetime = bar.datetime #self.interval = bar.interval.value self.volume = bar.volume self.open_interest = bar.open_interest self.open_price = bar.open_price self.high_price = bar.high_price self.low_price = bar.low_price self.close_price = bar.close_price def from_var(self, var: dict): for n, (k, v) in enumerate(var.items()): exec('self.v{} = {}'.format(n, str(v))) def save_bar_calc(self): data = self.to_dict() self.insert(data).execute() db.connect() db.create_tables([DbBarData, DbTickData, DbTradeData, DBBarCalcData]) trade = DbTradeData() bar_calc = DBBarCalcData() trade.truncate_table() bar_calc.truncate_table() return DbBarData, DbTickData, trade, bar_calc
def init_task(db: Database, driver: Driver): class DownLoadTask(ModelBase): """ Candlestick bar data for database storage. Index is defined unique with datetime, interval, symbol """ maintype: str = CharField(max_length=32) symbol: str = CharField(max_length=32) exchange: str = CharField(max_length=32) interval: int = IntegerField() startdate: datetime = DateTimeField() enddate: datetime = DateTimeField() status: str = CharField(max_length=32, null=True) detaildate: datetime = DateTimeField(null=True) processdate: datetime = DateTimeField(null=True) class Meta: database = db db_table = 'download_task' indexes = ((("maintype", "symbol", "exchange", "interval", "startdate", "enddate"), True), ) @staticmethod def from_task(task: DownloadTaskData): """ Generate DbBarData object from BarData. """ db_task = DownLoadTask() db_task.maintype = task.maintype.value db_task.symbol = task.symbol db_task.exchange = task.exchange.value db_task.interval = task.interval db_task.startdate = task.startdate db_task.enddate = task.enddate db_task.status = task.status.value db_task.detaildate = task.detaildate db_task.processdate = task.processdate return db_task def to_task(self): """ Generate BarData object from DbBarData. """ task = DownLoadTask( maintype=MainType(self.maintype), symbol=self.symbol, exchange=Exchange(self.exchange), interval=self.interval, startdate=self.startdate, enddate=self.enddate, status=TaskStatus(self.status), detaildate=self.detaildate, processdate=self.processdate, ) return task @staticmethod def save_all(objs: List["DownLoadTask"]): """ save a list of objects, update if exists. """ dicts = [i.to_dict() for i in objs] with db.atomic(): if driver is Driver.POSTGRESQL: for task in dicts: DownLoadTask.insert(task).on_conflict( update=task, conflict_target=( DownLoadTask.maintype, DownLoadTask.symbol, DownLoadTask.exchange, DownLoadTask.interval, DownLoadTask.startdate, DownLoadTask.enddate, ), ).execute() else: for c in chunked(dicts, 50): DownLoadTask.insert_many( c).on_conflict_ignore().execute() class DownloadTaskDetail(ModelBase): """ Tick data for database storage. Index is defined unique with (datetime, symbol) """ maintype: str = CharField(max_length=32) symbol: str = CharField(max_length=32) exchange: str = CharField(max_length=32) interval: int = IntegerField() detaildate: datetime = DateTimeField() status: str = CharField(max_length=32) processdate: datetime = DateTimeField() breakpointdate: datetime = DateTimeField() class Meta: database = db db_table = 'download_task_detail' indexes = ((("maintype", "symbol", "exchange", "interval", "detaildate"), True), ) @staticmethod def from_task_detail(task_detail: DownloadTaskDetailData): """ Generate DbTickData object from TickData. """ db_task_detail = DownloadTaskDetail() db_task_detail.maintype = task_detail.maintype.value db_task_detail.symbol = task_detail.symbol db_task_detail.exchange = task_detail.exchange.value db_task_detail.interval = task_detail.interval db_task_detail.detaildate = task_detail.detaildate db_task_detail.status = task_detail.status.value db_task_detail.processdate = task_detail.processdate db_task_detail.breakpointdate = task_detail.breakpointdate return db_task_detail def to_task_detail(self): """ Generate TickData object from DbTickData. """ task_detail = DownloadTaskDetailData( maintype=MainType(self.maintype), symbol=self.symbol, exchange=Exchange(self.exchange), interval=self.interval, detaildate=self.detaildate, status=TaskStatus(self.status), processdate=self.processdate, breakpointdate=self.breakpointdate, ) return task_detail @staticmethod def save_all(objs: List["DownloadTaskDetail"]): dicts = [i.to_dict() for i in objs] with db.atomic(): if driver is Driver.POSTGRESQL: for task_detail in dicts: DownloadTaskDetail.insert(task_detail).on_conflict( update=task_detail, conflict_target=( DownloadTaskDetail.maintype, DownloadTaskDetail.symbol, DownloadTaskDetail.exchange, DownloadTaskDetail.interval, DownloadTaskDetail.detaildate, ), ).execute() else: for c in chunked(dicts, 50): DownloadTaskDetail.insert_many( c).on_conflict_ignore().execute() db.connect() db.create_tables([DownLoadTask, DownloadTaskDetail]) return DownLoadTask, DownloadTaskDetail