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 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)