def initialize(self, obj: Database):
super(WukongoDB, self).initialize(obj)
if obj is not None:
# Importing the models at runtime to avoid circular import issue
from Models.User import User
from Models.BasicTypes import Move
from Models.Game import Game, GameMove, GameUser
obj.create_tables([User, Move, Game, GameMove, GameUser])
def create_database(database_instance: peewee.Database,
tables: List[peewee.Table]):
"""
Create empty database with tables based
:param database_instance: Peewee Database instance
:param tables: List of tables to be created
"""
with database_instance:
database_instance.create_tables(tables)
def fetch_columns(driver: Driver, db: Database, table_name: str):
if driver is Driver.SQLITE:
c = db.execute_sql(f"PRAGMA table_info({table_name});")
res = c.fetchall()
return [i[1] for i in res]
else:
c = db.execute_sql(f"SELECT column_name "
f"FROM INFORMATION_SCHEMA.COLUMNS "
f"WHERE table_name = '{table_name}';")
res = c.fetchall()
return [i[0] for i in res]
def drop_index_if_exists(driver: Driver, db: Database, table_name: str,
index_name: str):
if driver is Driver.MYSQL:
# mysql needs DROP ... ON
indexes = fetch_indexes(driver, db, table_name)
if index_name in indexes:
db.execute_sql(f"DROP INDEX `{index_name}` ON `{table_name}`;")
return
else:
db.execute_sql(f'DROP INDEX IF EXISTS "{index_name}";')
return
def down_grade(driver: Driver, db: Database):
assert driver is not Driver.SQLITE # sqlite doesn't support drop column
columns = fetch_columns(driver, db, "dbbardata")
if 'open_interest' in columns:
db.execute_sql("ALTER TABLE dbbardata " " DROP COLUMN open_interest;")
columns = fetch_columns(driver, db, "dbtickdata")
if 'open_interest' in columns:
db.execute_sql("ALTER TABLE dbtickdata " " DROP COLUMN open_interest;")
print("down grade succeed!")
def upgrade(driver: Driver, db: Database):
columns = fetch_columns(driver, db, "dbbardata")
if 'open_interest' not in columns:
db.execute_sql("ALTER TABLE dbbardata "
" ADD COLUMN open_interest FLOAT DEFAULT 0;")
columns = fetch_columns(driver, db, "dbtickdata")
if 'open_interest' not in columns:
db.execute_sql("ALTER TABLE dbtickdata "
" ADD COLUMN open_interest FLOAT DEFAULT 0;")
print("upgrade succeed!")
def initialize_database(
db: peewee.Database,
db_path: Path,
models: Iterable[Type[peewee.Model]],
) -> None:
""" Initialize and bind the corresponding database instance
to the database proxy. Create tables for the given models """
db_path.parent.mkdir(parents=True, exist_ok=True)
db.init(str(db_path))
database.initialize(db)
database.create_tables(models, safe=True)
def create_index_if_not_exists(driver: Driver, db: Database, table_name: str,
index_name: str, *args):
indexes = fetch_indexes(driver, db, table_name)
if index_name not in indexes:
if driver is Driver.POSTGRESQL:
# psql does'nt supports ``
keys = ",".join([f'"{key}"' for key in args])
db.execute_sql(f'CREATE UNIQUE INDEX "{index_name}" '
f' ON "{table_name}" ({keys});')
else:
# mysql needs ``, others supports ``
keys = ",".join([f'`{key}`' for key in args])
db.execute_sql(f'CREATE UNIQUE INDEX `{index_name}` '
f' ON `{table_name}` ({keys});')
def fetch_indexes(driver: Driver, db: Database, table_name: str):
if driver is Driver.SQLITE:
c = db.execute_sql(f"PRAGMA index_list({table_name});")
res = c.fetchall()
res = [i[1] for i in res]
return res
elif driver is Driver.POSTGRESQL:
c = db.execute_sql(f"SELECT indexname "
f" FROM pg_indexes"
f" WHERE tablename = '{table_name}';")
return [i[0] for i in c.fetchall()]
else:
c = db.execute_sql(f"SELECT DISTINCT INDEX_NAME"
f" FROM INFORMATION_SCHEMA.STATISTICS"
f" WHERE TABLE_NAME = '{table_name}';")
return [i[0] for i in c.fetchall()]
def mock_data_generation(database: Database):
AgencySyncConfiguration._meta.schema = ''
JobGlobalOption._meta.schema = ''
database.create_tables([JobGlobalOption, AgencySyncConfiguration])
JobGlobalOption.create(option_name='gi_sync_agenzia.gi_homepage',
option_value='http://gi.com/')
JobGlobalOption.create(option_name='gi_sync_agenzia.connection_timeout',
option_value='10')
AgencySyncConfiguration.create(agency_description="Rome agency",
agency_homepage="http://www.domain.com/",
export_url='https://pannello.gestionaleimmobiliare.it/export_xml_annunci1.html')
AgencySyncConfiguration.create(agency_description="Milan agency",
agency_homepage="http://www.domain2.com/",
export_url='https://pannello.gestionaleimmobiliare.it/export_xml_annunci2.html',
opt_geo_id=True, opt_stima=True)
async def _get_known_channels(self,
db: Database) -> List[discord.TextChannel]:
"""Récupérer la liste des channels connus en db"""
known_channels = []
with db:
with db.bind_ctx([Message]):
for known_channel_id in Message.select(
Message.channel_id).distinct():
known_channel = self.bot.get_channel(
known_channel_id.channel_id)
if not isinstance(known_channel, discord.TextChannel):
print(
f"Impossible de déterminer le channel correspondant à l'id {known_channel_id}"
)
continue
known_channels.append(known_channel)
return known_channels
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 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 create_db(db: Database):
with db:
db.create_tables([
models.Product,
])
def __init__(self, database, **connect_kwargs):
Database.__init__(self, SqliteExtAdapter(), database, **connect_kwargs)
class Meta:
database = Database(
"[bigquery-public-data:cloud_storage_geo_index.landsat_index]")
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 create_tables(database: Database):
database.create_tables([Mesh, Node])
def __init__(self, database, **connect_kwargs):
Database.__init__(self, SqliteExtAdapter(), database, **connect_kwargs)
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__(self, database, **connect_kwargs):
adapter = APSWAdapter(connect_kwargs.pop('timeout', None))
Database.__init__(self, adapter, database, **connect_kwargs)
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_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
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 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 bind_all(database: pw.Database):
with database.bind_ctx(ALL_TABLES):
yield
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__(self, database: Database, model):
logging.Handler.__init__(self)
database.bind([model])
database.create_tables([model])
self.model = model
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__(self, database, **connect_kwargs):
Database.__init__(self, PostgresqlExtAdapter(), database, **connect_kwargs)
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
