def addBar(self, instrument, bar, frequency):
instrument = normalize_instrument(instrument)
instrumentId = self.__getOrCreateInstrument(instrument)
timeStamp = dt.datetime_to_timestamp(bar.getDateTime())
try:
sql = "insert into bar (instrument_id, frequency, timestamp, open, high, low, close, volume, adj_close) values (?, ?, ?, ?, ?, ?, ?, ?, ?)"
params = [
instrumentId, frequency, timeStamp,
bar.getOpen(),
bar.getHigh(),
bar.getLow(),
bar.getClose(),
bar.getVolume(),
bar.getAdjClose()
]
self.__connection.execute(sql, params)
except sqlite3.IntegrityError:
sql = "update bar set open = ?, high = ?, low = ?, close = ?, volume = ?, adj_close = ?" \
" where instrument_id = ? and frequency = ? and timestamp = ?"
params = [
bar.getOpen(),
bar.getHigh(),
bar.getLow(),
bar.getClose(),
bar.getVolume(),
bar.getAdjClose(), instrumentId, frequency, timeStamp
]
self.__connection.execute(sql, params)
def get_stop_price_trigger(action, stopPrice, useAdjustedValues, bar):
ret = None
open_ = bar.getOpen(useAdjustedValues)
high = bar.getHigh(useAdjustedValues)
low = bar.getLow(useAdjustedValues)
# If the bar is above the stop price, use the open price.
# If the bar includes the stop price, use the open price or the stop price. Whichever is better.
if action in [broker.Order.Action.BUY, broker.Order.Action.BUY_TO_COVER]:
if low > stopPrice:
ret = open_
elif stopPrice <= high:
if open_ > stopPrice: # The stop price was penetrated on open.
ret = open_
else:
ret = stopPrice
# If the bar is below the stop price, use the open price.
# If the bar includes the stop price, use the open price or the stop price. Whichever is better.
elif action in [broker.Order.Action.SELL, broker.Order.Action.SELL_SHORT]:
if high < stopPrice:
ret = open_
elif stopPrice >= low:
if open_ < stopPrice: # The stop price was penetrated on open.
ret = open_
else:
ret = stopPrice
else: # Unknown action
assert (False)
return ret
def addBar(self, instrument, bar, frequency):
"""将bar中数据插入数据库"""
instrument = normalize_instrument(instrument)
instrumentId = self.__getOrCreateInstrument(instrument)
timeStamp = int(dt.datetime_to_timestamp(bar.getDateTime()))
try:
# extra为空时
if not bar.getExtraColumns():
sql = "insert into bar (instrument_id, frequency, timestamp, open, high, low, close, volume, adj_close) values (%s, %s, %s, %s, %s, %s, %s,%s, %s)"
params = [
instrumentId, frequency, timeStamp,
bar.getOpen(),
bar.getHigh(),
bar.getLow(),
bar.getClose(),
bar.getVolume(),
bar.getAdjClose()
]
self.__cursor.execute(sql, params)
self.__connection.commit()
else:
# extra非空时，将extra中字段处理为逗号分隔的连续字符串形式，用于执行sql语句
extraColumns = bar.getExtraColumns()
keys = extraColumns.keys()
delimiter = ','
keysString = delimiter.join(keys)
for x in range(0, len(keys)):
if keys[x] not in self.getBarFields():
# 字段不存在则先添加字段再插入数据
sqlAlter = '''ALTER TABLE bar ADD COLUMN {field} numeric '''
self.__cursor.execute(sqlAlter.format(field=keys[x]))
self.__connection.commit()
sql1 = "insert into bar (instrument_id, frequency, timestamp, open, high, low, close, volume, adj_close," + keysString + ")"
# %s"*len(keys)表示需要额外传入的参数个数
sql2 = " values (%s, %s, %s, %s, %s, %s, %s, %s, %s" + " ,%s" * len(
keys) + ")"
sql = sql1 + sql2
params1 = [
instrumentId, frequency, timeStamp,
bar.getOpen(),
bar.getHigh(),
bar.getLow(),
bar.getClose(),
bar.getVolume(),
bar.getAdjClose()
]
params2 = extraColumns.values()
params = params1 + params2
self.__cursor.execute(sql, params)
self.__connection.commit()
except psycopg2.IntegrityError:
self.__connection.rollback()
keys = bar.getExtraColumns().keys()
sql = "update bar set open = %s, high = %s, low = %s, close = %s, volume = %s, adj_close = %s{extra}" \
" where instrument_id = %s and frequency = %s and timestamp = %s"
extraUpdate = ''
for i in range(0, len(keys)):
extraUpdate = extraUpdate + ', ' + keys[i] + ' = %s'
params1 = [
bar.getOpen(),
bar.getHigh(),
bar.getLow(),
bar.getClose(),
bar.getVolume(),
bar.getAdjClose()
]
params2 = [instrumentId, frequency, timeStamp]
extraParams = []
for i in range(0, len(keys)):
extraParams.append(bar.getExtraColumns()[keys[i]])
params = params1 + extraParams + params2
self.__cursor.execute(sql.format(extra=extraUpdate), params)
self.__connection.commit()