def load_config(self, mysql_uri, mysql_db, config_file):
self.mysql_uri = mysql_uri
self.config_file = config_file
with open(self.config_file, 'r') as f:
content = json.loads(f.read())
self.mail_config = content['mail']
self.mysql_db = MysqlDB(mysql_db, mysql_db)
self.db_mgr = MysqlDatabaseManager(self.mysql_uri, self.mysql_db)
stmt = '''
CREATE TABLE IF NOT EXISTS {}.{}
( id int not null AUTO_INCREMENT, exch1 varchar(25), exch2 varchar(25),
currency varchar(25), price_diff_threshold decimal(20, 8), direction varchar(25),
succeeded_rule_id varchar(128),
status varchar(16),
primary key (id)
);
'''.format(self.mysql_db.alias, self.rule_table_name)
if not self.db_mgr.is_table_existed(self.mysql_db,
self.rule_table_name):
try:
self.db_mgr.session.execute(stmt)
self.db_mgr.session.commit()
except Exception as e:
print(e)
self.db_mgr.session.rollback()
self.fetch_rules()
def __init__(self, mysql_uri, source_db, target_db, since_date):
self.db_mgr = MysqlDatabaseManager(mysql_uri, source_db, target_db)
self.source_db = source_db
self.target_db = target_db
if not since_date:
self.since_date = (datetime.utcnow().date() - timedelta(2)).strftime('%Y%m%d')
else:
self.since_date = since_date
def __init__(self,
db_uri,
from_db,
to_db,
zone_info,
since_date=None,
end_date=None):
super(MysqlMergeUtil, self).__init__(db_uri, from_db, to_db, zone_info,
since_date, end_date)
self.db_mgr = MysqlDatabaseManager(db_uri, from_db, to_db)
def __init__(self, db_uri, exchangerate_db, source_db, target_db, coin,
first, second, date):
super(PriceDiffUtil, self).__init__()
self.exchangerate_db = exchangerate_db
self.source_db = source_db
self.target_db = target_db
self.db_mgr = MysqlDatabaseManager(db_uri, exchangerate_db, source_db,
target_db)
self.coin = coin
self.first_instmt = first.lower()
self.second_instmt = second.lower()
self.date = date
self.exchange_rate = self.get_exchange_rate_of_day()
self.price_diff_table = None
self.create_price_diff_table()
class PriceDiffUtil(object):
def __init__(self, db_uri, exchangerate_db, source_db, target_db, coin,
first, second, date):
super(PriceDiffUtil, self).__init__()
self.exchangerate_db = exchangerate_db
self.source_db = source_db
self.target_db = target_db
self.db_mgr = MysqlDatabaseManager(db_uri, exchangerate_db, source_db,
target_db)
self.coin = coin
self.first_instmt = first.lower()
self.second_instmt = second.lower()
self.date = date
self.exchange_rate = self.get_exchange_rate_of_day()
self.price_diff_table = None
self.create_price_diff_table()
def get_exchange_rate_of_day(self):
exchange_rate_table = 'exchange_rate'
if not self.db_mgr.is_table_existed(self.exchangerate_db,
exchange_rate_table):
raise ValueError('No exchange rate table found')
stmt = '''select * from {}.{} where date_time = '{}';
'''.format(self.exchangerate_db.alias, exchange_rate_table, self.date)
res = self.db_mgr.session.execute(stmt)
rates = [rate for rate in res]
if not rates:
raise ValueError('No record exchange rate for day {}'.format(
self.date))
exchange_rate = ExchangeRate()
exchange_rate.cny = rates[0][1]
exchange_rate.jpy = rates[0][2]
exchange_rate.eur = rates[0][3]
exchange_rate.krw = rates[0][4]
return exchange_rate
def create_price_diff_table(self):
self.price_diff_table = 'exch_{}_{}_{}_pricediff_{}'.format(
self.coin.lower(), self.first_instmt.lower(),
self.second_instmt.lower(), self.date)
stmt = '''create table if not exists {}.{}
(timestamp varchar(25) primary key, price_diff decimal(20, 8), price_diff_percent decimal(20, 8));'''
if not self.db_mgr.is_table_existed(self.target_db,
self.price_diff_table):
self.db_mgr.session.execute(
stmt.format(self.target_db.alias, self.price_diff_table))
def get_price_by_tick(self, instmt, coin, date, start_time, tick):
table_name = None
tb_names = self.db_mgr.get_table_names_from_db(self.source_db)
for tb in tb_names:
m = re.search(r'exch_(.*)_(.*)_snapshot_(\d+)', tb)
if m and instmt == m.group(1) \
and coin in m.group(2) and m.group(3) == date:
table_name = tb
break
else:
raise ValueError('No instmt and coin pair table found')
s_time = start_time.strftime('%Y%m%d %H:%M:%S.%f')
d_time = (start_time +
timedelta(0, tick)).strftime('%Y%m%d %H:%M:%S.%f')
stmt = '''select trade_px from {} where trades_date_time >= '{}'
and trades_date_time < '{}' and update_type=2
'''.format(table_name, s_time, d_time)
res = self.db_mgr.session.execute(stmt)
prices = [px for px in res]
if prices:
return float(prices[-1][0])
else:
return 0
def consistent_currency_by_usd(self, instmt, coin, price, exchange_rate):
er = exchange_rate
base_currency = instmt.get_base_currency()
coin_currency = re.split('_|-', coin)
if len(coin_currency) > 1:
nation = coin_currency[1]
return price / float(getattr(er, nation.lower()))
else:
for item in er.supported_currency:
if item.lower() in coin_currency:
return price / float(getattr(exchange_rate, item.lower()))
else:
return price / float(getattr(er, base_currency))
def get_exact_coin_name(self, instmt, coin, date):
tb_names = self.db_mgr.get_table_names_from_db(self.source_db)
for tb in tb_names:
m = re.search(r'exch_(.*)_(.*)_snapshot_(\d+)', tb)
if m and instmt == m.group(1) \
and coin in m.group(2) and m.group(3) == date:
return m.group(2)
else:
raise ValueError('No instmt and coin pair table found')
def generate_price_diff(self):
exact_date = datetime.strptime(self.date, '%Y%m%d').date()
begin_time = datetime.strptime(self.date, '%Y%m%d')
exch_factory = ExchangeFactory()
exch1 = exch_factory.get_exchange(exchange=self.first_instmt)
exch2 = exch_factory.get_exchange(exchange=self.second_instmt)
while True:
if begin_time.date() > exact_date:
break
end_time = begin_time + timedelta(0, 60)
coin1 = self.get_exact_coin_name(self.first_instmt, self.coin,
self.date)
coin2 = self.get_exact_coin_name(self.second_instmt, self.coin,
self.date)
price1 = self.get_price_by_tick(self.first_instmt, self.coin,
self.date, begin_time, 60)
price2 = self.get_price_by_tick(self.second_instmt, self.coin,
self.date, begin_time, 60)
if price1 == 0 or price2 == 0:
begin_time = end_time
continue
consistent_currency1 = self.consistent_currency_by_usd(
exch1, coin1, price1, self.exchange_rate)
consistent_currency2 = self.consistent_currency_by_usd(
exch2, coin2, price2, self.exchange_rate)
price_diff = consistent_currency1 - consistent_currency2
price_diff_percent = price_diff / min(consistent_currency1,
consistent_currency2) * 100.0
stmt = '''replace into {}.{} (timestamp, price_diff, price_diff_percent) values('{}',{},{})
'''.format(self.target_db.alias, self.price_diff_table,
begin_time.strftime('%Y%m%d %H:%M:%S.%f'), price_diff,
price_diff_percent)
try:
print(stmt)
self.db_mgr.session.execute(stmt)
self.db_mgr.session.commit()
except Exception as e:
print(e)
self.db_mgr.session.rollback()
begin_time = end_time
class KBarGenerator(object):
def __init__(self, mysql_uri, source_db, target_db, since_date):
self.db_mgr = MysqlDatabaseManager(mysql_uri, source_db, target_db)
self.source_db = source_db
self.target_db = target_db
if not since_date:
self.since_date = (datetime.utcnow().date() - timedelta(2)).strftime('%Y%m%d')
else:
self.since_date = since_date
def create_kbar_data_table_if_not_exists(self, db, instmt, coin, durance, timestamp=None):
kb_durance = KBarDurance(durance)
if kb_durance.is_need_create_new_table:
table_name = '{}.exch_{}_{}_{}_kbar_{}'.format(
db.alias, instmt, coin, durance, timestamp)
else:
table_name = '{}.exch_{}_{}_{}_kbar'.format(
db.alias, instmt, coin, durance)
stmt = '''CREATE TABLE IF NOT EXISTS {}
(time_start varchar(25),
time_end varchar(25),
exchange varchar(25),
market varchar(25),
open decimal(20, 8),
high decimal(20, 8),
low decimal(20, 8),
close decimal(20, 8),
volume decimal(20, 8),
primary key (time_start , time_end)
);
'''
try:
if not self.db_mgr.is_table_existed(db, table_name.split('.')[1]):
print('Creating {} {} KBar with durance {}.'.format(instmt, coin, durance))
self.db_mgr.session.execute(stmt.format(table_name))
self.db_mgr.session.commit()
return table_name
except Exception as e:
print(e)
self.db_mgr.session.rollback()
def create_last_kbar_data_table_if_not_exists(self, db):
table_name = '{}.last_updated_kbar'.format(db.alias)
stmt = '''CREATE TABLE IF NOT EXISTS {}
(
exchange varchar(25),
market varchar(25),
durance varchar(25),
open decimal(20, 8),
high decimal(20, 8),
low decimal(20, 8),
close decimal(20, 8),
volume decimal(20, 8),
primary key (exchange , market, durance)
);
'''
if not self.db_mgr.is_table_existed(db, 'last_updated_kbar'):
print('Creating Last Updated KBar Table')
self.db_mgr.session.execute(stmt.format(table_name))
self.db_mgr.session.commit()
def get_last_kbar_item(self, db, instmt, coin, durance):
table_name = '{}.last_updated_kbar'.format(db.alias)
stmt = '''select * from {} where exchange='{}'
and market='{}' and durance='{}';
'''
res = self.db_mgr.session.execute(stmt.format(table_name, instmt,
coin, durance))
if res:
kbar = res.fetchone()
return kbar
else:
return None
def update_last_kbar_item(self, db, instmt, coin, durance,
open, high , low , close, volume):
table_name = '{}.last_updated_kbar'.format(db.alias)
stmt = '''replace into {} (exchange , market, durance , open, high , low , close, volume)
values ('{}', '{}', '{}', {}, {}, {}, {}, {})
'''.format(table_name, instmt, coin, durance, open, high, low, close, volume )
try:
self.db_mgr.session.execute(stmt)
self.db_mgr.session.commit()
except Exception as e:
self.db_mgr.session.fallback()
def kbar_generate_worker(self, instmt, coin, durance, timestamp):
kbar_durance = KBarDurance(durance)
data_table = '{}.{}_{}_order_{}'.format(self.source_db.alias, instmt, coin, timestamp)
if kbar_durance.is_need_create_new_table:
kbar_table = '{}.exch_{}_{}_{}_kbar_{}'.format(self.target_db.alias, instmt, coin, durance, timestamp)
else:
kbar_table = '{}.exch_{}_{}_{}_kbar'.format(self.target_db.alias, instmt, coin, durance)
current_date = datetime.strptime(timestamp, '%Y_%m_%d').date()
begin_time = datetime.strptime(timestamp, '%Y_%m_%d')
last_kbar_item = self.get_last_kbar_item(self.target_db, instmt, coin, durance)
last_trade = Trade(instmt, coin, last_kbar_item[3], last_kbar_item[4],
last_kbar_item[5], last_kbar_item[6],
last_kbar_item[7]) if last_kbar_item else Trade(instmt, coin)
while True:
if begin_time.date() > current_date:
self.update_last_kbar_item(self.target_db, instmt, coin, durance,
last_trade.open, last_trade.high,
last_trade.low, last_trade.close,
last_trade.volume)
break
end_time = begin_time + kbar_durance.interval
stmt = '''
select t, tq, date_time from {}
where update_type=2 and date_time >= '{}' and date_time < '{}'
order by date_time;
'''.format(data_table, begin_time.strftime('%Y%m%d %H:%M:%S.%f'),
end_time.strftime('%Y%m%d %H:%M:%S.%f'))
res = self.db_mgr.session.execute(stmt)
items = [trade for trade in res]
stmt = '''
replace into {} (time_start, time_end, exchange, market, open, high , low, close, volume)
values('{}', '{}', '{}', '{}', {}, {}, {}, {}, {})
'''
try:
if not items:
self.db_mgr.session.execute(stmt.format(kbar_table,
begin_time.strftime('%Y%m%d %H:%M:%S.%f'),
end_time.strftime('%Y%m%d %H:%M:%S.%f'),
instmt, coin,
last_trade.open, last_trade.high, last_trade.low,
last_trade.close, last_trade.volume
))
self.db_mgr.session.commit()
else:
open = items[0][0]
high = reduce(lambda x,y: x if x> y else y , [ item[0] for item in items])
low = reduce(lambda x,y: x if x < y else y , [ item[0] for item in items])
close = items[-1][0]
volume = sum([item[1] for item in items])
#self.update_last_kbar_item(db, instmt, coin, durance, open, high, low, close, volume)
last_trade = Trade(instmt, coin, open, high, low, close, volume)
self.db_mgr.session.execute(stmt.format(kbar_table,
begin_time.strftime('%Y%m%d %H:%M:%S.%f'),
end_time.strftime('%Y%m%d %H:%M:%S.%f'),
instmt, coin,
open, high, low, close, volume))
self.db_mgr.session.commit()
except Exception as e:
print(e)
self.db_mgr.session.rollback()
begin_time = end_time
def kbar_generate(self):
tb_names = self.db_mgr.get_all_table_names()
instmt_coin_table = {}
self.create_last_kbar_data_table_if_not_exists(self.target_db)
for tn in tb_names:
r = re.search(r'bcex.(\w+)_(\w+)_order_(\d{4}_\d{2}_\d{2})', tn)
if r:
if r.group(1) not in instmt_coin_table:
instmt_coin_table[r.group(1)] = {r.group(2): [r.group(3)]}
else:
if r.group(2) not in instmt_coin_table[r.group(1)]:
instmt_coin_table[r.group(1)][r.group(2)] = [r.group(3)]
else:
instmt_coin_table[r.group(1)][r.group(2)].append(r.group(3))
import json
print(json.dumps(instmt_coin_table))
for instmt in instmt_coin_table:
for coin in instmt_coin_table[instmt]:
for timestamp in instmt_coin_table[instmt][coin]:
if timestamp >= self.since_date:
for durance in KBarDurance.kbar_durance_map.keys():
print('Generate KBar data for {} with {} by {} on {}'.format(instmt, coin, durance, timestamp))
self.create_kbar_data_table_if_not_exists(self.target_db, instmt, coin, durance, timestamp)
self.kbar_generate_worker(instmt, coin, durance, timestamp)
class MonitorConfig(with_metaclass(FlyweightMeta)):
def __init__(self):
super(MonitorConfig, self).__init__()
self.config_file = None
self.mysql_uri = None
self.mysql_db = None
self.rules = None
self.mail_config = None
self.rule_table_name = 'exchange_data_monitor_rules'
self.lock = threading.Lock()
def load_config(self, mysql_uri, mysql_db, config_file):
self.mysql_uri = mysql_uri
self.config_file = config_file
with open(self.config_file, 'r') as f:
content = json.loads(f.read())
self.mail_config = content['mail']
self.mysql_db = MysqlDB(mysql_db, mysql_db)
self.db_mgr = MysqlDatabaseManager(self.mysql_uri, self.mysql_db)
stmt = '''
CREATE TABLE IF NOT EXISTS {}.{}
( id int not null AUTO_INCREMENT, exch1 varchar(25), exch2 varchar(25),
currency varchar(25), price_diff_threshold decimal(20, 8), direction varchar(25),
succeeded_rule_id varchar(128),
status varchar(16),
primary key (id)
);
'''.format(self.mysql_db.alias, self.rule_table_name)
if not self.db_mgr.is_table_existed(self.mysql_db,
self.rule_table_name):
try:
self.db_mgr.session.execute(stmt)
self.db_mgr.session.commit()
except Exception as e:
print(e)
self.db_mgr.session.rollback()
self.fetch_rules()
def fetch_rules(self):
stmt = '''
select id, exch1, exch2, currency, price_diff_threshold, direction
from {}.{} where status = 'active';
'''.format(self.mysql_db.alias, self.rule_table_name)
try:
self.lock.acquire()
print('Fetching Rules')
self.db_mgr.session.execute('flush table {}.{}'.format(
self.mysql_db.alias, self.rule_table_name))
self.db_mgr.session.commit()
res = self.db_mgr.session.execute(stmt)
if self.rules:
del self.rules
self.rules = {}
for item in res:
self.rules[item[0]] = list(item[1:])
print(self.rules)
except Exception as e:
print(e)
self.db_mgr.session.rollback()
finally:
self.lock.release()
def deactive_rule(self, id):
stmt = '''
update {}.{} set status = 'deactive' where id = {};
'''.format(self.mysql_db.alias, self.rule_table_name, id)
try:
self.lock.acquire()
self.db_mgr.session.execute(stmt)
self.db_mgr.session.commit()
except Exception as e:
print(e)
finally:
self.lock.release()
def active_rule(self, id):
stmt = '''
update {}.{} set status = 'active' where id = {};
'''.format(self.mysql_db.alias, self.rule_table_name, id)
try:
self.lock.acquire()
self.db_mgr.session.execute(stmt)
self.db_mgr.session.commit()
except Exception as e:
print(e)
finally:
self.lock.release()
def get_succeeded_rule_ids(self, key):
stmt = '''
select succeeded_rule_id from {}.{} where id = {};
'''.format(self.mysql_db.alias, self.rule_table_name, key)
try:
self.lock.acquire()
res = self.db_mgr.session.execute(stmt).fetchone()
if res[0]:
ids = list(
map(lambda x: int(x.strip()), re.split(',|\ |;', res[0])))
return ids
else:
return None
except Exception as e:
print(e)
finally:
self.lock.release()
def safe_exchage_rate(self, dbalias, tablename, cny, jpy, eur, krw):
date = datetime.utcnow().date().strftime("%Y%m%d")
stmt = '''replace into {}.{} (date_time, cny, jpy, eur, krw)
values ('{}', {}, {}, {}, {});
'''.format(dbalias, tablename, date, cny, jpy, eur, krw)
try:
self.lock.acquire()
self.db_mgr.session.execute(stmt)
self.db_mgr.session.commit()
except Exception as e:
print(e)
finally:
self.lock.release()
class MysqlMergeUtil(MergeUtil):
def __init__(self,
db_uri,
from_db,
to_db,
zone_info,
since_date=None,
end_date=None):
super(MysqlMergeUtil, self).__init__(db_uri, from_db, to_db, zone_info,
since_date, end_date)
self.db_mgr = MysqlDatabaseManager(db_uri, from_db, to_db)
def is_table_need_merge(self, instmt, coin, timestamp):
tb_name_post_fix = 'exch_{}_{}_snapshot_{}'.format(
instmt, coin, timestamp)
origin_table_name = '{}.{}'.format(self.origin_db.alias,
tb_name_post_fix)
target_table_name = '{}.{}'.format(self.target_db.alias,
tb_name_post_fix)
stmt = '''select count(trades_date_time) from {}
where {}.trades_date_time != '20000101 00:00:00.000000'
and {}.order_date_time != '20000101 00:00:00.000000'
and {}.trades_date_time not in
(select trades_date_time from {});
'''.format(origin_table_name, origin_table_name, origin_table_name,
origin_table_name, target_table_name)
res = self.db_mgr.session.execute(stmt)
count = res.fetchone()[0]
return count > 0
def merge_unconfident_table(self, instmt, coin, timestamp):
tb_name_post_fix = 'exch_{}_{}_snapshot_{}'.format(
instmt, coin, timestamp)
origin_table_name = '{}.{}'.format(self.origin_db.alias,
tb_name_post_fix)
target_table_name = '{}.{}'.format(self.target_db.alias,
tb_name_post_fix)
stmt = '''replace into {} (id, trade_px, trade_volume,
b1, b2, b3, b4, b5, a1, a2, a3, a4, a5,
bq1, bq2, bq3, bq4, bq5, aq1, aq2, aq3, aq4,
aq5, order_date_time, trades_date_time, update_type)
select distinct * from {}
where {}.trades_date_time != '20000101 00:00:00.000000'
and {}.order_date_time != '20000101 00:00:00.000000'
and {}.trades_date_time not in
(select distinct trades_date_time from {});
'''.format(target_table_name, origin_table_name, origin_table_name,
origin_table_name, origin_table_name, target_table_name)
try:
res = self.db_mgr.session.execute(stmt)
self.db_mgr.session.commit()
except Exception as e:
print(e)
self.db_mgr.session.rollback()
def merge_confident_table(self, instmt, coin, timestamp):
tb_name_post_fix = 'exch_{}_{}_snapshot_{}'.format(
instmt, coin, timestamp)
origin_table_name = '{}.{}'.format(self.origin_db.alias,
tb_name_post_fix)
target_table_name = '{}.{}'.format(self.target_db.alias,
tb_name_post_fix)
stmt = '''replace into {} (id, trade_px, trade_volume,
b1,b2,b3,b4,b5,a1,a2,a3,a4,a5,
bq1,bq2,bq3,bq4,bq5,aq1,aq2,aq3,aq4,
aq5,order_date_time,trades_date_time,update_type)
select distinct * from {}
where {}.trades_date_time != '20000101 00:00:00.000000'
and {}.order_date_time != '20000101 00:00:00.000000';
'''.format(target_table_name, origin_table_name, origin_table_name,
origin_table_name)
try:
res = self.db_mgr.session.execute(stmt)
self.db_mgr.session.commit()
except Exception as e:
print(e)
self.db_mgr.session.rollback()
def merge(self):
origin_tb_names = self.db_mgr.get_table_names_from_db(self.origin_db)
instmt_coin_table = {}
for tn in origin_tb_names:
r = re.search(r'.*.exch_([a-z]+)_(.*)_snapshot_(\d+)', tn)
if r:
if r.group(1) not in instmt_coin_table:
instmt_coin_table[r.group(1)] = {r.group(2): [r.group(3)]}
else:
if r.group(2) not in instmt_coin_table[r.group(1)]:
instmt_coin_table[r.group(1)][r.group(2)] = [
r.group(3)
]
else:
instmt_coin_table[r.group(1)][r.group(2)].append(
r.group(3))
import json
print(json.dumps(instmt_coin_table))
for instmt in instmt_coin_table:
for coin in instmt_coin_table[instmt]:
for timestamp in instmt_coin_table[instmt][coin]:
if timestamp >= self.since_date and timestamp <= self.end_date:
print('Merging data table: exch_{}_{}_{}'.format(
instmt, coin, timestamp))
if not self.db_mgr.is_table_existed(
self.target_db, 'exch_{}_{}_{}'.format(
instmt, coin, timestamp)):
self.create_market_data_table(
self.target_db, instmt, coin, timestamp)
if self.zone_info.is_confident(self.origin_db.zone,
instmt):
self.merge_confident_table(instmt, coin, timestamp)
else:
self.merge_unconfident_table(
instmt, coin, timestamp)