def connect(self, setting: dict):
""""""
if not setting['password'].startswith("md5:"):
setting['password'] = "******" + hashlib.md5(setting['password'].encode()).hexdigest()
username = setting['username']
password = setting['password']
config_path = str(get_file_path("vnoes.ini"))
with open(config_path, "wt") as f:
if 'test' in setting:
log_level = 'DEBUG'
log_mode = 'console'
else:
log_level = 'WARNING'
log_mode = 'file'
log_dir = get_file_path('oes')
log_path = os.path.join(log_dir, 'log.log')
if not os.path.exists(log_dir):
os.mkdir(log_dir)
content = config_template.format(**setting,
log_level=log_level,
log_mode=log_mode,
log_path=log_path)
f.write(content)
self.md_api.tcp_server = setting['md_tcp_server']
self.md_api.qry_server = setting['md_qry_server']
Thread(target=self._connect_md_sync, args=(config_path, username, password)).start()
self.td_api.ord_server = setting['td_ord_server']
self.td_api.rpt_server = setting['td_rpt_server']
self.td_api.qry_server = setting['td_qry_server']
self.td_api.hdd_serial = setting['hdd_serial']
Thread(target=self._connect_td_sync, args=(config_path, username, password)).start()
def __initDataManager(self, file_preffix):
database = f"{file_preffix}{self.__code}_{self.__exchange.value.__str__()}.db"
path = str(get_file_path(database))
db = SqliteDatabase(path)
self.__database_manager = init_by_sql_databease(db)
self.__updateNewestTime()
def init(driver: Driver, settings: dict):
database = settings["database"]
store_path = str(get_folder_path('db_store'))
path = str(get_file_path(database))
db = SqliteDatabase(path)
bcolz_meta, bar, tick = init_models(db, driver)
return SqlManager(store_path, bcolz_meta, bar, tick)
def init():
settings = get_settings("database.")
database = settings["database"]
path = str(get_file_path(database))
db = SqliteDatabase(path)
DbCtaTrade, DbCtaSignal, DbCtaPosition, DbCtaParams = init_models(db)
return RecorderDbManager(DbCtaTrade, DbCtaSignal, DbCtaPosition,
DbCtaParams)
def connect(self, setting: dict) -> None:
""""""
if not setting['password'].startswith("md5:"):
setting['password'] = "******" + \
hashlib.md5(setting['password'].encode()).hexdigest()
username = setting['username']
password = setting['password']
config_path = str(get_file_path("vnoes.ini"))
with open(config_path, "wt") as f:
if 'test' in setting:
log_level = 'DEBUG'
log_mode = 'console'
else:
log_level = 'WARNING'
log_mode = 'file'
log_dir = get_file_path('oes')
log_path = os.path.join(log_dir, 'log.log')
if not os.path.exists(log_dir):
os.mkdir(log_dir)
content = config_template.format(**setting,
log_level=log_level,
log_mode=log_mode,
log_path=log_path)
f.write(content)
self.md_api.connect(config_path=config_path,
username=username,
password=password)
self.td_api.connect(
config_path=config_path,
username=username,
password=password,
ord_server=setting['td_ord_server'],
rpt_server=setting['td_rpt_server'],
hdd_serial=setting['hdd_serial'],
qry_server=setting['td_qry_server'],
customize_ip=setting['customize_ip'],
customize_mac=setting['customize_mac'],
)
def connect(self, setting: dict):
""""""
if not setting['password'].startswith("md5:"):
setting['password'] = "******" + \
hashlib.md5(setting['password'].encode()).hexdigest()
username = setting['username']
password = setting['password']
config_path = str(get_file_path("vnoes.ini"))
with open(config_path, "wt") as f:
if 'test' in setting:
log_level = 'DEBUG'
log_mode = 'console'
else:
log_level = 'WARNING'
log_mode = 'file'
log_dir = get_file_path('oes')
log_path = os.path.join(log_dir, 'log.log')
if not os.path.exists(log_dir):
os.mkdir(log_dir)
content = config_template.format(**setting,
log_level=log_level,
log_mode=log_mode,
log_path=log_path)
f.write(content)
self.md_api.tcp_server = setting['md_tcp_server']
self.md_api.qry_server = setting['md_qry_server']
Thread(target=self._connect_md_sync,
args=(config_path, username, password)).start()
self.td_api.ord_server = setting['td_ord_server']
self.td_api.rpt_server = setting['td_rpt_server']
self.td_api.qry_server = setting['td_qry_server']
self.td_api.hdd_serial = setting['hdd_serial']
Thread(target=self._connect_td_sync,
args=(config_path, username, password)).start()
def __init__(self, settings_dict):
self.settings_dict = settings_dict
self.file_path_str = get_file_path(settings_dict['database'])
os_str = platform.system()
if os_str == "Windows":
sqlite_os = "/"
elif os_str == "Linux":
sqlite_os = "//"
else:
print(f"OS is {os_str}. DBoperation may meet problem.")
self.engine = create_engine(
f"{self.settings_dict['driver']}://{sqlite_os}{self.file_path_str}"
)
class IbApi(EWrapper):
""""""
data_filename = "ib_contract_data.db"
data_filepath = str(get_file_path(data_filename))
def __init__(self, gateway: BaseGateway):
""""""
super().__init__()
self.gateway = gateway
self.gateway_name = gateway.gateway_name
self.status = False
self.reqid = 0
self.orderid = 0
self.clientid = 0
self.account = ""
self.ticks = {}
self.orders = {}
self.accounts = {}
self.contracts = {}
self.tick_exchange = {}
self.history_req = None
self.history_condition = Condition()
self.history_buf = []
self.client = IbClient(self)
self.thread = Thread(target=self.client.run)
def connectAck(self): # pylint: disable=invalid-name
"""
Callback when connection is established.
"""
self.status = True
self.gateway.write_log("IB TWS连接成功")
self.load_contract_data()
def connectionClosed(self): # pylint: disable=invalid-name
"""
Callback when connection is closed.
"""
self.status = False
self.gateway.write_log("IB TWS连接断开")
def nextValidId(self, orderId: int): # pylint: disable=invalid-name
"""
Callback of next valid orderid.
"""
super().nextValidId(orderId)
if not self.orderid:
self.orderid = orderId
def currentTime(self, time: int): # pylint: disable=invalid-name
"""
Callback of current server time of IB.
"""
super().currentTime(time)
dt = datetime.fromtimestamp(time)
time_string = dt.strftime("%Y-%m-%d %H:%M:%S.%f")
msg = f"服务器时间: {time_string}"
self.gateway.write_log(msg)
def error(self, reqId: TickerId, errorCode: int, errorString: str): # pylint: disable=invalid-name
"""
Callback of error caused by specific request.
"""
super().error(reqId, errorCode, errorString)
msg = f"信息通知，代码：{errorCode}，内容: {errorString}"
self.gateway.write_log(msg)
def tickPrice( # pylint: disable=invalid-name
self, reqId: TickerId, tickType: TickType, price: float,
attrib: TickAttrib):
"""
Callback of tick price update.
"""
super().tickPrice(reqId, tickType, price, attrib)
if tickType not in TICKFIELD_IB2VT:
return
tick = self.ticks[reqId]
name = TICKFIELD_IB2VT[tickType]
setattr(tick, name, price)
# Update name into tick data.
contract = self.contracts.get(tick.vt_symbol, None)
if contract:
tick.name = contract.name
# Forex and spot product of IDEALPRO has no tick time and last price.
# We need to calculate locally.
exchange = self.tick_exchange[reqId]
if exchange is Exchange.IDEALPRO:
tick.last_price = (tick.bid_price_1 + tick.ask_price_1) / 2
tick.datetime = datetime.now()
self.gateway.on_tick(copy(tick))
def tickSize(self, reqId: TickerId, tickType: TickType, size: int): # pylint: disable=invalid-name
"""
Callback of tick volume update.
"""
super().tickSize(reqId, tickType, size)
if tickType not in TICKFIELD_IB2VT:
return
tick = self.ticks[reqId]
name = TICKFIELD_IB2VT[tickType]
setattr(tick, name, size)
self.gateway.on_tick(copy(tick))
def tickString(self, reqId: TickerId, tickType: TickType, value: str): # pylint: disable=invalid-name
"""
Callback of tick string update.
"""
super().tickString(reqId, tickType, value)
if tickType != TickTypeEnum.LAST_TIMESTAMP:
return
tick = self.ticks[reqId]
tick.datetime = datetime.fromtimestamp(int(value))
self.gateway.on_tick(copy(tick))
def orderStatus( # pylint: disable=invalid-name
self,
orderId: OrderId,
status: str,
filled: float,
remaining: float,
avgFillPrice: float,
permId: int,
parentId: int,
lastFillPrice: float,
clientId: int,
whyHeld: str,
mktCapPrice: float,
):
"""
Callback of order status update.
"""
super().orderStatus(
orderId,
status,
filled,
remaining,
avgFillPrice,
permId,
parentId,
lastFillPrice,
clientId,
whyHeld,
mktCapPrice,
)
orderid = str(orderId)
order = self.orders.get(orderid, None)
order.traded = filled
# To filter PendingCancel status
order_status = STATUS_IB2VT.get(status, None)
if order_status:
order.status = order_status
self.gateway.on_order(copy(order))
def openOrder( # pylint: disable=invalid-name
self,
orderId: OrderId,
ib_contract: Contract,
ib_order: Order,
orderState: OrderState,
):
"""
Callback when opening new order.
"""
super().openOrder(orderId, ib_contract, ib_order, orderState)
orderid = str(orderId)
order = OrderData(
symbol=ib_contract.conId,
exchange=EXCHANGE_IB2VT.get(ib_contract.exchange,
ib_contract.exchange),
type=ORDERTYPE_IB2VT[ib_order.orderType],
orderid=orderid,
direction=DIRECTION_IB2VT[ib_order.action],
volume=ib_order.totalQuantity,
gateway_name=self.gateway_name,
)
if order.type == OrderType.LIMIT:
order.price = ib_order.lmtPrice
elif order.type == OrderType.STOP:
order.price = ib_order.auxPrice
self.orders[orderid] = order
self.gateway.on_order(copy(order))
def updateAccountValue( # pylint: disable=invalid-name
self, key: str, val: str, currency: str, accountName: str):
"""
Callback of account update.
"""
super().updateAccountValue(key, val, currency, accountName)
if not currency or key not in ACCOUNTFIELD_IB2VT:
return
accountid = f"{accountName}.{currency}"
account = self.accounts.get(accountid, None)
if not account:
account = AccountData(accountid=accountid,
gateway_name=self.gateway_name)
self.accounts[accountid] = account
name = ACCOUNTFIELD_IB2VT[key]
setattr(account, name, float(val))
def updatePortfolio( # pylint: disable=invalid-name
self,
contract: Contract,
position: float,
marketPrice: float,
marketValue: float,
averageCost: float,
unrealizedPNL: float,
realizedPNL: float,
accountName: str,
):
"""
Callback of position update.
"""
super().updatePortfolio(
contract,
position,
marketPrice,
marketValue,
averageCost,
unrealizedPNL,
realizedPNL,
accountName,
)
if contract.exchange:
exchange = EXCHANGE_IB2VT.get(contract.exchange, None)
elif contract.primaryExchange:
exchange = EXCHANGE_IB2VT.get(contract.primaryExchange, None)
else:
exchange = Exchange.SMART # Use smart routing for default
if not exchange:
msg = f"存在不支持的交易所持仓{contract.conId} {contract.exchange} {contract.primaryExchange}"
self.gateway.write_log(msg)
return
try:
ib_size = int(contract.multiplier)
except ValueError:
ib_size = 1
price = averageCost / ib_size
pos = PositionData(
symbol=generate_symbol(contract),
exchange=exchange,
direction=Direction.NET,
volume=position,
price=price,
pnl=unrealizedPNL,
gateway_name=self.gateway_name,
)
self.gateway.on_position(pos)
def updateAccountTime(self, timeStamp: str): # pylint: disable=invalid-name
"""
Callback of account update time.
"""
super().updateAccountTime(timeStamp)
for account in self.accounts.values():
self.gateway.on_account(copy(account))
def contractDetails(self, reqId: int, contractDetails: ContractDetails): # pylint: disable=invalid-name
"""
Callback of contract data update.
"""
super().contractDetails(reqId, contractDetails)
# Generate symbol from ib contract details
ib_contract = contractDetails.contract
if not ib_contract.multiplier:
ib_contract.multiplier = 1
symbol = generate_symbol(ib_contract)
# Generate contract
contract = ContractData(
symbol=symbol,
exchange=EXCHANGE_IB2VT[ib_contract.exchange],
name=contractDetails.longName,
product=PRODUCT_IB2VT[ib_contract.secType],
size=ib_contract.multiplier,
pricetick=contractDetails.minTick,
net_position=True,
history_data=True,
stop_supported=True,
gateway_name=self.gateway_name,
)
if contract.vt_symbol not in self.contracts:
self.gateway.on_contract(contract)
self.contracts[contract.vt_symbol] = contract
self.save_contract_data()
def execDetails(self, reqId: int, contract: Contract,
execution: Execution): # pylint: disable=invalid-name
"""
Callback of trade data update.
"""
super().execDetails(reqId, contract, execution)
# today_date = datetime.now().strftime("%Y%m%d")
trade = TradeData(
symbol=contract.conId,
exchange=EXCHANGE_IB2VT.get(contract.exchange, contract.exchange),
orderid=str(execution.orderId),
tradeid=str(execution.execId),
direction=DIRECTION_IB2VT[execution.side],
price=execution.price,
volume=execution.shares,
time=datetime.strptime(execution.time, "%Y%m%d %H:%M:%S"),
gateway_name=self.gateway_name,
)
self.gateway.on_trade(trade)
def managedAccounts(self, accountsList: str):
"""
Callback of all sub accountid.
"""
super().managedAccounts(accountsList)
if not self.account:
for account_code in accountsList.split(","):
self.account = account_code
self.gateway.write_log(f"当前使用的交易账号为{self.account}")
self.client.reqAccountUpdates(True, self.account)
def historicalData(self, reqId: int, ib_bar: IbBarData):
"""
Callback of history data update.
"""
dt = datetime.strptime(ib_bar.date, "%Y%m%d %H:%M:%S")
bar = BarData(symbol=self.history_req.symbol,
exchange=self.history_req.exchange,
datetime=dt,
interval=self.history_req.interval,
volume=ib_bar.volume,
open_price=ib_bar.open,
high_price=ib_bar.high,
low_price=ib_bar.low,
close_price=ib_bar.close,
gateway_name=self.gateway_name)
self.history_buf.append(bar)
def historicalDataEnd(self, reqId: int, start: str, end: str):
"""
Callback of history data finished.
"""
self.history_condition.acquire()
self.history_condition.notify()
self.history_condition.release()
def connect(self, host: str, port: int, clientid: int, account: str):
"""
Connect to TWS.
"""
if self.status:
return
self.clientid = clientid
self.account = account
self.client.connect(host, port, clientid)
self.thread.start()
self.client.reqCurrentTime()
def close(self):
"""
Disconnect to TWS.
"""
if not self.status:
return
self.status = False
self.client.disconnect()
def subscribe(self, req: SubscribeRequest):
"""
Subscribe tick data update.
"""
if not self.status:
return
if req.exchange not in EXCHANGE_VT2IB:
self.gateway.write_log(f"不支持的交易所{req.exchange}")
return
# Extract ib contract detail
ib_contract = generate_ib_contract(req.symbol, req.exchange)
if not ib_contract:
self.gateway.write_log("代码解析失败，请检查格式是否正确")
return
# Get contract data from TWS.
self.reqid += 1
self.client.reqContractDetails(self.reqid, ib_contract)
# Subscribe tick data and create tick object buffer.
self.reqid += 1
self.client.reqMktData(self.reqid, ib_contract, "", False, False, [])
tick = TickData(
symbol=req.symbol,
exchange=req.exchange,
datetime=datetime.now(),
gateway_name=self.gateway_name,
)
self.ticks[self.reqid] = tick
self.tick_exchange[self.reqid] = req.exchange
def send_order(self, req: OrderRequest):
"""
Send a new order.
"""
if not self.status:
return ""
if req.exchange not in EXCHANGE_VT2IB:
self.gateway.write_log(f"不支持的交易所：{req.exchange}")
return ""
if req.type not in ORDERTYPE_VT2IB:
self.gateway.write_log(f"不支持的价格类型：{req.type}")
return ""
self.orderid += 1
ib_contract = generate_ib_contract(req.symbol, req.exchange)
if not ib_contract:
return ""
ib_order = Order()
ib_order.orderId = self.orderid
ib_order.clientId = self.clientid
ib_order.action = DIRECTION_VT2IB[req.direction]
ib_order.orderType = ORDERTYPE_VT2IB[req.type]
ib_order.totalQuantity = req.volume
ib_order.account = self.account
if req.type == OrderType.LIMIT:
ib_order.lmtPrice = req.price
elif req.type == OrderType.STOP:
ib_order.auxPrice = req.price
self.client.placeOrder(self.orderid, ib_contract, ib_order)
self.client.reqIds(1)
order = req.create_order_data(str(self.orderid), self.gateway_name)
self.gateway.on_order(order)
return order.vt_orderid
def cancel_order(self, req: CancelRequest):
"""
Cancel an existing order.
"""
if not self.status:
return
self.client.cancelOrder(int(req.orderid))
def query_history(self, req: HistoryRequest):
""""""
self.history_req = req
self.reqid += 1
ib_contract = generate_ib_contract(req.symbol, req.exchange)
if req.end:
end = req.end
end_str = end.strftime("%Y%m%d %H:%M:%S")
else:
end = datetime.now()
end_str = ""
delta = end - req.start
days = min(delta.days, 180) # IB only provides 6-month data
duration = f"{days} D"
bar_size = INTERVAL_VT2IB[req.interval]
if req.exchange == Exchange.IDEALPRO:
bar_type = "MIDPOINT"
else:
bar_type = "TRADES"
self.client.reqHistoricalData(self.reqid, ib_contract, end_str,
duration, bar_size, bar_type, 1, 1,
False, [])
self.history_condition.acquire() # Wait for async data return
self.history_condition.wait()
self.history_condition.release()
history = self.history_buf
self.history_buf = [] # Create new buffer list
self.history_req = None
return history
def load_contract_data(self):
""""""
f = shelve.open(self.data_filepath)
self.contracts = f.get("contracts", {})
f.close()
for contract in self.contracts.values():
self.gateway.on_contract(contract)
self.gateway.write_log("本地缓存合约信息加载成功")
def save_contract_data(self):
""""""
f = shelve.open(self.data_filepath)
f["contracts"] = self.contracts
f.close()
def init_sqlite(settings: dict):
database = settings["database"]
path = str(get_file_path(database))
db = SqliteDatabase(path)
return db
from peewee import (
AutoField,
CharField,
Database,
FloatField,
DateTimeField,
Model,
SqliteDatabase
)
from vnpy.trader.constant import Interval, Exchange
# 使用vn.py运行时目录的SQLite数据库
from vnpy.trader.utility import get_file_path
from vnpy.trader.setting import SETTINGS
path = get_file_path(SETTINGS["database.database"])
# 或者可以手动指定数据库位置
# path = "C:\\users\\administrator\\.vntrader\\database.db"
# 创建数据库对象
db = SqliteDatabase(path)
# 创建数据ORM的类
class DbBarData(Model):
"""
Candlestick bar data for database storage.
Index is defined unique with datetime, interval, symbol
"""
class InfluxdbDatabase(BaseDatabase):
""""""
overview_filename = "influxdb_overview"
overview_filepath = str(get_file_path(overview_filename))
def __init__(self) -> None:
""""""
database = SETTINGS["database.database"]
user = SETTINGS["database.user"]
password = SETTINGS["database.password"]
host = SETTINGS["database.host"]
port = SETTINGS["database.port"]
self.client = InfluxDBClient(host, port, user, password, database)
self.client.create_database(database)
def save_bar_data(self, bars: List[BarData]) -> bool:
""""""
json_body = []
bar = bars[0]
vt_symbol = bar.vt_symbol
interval = bar.interval
for bar in bars:
bar.datetime = convert_tz(bar.datetime)
d = {
"measurement": "bar_data",
"tags": {
"vt_symbol": vt_symbol,
"interval": interval.value
},
"time": bar.datetime.isoformat(),
"fields": {
"open_price": bar.open_price,
"high_price": bar.high_price,
"low_price": bar.low_price,
"close_price": bar.close_price,
"volume": bar.volume,
"open_interest": bar.open_interest,
}
}
json_body.append(d)
self.client.write_points(json_body, batch_size=10000)
# Update bar overview
symbol, exchange = extract_vt_symbol(vt_symbol)
key = f"{vt_symbol}_{interval.value}"
f = shelve.open(self.overview_filepath)
overview = f.get(key, None)
if not overview:
overview = BarOverview(
symbol=symbol,
exchange=exchange,
interval=interval
)
overview.count = len(bars)
overview.start = bars[0].datetime
overview.end = bars[-1].datetime
else:
overview.start = min(overview.start, bars[0].datetime)
overview.end = max(overview.end, bars[-1].datetime)
query = (
"select count(close_price) from bar_data"
" where vt_symbol=$vt_symbol"
" and interval=$interval"
)
bind_params = {
"vt_symbol": vt_symbol,
"interval": interval.value
}
result = self.client.query(query, bind_params=bind_params)
points = result.get_points()
for d in points:
overview.count = d["count"]
f[key] = overview
f.close()
def save_tick_data(self, ticks: List[TickData]) -> bool:
""""""
json_body = []
tick = ticks[0]
vt_symbol = tick.vt_symbol
for tick in ticks:
tick.datetime = convert_tz(tick.datetime)
d = {
"measurement": "tick_data",
"tags": {
"vt_symbol": vt_symbol
},
"time": tick.datetime.isoformat(),
"fields": {
"name": tick.name,
"volume": tick.volume,
"open_interest": tick.open_interest,
"last_price": tick.last_price,
"last_volume": tick.last_volume,
"limit_up": tick.limit_up,
"limit_down": tick.limit_down,
"open_price": tick.open_price,
"high_price": tick.high_price,
"low_price": tick.low_price,
"pre_close": tick.pre_close,
"bid_price_1": tick.bid_price_1,
"bid_price_2": tick.bid_price_2,
"bid_price_3": tick.bid_price_3,
"bid_price_4": tick.bid_price_4,
"bid_price_5": tick.bid_price_5,
"ask_price_1": tick.ask_price_1,
"ask_price_2": tick.ask_price_2,
"ask_price_3": tick.ask_price_3,
"ask_price_4": tick.ask_price_4,
"ask_price_5": tick.ask_price_5,
"bid_volume_1": tick.bid_volume_1,
"bid_volume_2": tick.bid_volume_2,
"bid_volume_3": tick.bid_volume_3,
"bid_volume_4": tick.bid_volume_4,
"bid_volume_5": tick.bid_volume_5,
"ask_volume_1": tick.ask_volume_1,
"ask_volume_2": tick.ask_volume_2,
"ask_volume_3": tick.ask_volume_3,
"ask_volume_4": tick.ask_volume_4,
"ask_volume_5": tick.ask_volume_5,
}
}
json_body.append(d)
self.client.write_points(json_body, batch_size=10000)
def load_bar_data(
self,
symbol: str,
exchange: Exchange,
interval: Interval,
start: datetime,
end: datetime
) -> List[BarData]:
""""""
query = (
"select * from bar_data"
" where vt_symbol=$vt_symbol"
" and interval=$interval"
f" and time >= '{start.date().isoformat()}'"
f" and time <= '{end.date().isoformat()}';"
)
bind_params = {
"vt_symbol": generate_vt_symbol(symbol, exchange),
"interval": interval.value
}
result = self.client.query(query, bind_params=bind_params)
points = result.get_points()
bars: List[BarData] = []
for d in points:
dt = datetime.strptime(d["time"], "%Y-%m-%dT%H:%M:%SZ")
bar = BarData(
symbol=symbol,
exchange=exchange,
interval=interval,
datetime=DB_TZ.localize(dt),
open_price=d["open_price"],
high_price=d["high_price"],
low_price=d["low_price"],
close_price=d["close_price"],
volume=d["volume"],
open_interest=d["open_interest"],
gateway_name="DB"
)
bars.append(bar)
return bars
def load_tick_data(
self,
symbol: str,
exchange: Exchange,
start: datetime,
end: datetime
) -> List[TickData]:
""""""
query = (
"select * from tick_data"
" where vt_symbol=$vt_symbol"
f" and time >= '{start.date().isoformat()}'"
f" and time <= '{end.date().isoformat()}';"
)
bind_params = {
"vt_symbol": generate_vt_symbol(symbol, exchange),
}
result = self.client.query(query, bind_params=bind_params)
points = result.get_points()
ticks: List[TickData] = []
for d in points:
dt = datetime.strptime(d["time"], "%Y-%m-%dT%H:%M:%SZ")
tick = TickData(
symbol=symbol,
exchange=exchange,
datetime=DB_TZ.localize(dt),
name=d["name"],
volume=d["volume"],
open_interest=d["open_interest"],
last_price=d["last_price"],
last_volume=d["last_volume"],
limit_up=d["limit_up"],
limit_down=d["limit_down"],
open_price=d["open_price"],
high_price=d["high_price"],
low_price=d["low_price"],
pre_close=d["pre_close"],
bid_price_1=d["bid_price_1"],
bid_price_2=d["bid_price_2"],
bid_price_3=d["bid_price_3"],
bid_price_4=d["bid_price_4"],
bid_price_5=d["bid_price_5"],
ask_price_1=d["ask_price_1"],
ask_price_2=d["ask_price_2"],
ask_price_3=d["ask_price_3"],
ask_price_4=d["ask_price_4"],
ask_price_5=d["ask_price_5"],
bid_volume_1=d["bid_volume_1"],
bid_volume_2=d["bid_volume_2"],
bid_volume_3=d["bid_volume_3"],
bid_volume_4=d["bid_volume_4"],
bid_volume_5=d["bid_volume_5"],
ask_volume_1=d["ask_volume_1"],
ask_volume_2=d["ask_volume_2"],
ask_volume_3=d["ask_volume_3"],
ask_volume_4=d["ask_volume_4"],
ask_volume_5=d["ask_volume_5"],
gateway_name="DB"
)
ticks.append(tick)
return ticks
def delete_bar_data(
self,
symbol: str,
exchange: Exchange,
interval: Interval
) -> int:
""""""
bind_params = {
"vt_symbol": generate_vt_symbol(symbol, exchange),
"interval": interval.value
}
# Query data count
query1 = (
"select count(close_price) from bar_data"
" where vt_symbol=$vt_symbol"
" and interval=$interval"
)
result = self.client.query(query1, bind_params=bind_params)
points = result.get_points()
for d in points:
count = d["count"]
# Delete data
query2 = (
"drop series from bar_data"
" where vt_symbol=$vt_symbol"
" and interval=$interval"
)
self.client.query(query2, bind_params=bind_params)
# Delete overview
f = shelve.open(self.overview_filepath)
vt_symbol = generate_vt_symbol(symbol, exchange)
key = f"{vt_symbol}_{interval.value}"
if key in f:
f.pop(key)
f.close()
return count
def delete_tick_data(
self,
symbol: str,
exchange: Exchange
) -> int:
""""""
bind_params = {
"vt_symbol": generate_vt_symbol(symbol, exchange),
}
# Query data count
query1 = (
"select count(last_price) from tick_data"
" where vt_symbol=$vt_symbol"
)
result = self.client.query(query1, bind_params=bind_params)
points = result.get_points()
for d in points:
count = d["count"]
# Delete data
query2 = (
"drop series from tick_data"
" where vt_symbol=$vt_symbol"
)
self.client.query(query2, bind_params=bind_params)
return count
def get_bar_overview(self) -> List[BarOverview]:
"""
Return data avaible in database.
"""
# Init bar overview if not exists
query = "select count(close_price) from bar_data"
result = self.client.query(query)
points = result.get_points()
data_count = 0
for d in points:
data_count = d["count"]
f = shelve.open(self.overview_filepath)
overview_count = len(f)
if data_count and not overview_count:
self.init_bar_overview()
overviews = list(f.values())
f.close()
return overviews
def init_bar_overview(self) -> None:
"""
Init overview table if not exists.
"""
f = shelve.open(self.overview_filepath)
query: str = "select count(close_price) from bar_data group by *"
result = self.client.query(query)
for k, v in result.items():
tags = k[1]
data = list(v)[0]
vt_symbol = tags["vt_symbol"]
symbol, exchange = extract_vt_symbol(vt_symbol)
interval = Interval(tags["interval"])
overview = BarOverview(
symbol=symbol,
exchange=exchange,
interval=interval,
count=data["count"]
)
overview.start = self.get_bar_datetime(vt_symbol, interval, 1)
overview.end = self.get_bar_datetime(vt_symbol, interval, -1)
key = f"{vt_symbol}_{interval.value}"
f[key] = overview
f.close()
def get_bar_datetime(self, vt_symbol: str, interval: Interval, order: int) -> datetime:
""""""
if order > 0:
keyword = "first"
else:
keyword = "last"
query = (
f"select {keyword}(close_price), * from bar_data"
" where vt_symbol=$vt_symbol"
" and interval=$interval"
)
bind_params = {
"vt_symbol": vt_symbol,
"interval": interval.value
}
result = self.client.query(query, bind_params=bind_params)
points = result.get_points()
for d in points:
dt = datetime.strptime(d["time"], "%Y-%m-%dT%H:%M:%SZ")
return dt
def get_sqlite(dbName):
path = str(get_file_path(dbName))
db = SqliteDatabase(path)
return db
""""""
from datetime import datetime
from typing import List
from peewee import (AutoField, CharField, DateTimeField, FloatField,
IntegerField, Model, SqliteDatabase as
PeeweeSqliteDatabase, ModelSelect, ModelDelete, chunked,
fn)
from vnpy.trader.constant import Exchange, Interval
from vnpy.trader.object import BarData, TickData
from vnpy.trader.utility import get_file_path
from vnpy.trader.database import (BaseDatabase, BarOverview, DB_TZ, convert_tz)
path = str(get_file_path("database.db"))
db = PeeweeSqliteDatabase(path)
class DbBarData(Model):
""""""
id = AutoField()
symbol: str = CharField()
exchange: str = CharField()
datetime: datetime = DateTimeField()
interval: str = CharField()
volume: float = FloatField()
turnover: float = FloatField()
open_interest: float = FloatField()
""""""
from peewee import SqliteDatabase, Model, CharField, DateTimeField, FloatField
#from .constant import Exchange, Interval
#from .object import BarData, TickData
#from .utility import get_file_path
from vnpy.trader.constant import Exchange, Interval
from vnpy.trader.object import BarData, TickData
from vnpy.trader.utility import get_file_path
DB_NAME = "database.db"
dbname = str(get_file_path(DB_NAME))
DB = SqliteDatabase(str(get_file_path(DB_NAME)))
class DbBarData(Model):
"""
Candlestick bar data for database storage.
Index is defined unique with vt_symbol, interval and datetime.
"""
symbol = CharField()
exchange = CharField()
datetime = DateTimeField()
interval = CharField()
volume = FloatField()
open_price = FloatField()
high_price = FloatField()
