def get_last_time_db(self,schemaName, tableName, startTime, environment):
if (pgConn.table_exits(schemaName, tableName, environment)):
maxTime = pgConn.query('select max(time) from "{}"."{}"'.format(schemaName, tableName), environment=environment, dataframe=True)['max'][0]
if (maxTime is not None):
#maxTime += pd.Timedelta(minutes=1)
return maxTime
return startTime
def get_universe(self, d, top, uni_time, exchange_list, exclude_bool,
exclude_base, exclude_quote, include_bool, include_base,
include_quote):
sql = "select * from universe.mktcap_d{}_top{} where time <= '{}' order by time desc fetch first 5 rows only;"
try:
uni_time = datetime.strftime(uni_time, '%Y-%m-%d %H:%M:%S')
data = pgConn.query(sql.format(d, top, uni_time),
environment="aws_dev",
dataframe=True)
except:
print('ERROR: Check universe')
universe_symbol_all = []
length_d = 5
for i, symbol in enumerate(data.columns):
tf = [data.values[j][i] for j in range(5)]
if 'true' in tf:
base, quote, exchange = symbol.split('-')
if exclude_bool:
if (exchange in exchange_list) and (
base
not in exclude_base) and (quote
not in exclude_quote):
universe_symbol_all.append(data.columns[i])
if include_bool:
if (exchange in exchange_list) and (
base in include_base) and (quote in include_quote):
universe_symbol_all.append(data.columns[i])
universe_symbol = list(set(universe_symbol_all))
return universe_symbol
def get_data_range(index, startdate, enddate, days, option):
"""
get_data_range: setup query for sql
"""
schema = 'coincap'
tablename = 'history'
start_epoch = str(
time.mktime(
time.strptime(
str(pd.to_datetime(startdate).date() - timedelta(days)),
'%Y-%m-%d')))[:-2] + '000'
end_epoch = str(
time.mktime(
time.strptime(str(pd.to_datetime(enddate).date()),
'%Y-%m-%d')))[:-2] + '000'
q_data = """select * from {}.{} where symbol='{}' and ts >= '{}' and ts < '{}' order by ts asc""".format(
schema, tablename, index, start_epoch, end_epoch)
data = query(q_data, environment='aws_dev', dataframe=True)
return data
def last_db_date(self):
q_data = """select * from {}.{} order by time desc limit 5""".format(self.schema,self.table_name)
df = query(q_data, environment='aws_dev',dataframe=True)
return df['time'][3]
def get_universe(is_csv, d, top, uni_time, exchange_list):
sql = "select * from universe.mktcap_d{}_top{} where time <= '{}' order by time desc fetch first 5 rows only;"
# print(sql)
try:
uni_time = datetime.strftime(uni_time, '%Y-%m-%d %H:%M:%S')
data = pgConn.query(sql.format(d, top, uni_time),
environment="aws_dev",
dataframe=True)
except:
print('universe error')
# exchange_list = ['BTRX']
universe_symbol_all = []
length_d = 5
for i, symbol in enumerate(data.columns):
tf = [data.values[j][i] for j in range(5)]
if 'true' in tf:
base, quote, exchange = symbol.split('-')
if (exchange in exchange_list) and (base not in ex_coin) and (
quote not in ex_coin):
if is_csv:
if exchange in ['KRKN']:
if quote in ['USD']:
universe_symbol_all.append(data.columns[i])
elif exchange in ['BINA']:
universe_symbol_all.append('XRP-USDT-BINA')
if quote in ['BTC', 'USDT']:
universe_symbol_all.append(data.columns[i])
elif exchange in ['GDAX']:
if quote in ['BTC', 'USD']:
universe_symbol_all.append(data.columns[i])
else:
universe_symbol_all.append(data.columns[i])
else:
if exchange in ['BINA']:
universe_symbol_all.append('XRP-USDT-BINA')
if quote in ['BTC', 'USDT']:
universe_symbol_all.append(data.columns[i])
elif exchange in ['GDAX']:
if quote in ['BTC', 'USD']:
universe_symbol_all.append(data.columns[i])
else:
universe_symbol_all.append(data.columns[i])
'''
for day in range(length_d):
for i, symbol in enumerate(data.values[day]):
if symbol in ['true']:
base, quote, exchange = data.columns[i].split('-')
if (exchange in exchange_list) and (base not in ex_coin) and (quote not in ex_coin):
universe_symbol_all.append(data.columns[i])
'''
# TODO erase after coinapi update
# universe_symbol.append('XRP-USD-KRKN')
###
universe_symbol = list(set(universe_symbol_all))
print(universe_symbol)
print(len(universe_symbol))
return universe_symbol
import time
from collections import defaultdict
from utils.postgresConnection import storeInDb, query
from get_data_fns import get_coins, get_history_of_coin, join_history_data
if __name__ == '__main__':
coins = get_coins()
ts_df = query('select distinct ts, symbol from coincap.history',
environment='aws_dev',
dataframe=True)
unix_ts_already_done_dict = defaultdict(set)
for coin in set(ts_df['symbol'].values):
unix_ts_already_done_dict[coin] = set(
ts_df[ts_df['symbol'] == coin]['ts'].values)
while True:
for coin in get_coins():
data = get_history_of_coin(coin)
if data is None:
continue
df = join_history_data(data)
df['symbol'] = coin
ts_already_done = unix_ts_already_done_dict[coin]
df = df[~df['ts'].isin(ts_already_done)] # take away duplicates
unix_ts_already_done_dict[coin] |= set(df['ts'].values) # union
storeInDb(df, 'history', 'aws_dev', schema='coincap')
time.sleep(2.5)