def save(self):
cursor = Providers.db().get_cursor()
query = "INSERT INTO settings (user_id, name, is_default, created_at, updated_at, instrument_id, " \
"candles_durations, analyzer_working_interval_sec, analyzer_collect_interval_sec, " \
"analyzer_bid_times, analyzer_deep, analyzer_min_deep, analyzer_patterns_control, " \
"analyzer_candles_parent_relation, analyzer_capacity_granularity, analyzer_capacity_type, " \
"signaler_min_chance, signaler_min_repeats, signaler_delay_on_trend,signaler_put_max_change_cost, " \
"signaler_call_max_change_cost,analyzer_expiry_time_bid_divider, signaler_min_ticks_count, " \
"signaler_trend_chance) " \
"VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) RETURNING id"
cursor.execute(query,
(self.user_id, self.name, self.is_default, self.created_at, self.updated_at, self.instrument_id,
self.candles_durations, self.analyzer_working_interval_sec, self.analyzer_collect_interval_sec,
self.analyzer_bid_times, self.analyzer_deep, self.analyzer_min_deep,
self.analyzer_patterns_control,
self.analyzer_candles_parent_relation, self.analyzer_capacity_granularity,
self.analyzer_capacity_type,
self.signaler_min_chance, self.signaler_min_repeats,
self.signaler_delay_on_trend, self.signaler_put_max_change_cost,
self.signaler_call_max_change_cost,
self.analyzer_expiry_time_bid_divider, self.signaler_min_ticks_count,
self.signaler_trend_chance))
Providers.db().commit()
row = cursor.fetchone()
if row:
self.id = row[0]
return self
def save_many(candles: list):
cursor = Providers.db().get_cursor()
query = 'INSERT INTO candles (instrument_id, from_ts, till_ts, duration, high, low, "open", "close", range, ' \
'change, average, average_power, range_power, change_power, high_power, low_power) VALUES ' + \
','.join(v.__tuple_str() for v in candles) + ' ON CONFLICT (instrument_id, from_ts, till_ts) DO NOTHING'
cursor.execute(query)
Providers.db().commit()
def update_status(self, key=None, value=None):
if key is not None and value is not None:
self.status[key] = value
cursor = Providers.db().get_cursor()
query = "UPDATE tasks SET status=%s WHERE id=%s"
cursor.execute(query, [json.dumps(self.status), self.id])
Providers.db().commit()
def save(self):
cursor = Providers.db().get_cursor()
row = cursor.execute(
"INSERT INTO predictions (setting_id, task_id, time_bid, pattern_id, "
"created_cost, created_ask, created_bid, expiration_cost, expiration_ask, "
"expiration_bid, last_cost, range_max_change_cost, "
"range_max_avg_change_cost,call_max_change_cost,put_max_change_cost,"
"call_max_avg_change_cost, put_max_avg_change_cost, range_sum_max_change_cost,"
"call_sum_max_change_cost, put_sum_max_change_cost, count_change_cost,created_at, "
"expiration_at, history_num, time_to_expiration) "
"VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) "
"ON CONFLICT "
"(setting_id,time_bid,pattern_id,expiration_at,time_to_expiration,history_num)"
"DO UPDATE SET expiration_cost=EXCLUDED.expiration_cost RETURNING id",
(self.setting_id, self.task_id, self.time_bid, self.pattern_id,
self.created_cost, self.created_ask, self.created_bid,
self.expiration_cost, self.expiration_ask, self.expiration_bid,
self.last_cost, self.range_max_change_cost,
self.range_max_avg_change_cost, self.call_max_change_cost,
self.put_max_change_cost, self.call_max_avg_change_cost,
self.put_max_avg_change_cost, self.range_sum_max_change_cost,
self.call_sum_max_change_cost, self.put_sum_max_change_cost,
self.count_change_cost, self.created_at, self.expiration_at,
self.history_num, self.time_to_expiration))
Providers.db().commit()
if row:
self.id = row.id
return self
def save(self):
cursor = Providers.db().get_cursor()
cursor.execute(
"INSERT INTO patterns (setting_id,task_id,time_bid,sequence,sequence_duration,"
"used_count,calls_count,"
"puts_count,same_count,trend,range_max_change_cost, "
"range_max_avg_change_cost,call_max_change_cost,put_max_change_cost,"
"call_max_avg_change_cost, put_max_avg_change_cost, range_sum_max_change_cost,"
"call_sum_max_change_cost, put_sum_max_change_cost, count_change_cost,"
"delay,expires,history_num,created_at,trend_max_call_count,trend_max_put_count) "
"VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) "
"ON CONFLICT (sequence,setting_id,time_bid,expires,history_num)"
"DO UPDATE SET used_count=patterns.used_count + 1 RETURNING id",
(self.setting_id, self.task_id, self.time_bid, self.sequence,
self.sequence_duration, self.used_count, self.calls_count,
self.puts_count, self.same_count, self.trend,
self.range_max_change_cost, self.range_max_avg_change_cost,
self.call_max_change_cost, self.put_max_change_cost,
self.call_max_avg_change_cost, self.put_max_avg_change_cost,
self.range_sum_max_change_cost, self.call_sum_max_change_cost,
self.put_sum_max_change_cost, self.count_change_cost, self.delay,
self.expires, self.history_num, self.created_at,
self.trend_max_call_count, self.trend_max_put_count))
Providers.db().commit()
row = cursor.fetchone()
if row:
self.id = row.id
Providers.db().commit()
return self
def save_many(instruments: list):
cursor = Providers.db().get_cursor()
query = "INSERT INTO instruments (instrument, pip, name, not_working_time) VALUES " + \
",".join(v.__tuple_str() for v in instruments) + \
" ON CONFLICT (instrument) DO NOTHING"
cursor.execute(query)
Providers.db().commit()
def terminate(self):
cursor = Providers.db().get_cursor()
query = "UPDATE tasks SET terminated_at=%s WHERE id=%s RETURNING id;"
cursor.execute(query, (time.time(), self.id))
Providers.db().commit()
row = cursor.fetchone()
if row:
return self
def launch(self, thread):
cursor = Providers.db().get_cursor()
query = "UPDATE tasks SET launched_at=%s, thread=%s WHERE id=%s RETURNING id;"
cursor.execute(query, (time.time(), thread, self.id))
Providers.db().commit()
row = cursor.fetchone()
if row:
return self
def append_exception(self, json_exception):
self.handled_exceptions.append(json_exception)
cursor = Providers.db().get_cursor()
query = "UPDATE tasks SET handled_exceptions=%s WHERE id=%s RETURNING id;"
cursor.execute(query, (json.dumps(self.handled_exceptions), self.id))
Providers.db().commit()
row = cursor.fetchone()
if row:
return self
def save(self):
cursor = Providers.db().get_cursor()
query = "INSERT INTO instruments (instrument, pip, name, not_working_time) VALUES " + \
"(%s,%s,%s,%s) ON CONFLICT (instrument) DO NOTHING RETURNING id;"
cursor.execute(query, (self.instrument, self.pip, self.name, json.dumps(self.not_working_time)))
Providers.db().commit()
row = cursor.fetchone()
if row:
self.id = row[0]
return self
def save(self):
cursor = Providers.db().get_cursor()
row = cursor.execute(
"INSERT INTO quotations (ts, instrument_id, ask, bid, value) "
"VALUES (%s,%s,%s,%s,%s) ON CONFLICT (ts,instrument_id) DO NOTHING RETURNING ts",
(self.ts, self.instrument_id, self.ask, self.bid, self.value))
Providers.db().commit()
if row:
return self
def save_many(quotations: list):
cursor = Providers.db().get_cursor()
query = 'INSERT INTO quotations (ts, instrument_id, ask, bid, "value") VALUES ' + \
','.join(v.__tuple_str() for v in quotations) + \
' ON CONFLICT (ts,instrument_id) DO NOTHING RETURNING ts as ts'
cursor.execute(query)
Providers.db().commit()
res = cursor.fetchall()
if res:
return res
return []
def terminate(self):
cursor = Providers.db().get_cursor()
query = "UPDATE workers SET terminated_at=%s, terminated_code=%s, terminated_traceback=%s, " \
"terminated_description=%s WHERE id=%s RETURNING id;"
cursor.execute(
query,
(time.time(), self.terminated_code, self.terminated_traceback,
self.terminated_description, self.id))
Providers.db().commit()
row = cursor.fetchone()
if row:
return self
def save(self):
cursor = Providers.db().get_cursor()
query = "INSERT INTO workers (host_name, pid, launched_at, terminated_at, " \
"terminated_code, terminated_traceback, terminated_description) VALUES " + \
"(%s,%s,%s,%s,%s,%s,%s) RETURNING id;"
cursor.execute(
query, (self.host_name, self.pid, self.launched_at,
self.terminated_at, self.terminated_code,
self.terminated_traceback, self.terminated_description))
Providers.db().commit()
row = cursor.fetchone()
if row:
self.id = row[0]
return self
def save(self):
cursor = Providers.db().get_cursor()
row = cursor.execute(
'INSERT INTO candles (instrument_id, from_ts, till_ts, duration, high, low, "open", '
'"close", range, change, average, average_power, range_power, change_power, high_power, '
'low_power) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) '
'ON CONFLICT (ts,instrument_id) DO NOTHING RETURNING ts',
(self.instrument_id, self.from_ts, self.till_ts, self.duration,
self.high, self.low, self.open, self.close, self.range,
self.change, self.average, self.average_power, self.range_power,
self.change_power, self.high_power, self.low_power))
Providers.db().commit()
if row:
return self
def make(from_ts, duration, instrument_id):
candle_raw = Quotation.prepare_candle(from_ts, duration, instrument_id)
candle = Candle(candle_raw)
candle.instrument_id = instrument_id
candle.from_ts = from_ts - duration
candle.till_ts = from_ts
candle.duration = duration
candle.range = candle.high - candle.low
candle.change = candle.open - candle.close
cursor = Providers.db().get_cursor()
# Свеча для сравнения за прошлую длительность времени, а не просто прошлая свеча
cursor.execute(
"SELECT * FROM candles WHERE instrument_id=%s AND duration=%s AND till_ts<=%s "
"ORDER BY till_ts DESC LIMIT 1",
(instrument_id, duration, from_ts - duration))
last_candle = Candle()
last_candle_raw = cursor.fetchone()
if last_candle_raw:
last_candle = Candle.model(last_candle_raw)
if last_candle.change:
candle.change_power = candle.change / (last_candle.change / 100)
return candle
def get_one_to_ts(ts, instrument_id):
cursor = Providers.db().get_cursor()
cursor.execute(
"SELECT * FROM quotations WHERE ts<=%s AND instrument_id=%s ORDER BY ts LIMIT 1",
(ts, instrument_id))
row = cursor.fetchone()
if row:
return Quotation.model(row)
def get_instruments_dict(self):
cursor = Providers.db().get_cursor()
result_dict = {}
for active_item in self.config.get_broker_instruments():
cursor.execute("SELECT * FROM instruments WHERE instrument=%s", [active_item])
row = cursor.fetchone()
if row:
result_dict[active_item] = row.id
return result_dict
def save(self):
cursor = Providers.db().get_cursor()
query = "INSERT INTO tasks (user_id, setting_id, worker_id, is_enabled, service_name, params, status, thread, " \
"start_at, launched_at, stop_at, terminated_at, handled_exceptions) " \
"VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) RETURNING id;"
cursor.execute(query, [
self.user_id, self.setting_id, self.worker_id, self.is_enabled,
self.service_name,
json.dumps(self.params),
json.dumps(
self.status), self.thread, self.start_at, self.launched_at,
self.stop_at, self.terminated_at, self.handled_exceptions
])
Providers.db().commit()
row = cursor.fetchone()
if row:
self.id = row[0]
return self
def save_many(predictions: list):
cursor = Providers.db().get_cursor()
query = 'INSERT INTO predictions (setting_id, task_id, time_bid, pattern_id, ' + \
'created_cost, created_ask, created_bid, expiration_cost, expiration_ask, expiration_bid, ' \
'last_cost, range_max_change_cost, ' + \
'range_max_avg_change_cost,call_max_change_cost,put_max_change_cost,' + \
'call_max_avg_change_cost, put_max_avg_change_cost, range_sum_max_change_cost,' + \
'call_sum_max_change_cost, put_sum_max_change_cost, count_change_cost,created_at, ' + \
'expiration_at, history_num, time_to_expiration) VALUES ' + \
','.join(v.__tuple_str() for v in predictions) + 'ON CONFLICT ' \
'(setting_id,time_bid,pattern_id,expiration_at,time_to_expiration,history_num)' + \
'DO UPDATE SET expiration_cost=EXCLUDED.expiration_cost RETURNING id'
cursor.execute(query)
Providers.db().commit()
res = cursor.fetchall()
if res:
return res
return []
def save(self):
cursor = Providers.db().get_cursor()
row = cursor.execute(
"INSERT INTO signals (instrument_id,setting_id,task_id,"
"pattern_id,created_at,expiration_at,direction,created_cost,expiration_cost,"
"closed_cost,max_cost,min_cost,call_max_change_cost,put_max_change_cost,time_bid,"
"history_num, closed_discrepancy_cost, closed_change_cost, is_read) "
"VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) RETURNING id",
(self.instrument_id, self.setting_id, self.task_id,
self.pattern_id, self.created_at, self.expiration_at,
self.direction, self.created_cost, self.expiration_cost,
self.closed_cost, self.max_cost, self.min_cost,
self.call_max_change_cost, self.put_max_change_cost,
self.time_bid, self.history_num, self.closed_discrepancy_cost,
self.closed_change_cost, self.is_read))
Providers.db().commit()
if row:
self.id = row.id
return self
def update_close_cost(task, quotation):
cursor = Providers.db().get_cursor()
cursor.execute(
"UPDATE signals SET closed_cost=%s, closed_discrepancy_cost=%s-expiration_cost, "
"closed_change_cost=%s-created_cost WHERE expiration_at<=%s "
"AND closed_cost=0 AND task_id=%s RETURNING *",
(quotation.value, quotation.value, quotation.value, quotation.ts,
task.id))
Providers.db().commit()
signals_updated = cursor.fetchall()
if task.get_param("history_num") == 0:
for signal in signals_updated:
Providers.telebot().send_signal(
task.setting.instrument.instrument + ": " +
"Закрыт сигнал: " +
("put" if signal.direction == -1 else "call") + ". Цена " +
str(round(signal.created_cost, 5)) + " изменилась на " +
str(round(signal.closed_change_cost, 5)) +
" с прогнозом " + str(round(signal.expiration_cost, 5)) +
" и стала " + str(round(signal.closed_cost, 5)))
def get_from_interval(start_ts, end_ts, instrument_id):
cursor = Providers.db().get_cursor()
cursor.execute(
"SELECT * FROM quotations WHERE ts>=%s AND ts<=%s AND instrument_id=%s ORDER BY ts;",
(start_ts, end_ts, instrument_id))
rows = cursor.fetchall()
res = []
if rows:
for row in rows:
res.append(Quotation.model(row))
return res
def get_last_on_sequence(sequence, time_bid, task):
cursor = Providers.db().get_cursor()
cursor.execute(
"SELECT * FROM patterns WHERE sequence=%s AND setting_id=%s AND time_bid=%s AND history_num=%s "
"ORDER BY created_at DESC LIMIT 1",
(sequence, task.setting.id, time_bid,
task.get_param("history_num", 0)))
model = cursor.fetchone()
if model:
return Pattern.model(model)
def get_pending(worker_id):
tasks = []
cursor = Providers.db().get_cursor()
cursor.execute(
"SELECT * FROM tasks WHERE worker_id=%s AND is_enabled=%s AND start_at<=%s AND launched_at=%s",
[worker_id, True, time.time(), 0])
rows = cursor.fetchall()
if rows:
for row in rows:
tasks.append(Task(row))
return tasks
def save_many(patterns: list):
incr = 1
if Providers.config().no_write:
incr = 0
cursor = Providers.db().get_cursor()
query = 'INSERT INTO patterns (setting_id,task_id,time_bid,sequence,sequence_duration,' \
'used_count,calls_count,' + \
'puts_count,same_count,trend,range_max_change_cost,' + \
'range_max_avg_change_cost,call_max_change_cost,put_max_change_cost,' + \
'call_max_avg_change_cost, put_max_avg_change_cost,range_sum_max_change_cost,' + \
'call_sum_max_change_cost, put_sum_max_change_cost,count_change_cost,' + \
'delay,expires,history_num,created_at,trend_max_call_count,trend_max_put_count) VALUES ' + \
','.join(v.__tuple_str() for v in patterns) + \
'ON CONFLICT (sequence,setting_id,time_bid,expires,history_num)' + \
'DO UPDATE SET used_count=patterns.used_count + ' + str(incr) + ' RETURNING *'
cursor.execute(query)
Providers.db().commit()
res = cursor.fetchall()
if res:
return res
return []
def update(self):
cursor = Providers.db().get_cursor()
cursor.execute(
"UPDATE patterns SET setting_id=%s,task_id=%s,time_bid=%s,sequence=%s,"
"sequence_duration=%s,used_count=%s,"
"calls_count=%s,puts_count=%s,same_count=%s,trend=%s,range_max_change_cost=%s, "
"range_max_avg_change_cost=%s,call_max_change_cost=%s,put_max_change_cost=%s,"
"call_max_avg_change_cost=%s, put_max_avg_change_cost=%s, range_sum_max_change_cost=%s,"
"call_sum_max_change_cost=%s, put_sum_max_change_cost=%s, count_change_cost=%s,"
"delay=%s,expires=%s,history_num=%s,"
"created_at=%s, trend_max_call_count=%s, trend_max_put_count=%s WHERE id=%s",
(self.setting_id, self.task_id, self.time_bid, self.sequence,
self.sequence_duration, self.used_count, self.calls_count,
self.puts_count, self.same_count, self.trend,
self.range_max_change_cost, self.range_max_avg_change_cost,
self.call_max_change_cost, self.put_max_change_cost,
self.call_max_avg_change_cost, self.put_max_avg_change_cost,
self.range_sum_max_change_cost, self.call_sum_max_change_cost,
self.put_sum_max_change_cost, self.count_change_cost, self.delay,
self.expires, self.history_num, self.created_at,
self.trend_max_call_count, self.trend_max_put_count, self.id))
Providers.db().commit()
def get_last_with_nesting(till_ts,
deep,
instrument_id,
durations,
relation="parent"):
cursor = Providers.db().get_cursor()
query = "SELECT change,change_power,duration,till_ts,from_ts FROM " \
"get_last_candles_with_nesting({0},{1},{2},'{3}',{4}) " \
"ORDER BY till_ts DESC".format(instrument_id, till_ts, deep, relation, "ARRAY" + str(durations))
cursor.execute(query)
rows = cursor.fetchall()
if rows:
return Candle.get_candles_with_parents(till_ts, rows, deep,
relation)
def prepare_candle(from_ts, duration, instrument_id):
cursor = Providers.db().get_cursor()
cursor.execute(
"SELECT "
"MAX(value) OVER w AS high, "
"MIN(value) OVER w AS low, "
"first_value(value) OVER w AS open, "
"last_value(value) OVER w AS close, "
"AVG(value) OVER w AS average "
"FROM quotations WHERE ts>=%s AND instrument_id=%s AND ts<=%s "
"WINDOW w AS () "
"ORDER BY ts DESC LIMIT 1",
[from_ts - duration, instrument_id, from_ts])
candle_raw = cursor.fetchone()
if candle_raw:
return candle_raw
def get_success_percent(task):
cursor = Providers.db().get_cursor()
cursor.execute(
"SELECT * FROM signals "
"WHERE history_num=%s AND closed_cost > 0 AND setting_id=%s AND task_id=%s",
[task.get_param("history_num", 0), task.setting_id, task.id])
signals = cursor.fetchall()
success = []
for signal in signals:
if signal.direction == -1 and signal.closed_change_cost < 0:
success.append(signal)
if signal.direction == 1 and signal.closed_change_cost > 0:
success.append(signal)
chance = 0
if len(signals) > 0:
chance = round(len(success) / len(signals) * 100, 4)
return chance