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 onBars(self, broker_, bars):
volumeLeft = {}
for instrument in bars.getInstruments():
bar = bars[instrument]
# Reset the volume available for each instrument.
if bar.getFrequency() == skywalker.bar.Frequency.TRADE:
volumeLeft[instrument] = bar.getVolume()
elif self.__volumeLimit is not None:
# We can't round here because there is no order to request the instrument traits.
volumeLeft[instrument] = bar.getVolume() * self.__volumeLimit
# Reset the volume used for each instrument.
self.__volumeUsed[instrument] = 0.0
self.__volumeLeft = volumeLeft
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()