class Database:
def __init__(self,
logger: Logger,
config: Config,
uri="sqlite:///data/crypto_trading.db"):
self.logger = logger
self.config = config
self.engine = create_engine(uri)
self.SessionMaker = sessionmaker(bind=self.engine)
self.socketio_client = Client()
def socketio_connect(self):
if self.socketio_client.connected and self.socketio_client.namespaces:
return True
try:
if not self.socketio_client.connected:
self.socketio_client.connect("http://api:5123",
namespaces=["/backend"])
while not self.socketio_client.connected or not self.socketio_client.namespaces:
time.sleep(0.1)
return True
except SocketIOConnectionError:
return False
@contextmanager
def db_session(self):
"""
Creates a context with an open SQLAlchemy session.
"""
session: Session = scoped_session(self.SessionMaker)
yield session
session.commit()
session.close()
def set_coins(self, symbols: List[str]):
session: Session
# Add coins to the database and set them as enabled or not
with self.db_session() as session:
# For all the coins in the database, if the symbol no longer appears
# in the config file, set the coin as disabled
coins: List[Coin] = session.query(Coin).all()
for coin in coins:
if coin.symbol not in symbols:
coin.enabled = False
# For all the symbols in the config file, add them to the database
# if they don't exist
for symbol in symbols:
coin = next((coin for coin in coins if coin.symbol == symbol),
None)
if coin is None:
session.add(Coin(symbol))
else:
coin.enabled = True
# For all the combinations of coins in the database, add a pair to the database
with self.db_session() as session:
coins: List[Coin] = session.query(Coin).filter(Coin.enabled).all()
for from_coin in coins:
for to_coin in coins:
if from_coin != to_coin:
pair = session.query(Pair).filter(
Pair.from_coin == from_coin,
Pair.to_coin == to_coin).first()
if pair is None:
session.add(Pair(from_coin, to_coin))
def get_coins(self, only_enabled=True) -> List[Coin]:
session: Session
with self.db_session() as session:
if only_enabled:
coins = session.query(Coin).filter(Coin.enabled).all()
else:
coins = session.query(Coin).all()
session.expunge_all()
return coins
def get_coin(self, coin: Union[Coin, str]) -> Coin:
if isinstance(coin, Coin):
return coin
session: Session
with self.db_session() as session:
coin = session.query(Coin).get(coin)
session.expunge(coin)
return coin
def set_current_coin(self, coin: Union[Coin, str]):
coin = self.get_coin(coin)
session: Session
with self.db_session() as session:
if isinstance(coin, Coin):
coin = session.merge(coin)
cc = CurrentCoin(coin)
session.add(cc)
self.send_update(cc)
def get_current_coin(self) -> Optional[Coin]:
session: Session
with self.db_session() as session:
current_coin = session.query(CurrentCoin).order_by(
CurrentCoin.datetime.desc()).first()
if current_coin is None:
return None
coin = current_coin.coin
session.expunge(coin)
return coin
def get_pair(self, from_coin: Union[Coin, str], to_coin: Union[Coin, str]):
from_coin = self.get_coin(from_coin)
to_coin = self.get_coin(to_coin)
session: Session
with self.db_session() as session:
pair: Pair = session.query(Pair).filter(
Pair.from_coin == from_coin, Pair.to_coin == to_coin).first()
session.expunge(pair)
return pair
def get_pairs_from(self,
from_coin: Union[Coin, str],
only_enabled=True) -> List[Pair]:
from_coin = self.get_coin(from_coin)
session: Session
with self.db_session() as session:
pairs = session.query(Pair).filter(Pair.from_coin == from_coin)
if only_enabled:
pairs = pairs.filter(Pair.enabled.is_(True))
pairs = pairs.all()
session.expunge_all()
return pairs
def get_pairs(self, only_enabled=True) -> List[Pair]:
session: Session
with self.db_session() as session:
pairs = session.query(Pair)
if only_enabled:
pairs = pairs.filter(Pair.enabled.is_(True))
pairs = pairs.all()
session.expunge_all()
return pairs
def log_scout(
self,
pair: Pair,
target_ratio: float,
current_coin_price: float,
other_coin_price: float,
):
session: Session
with self.db_session() as session:
pair = session.merge(pair)
sh = ScoutHistory(pair, target_ratio, current_coin_price,
other_coin_price)
session.add(sh)
self.send_update(sh)
def prune_scout_history(self):
time_diff = datetime.now() - timedelta(
hours=self.config.SCOUT_HISTORY_PRUNE_TIME)
session: Session
with self.db_session() as session:
session.query(ScoutHistory).filter(
ScoutHistory.datetime < time_diff).delete()
def prune_value_history(self):
session: Session
with self.db_session() as session:
# Sets the first entry for each coin for each hour as 'hourly'
hourly_entries: List[CoinValue] = (
session.query(CoinValue).group_by(
CoinValue.coin_id,
func.strftime("%H", CoinValue.datetime)).all())
for entry in hourly_entries:
entry.interval = Interval.HOURLY
# Sets the first entry for each coin for each day as 'daily'
daily_entries: List[CoinValue] = (
session.query(CoinValue).group_by(
CoinValue.coin_id, func.date(CoinValue.datetime)).all())
for entry in daily_entries:
entry.interval = Interval.DAILY
# Sets the first entry for each coin for each month as 'weekly'
# (Sunday is the start of the week)
weekly_entries: List[CoinValue] = (
session.query(CoinValue).group_by(
CoinValue.coin_id,
func.strftime("%Y-%W", CoinValue.datetime)).all())
for entry in weekly_entries:
entry.interval = Interval.WEEKLY
# The last 24 hours worth of minutely entries will be kept, so
# count(coins) * 1440 entries
time_diff = datetime.now() - timedelta(hours=24)
session.query(CoinValue).filter(
CoinValue.interval == Interval.MINUTELY,
CoinValue.datetime < time_diff).delete()
# The last 28 days worth of hourly entries will be kept, so count(coins) * 672 entries
time_diff = datetime.now() - timedelta(days=28)
session.query(CoinValue).filter(
CoinValue.interval == Interval.HOURLY,
CoinValue.datetime < time_diff).delete()
# The last years worth of daily entries will be kept, so count(coins) * 365 entries
time_diff = datetime.now() - timedelta(days=365)
session.query(CoinValue).filter(
CoinValue.interval == Interval.DAILY,
CoinValue.datetime < time_diff).delete()
# All weekly entries will be kept forever
def create_database(self):
Base.metadata.create_all(self.engine)
def start_trade_log(self, from_coin: Coin, to_coin: Coin, selling: bool):
return TradeLog(self, from_coin, to_coin, selling)
def send_update(self, model):
if not self.socketio_connect():
return
self.socketio_client.emit(
"update",
{
"table": model.__tablename__,
"data": model.info()
},
namespace="/backend",
)
def migrate_old_state(self):
"""
For migrating from old dotfile format to SQL db. This method should be removed in
the future.
"""
if os.path.isfile(".current_coin"):
with open(".current_coin") as f:
coin = f.read().strip()
self.logger.info(
f".current_coin file found, loading current coin {coin}")
self.set_current_coin(coin)
os.rename(".current_coin", ".current_coin.old")
self.logger.info(
f".current_coin renamed to .current_coin.old - You can now delete this file"
)
if os.path.isfile(".current_coin_table"):
with open(".current_coin_table") as f:
self.logger.info(
f".current_coin_table file found, loading into database")
table: dict = json.load(f)
session: Session
with self.db_session() as session:
for from_coin, to_coin_dict in table.items():
for to_coin, ratio in to_coin_dict.items():
if from_coin == to_coin:
continue
pair = session.merge(
self.get_pair(from_coin, to_coin))
pair.ratio = ratio
session.add(pair)
os.rename(".current_coin_table", ".current_coin_table.old")
self.logger.info(
".current_coin_table renamed to .current_coin_table.old - "
"You can now delete this file")
class Database:
def __init__(self, logger: Logger, config: Config):
self.logger = logger
self.config = config
self.socketio_client = Client()
engine_args = {}
if not config.DB_URI.startswith("sqlite"):
engine_args = {"pool_size": 10, "max_overflow": 20}
self.engine = create_engine(config.DB_URI, **engine_args)
self.session_factory = sessionmaker(bind=self.engine)
self.scoped_session_factory = scoped_session(self.session_factory)
def socketio_connect(self):
if not self.config.ENABLE_API:
return False
if self.socketio_client.connected and self.socketio_client.namespaces:
return True
try:
if not self.socketio_client.connected:
self.socketio_client.connect("http://api:5123",
namespaces=["/backend"])
while not self.socketio_client.connected or not self.socketio_client.namespaces:
time.sleep(0.1)
return True
except SocketIOConnectionError:
return False
@contextmanager
def db_session(self):
"""
Creates a context with an open SQLAlchemy session.
"""
session: Session = self.scoped_session_factory()
yield session
session.commit()
session.close()
def set_coins(self, symbols: List[str]):
session: Session
# Add coins to the database and set them as enabled or not
with self.db_session() as session:
# For all the coins in the database, if the symbol no longer appears
# in the config file, set the coin as disabled
coins: List[Coin] = session.query(Coin).all()
for coin in coins:
if coin.symbol not in symbols:
coin.enabled = False
# For all the symbols in the config file, add them to the database
# if they don't exist
for symbol in symbols:
coin = next((coin for coin in coins if coin.symbol == symbol),
None)
if coin is None:
session.add(Coin(symbol))
else:
coin.enabled = True
# For all the combinations of coins in the database, add a pair to the database
with self.db_session() as session:
coins: List[Coin] = session.query(Coin).filter(Coin.enabled).all()
for from_coin in coins:
for to_coin in coins:
if from_coin != to_coin:
pair = session.query(Pair).filter(
Pair.from_coin == from_coin,
Pair.to_coin == to_coin).first()
if pair is None:
session.add(Pair(from_coin, to_coin))
def get_coins(self, only_enabled=True) -> List[Coin]:
session: Session
with self.db_session() as session:
if only_enabled:
coins = session.query(Coin).filter(Coin.enabled).all()
else:
coins = session.query(Coin).all()
session.expunge_all()
return coins
def get_coin(self, coin: Union[Coin, str]) -> Coin:
if isinstance(coin, Coin):
return coin
session: Session
with self.db_session() as session:
coin = session.query(Coin).get(coin)
session.expunge(coin)
return coin
def set_current_coin(self, coin: Union[Coin, str]):
coin = self.get_coin(coin)
session: Session
with self.db_session() as session:
if isinstance(coin, Coin):
coin = session.merge(coin)
cc = CurrentCoin(coin)
session.add(cc)
self.send_update(cc)
def get_current_coin(self) -> Optional[Coin]:
session: Session
with self.db_session() as session:
current_coin = session.query(CurrentCoin).order_by(
CurrentCoin.datetime.desc()).first()
if current_coin is None:
return None
coin = current_coin.coin
session.expunge(coin)
return coin
def get_current_coin_date(self) -> Optional[datetime]:
session: Session
with self.db_session() as session:
current_coin = session.query(CurrentCoin).order_by(
CurrentCoin.datetime.desc()).first()
if current_coin is None:
return datetime.now()
coin_date = current_coin.datetime
return coin_date
def get_pair(self, from_coin: Union[Coin, str], to_coin: Union[Coin, str]):
from_coin = self.get_coin(from_coin)
to_coin = self.get_coin(to_coin)
session: Session
with self.db_session() as session:
pair: Pair = session.query(Pair).filter(
Pair.from_coin == from_coin, Pair.to_coin == to_coin).first()
session.expunge(pair)
return pair
def get_pairs_from(self,
from_coin: Union[Coin, str],
only_enabled=True) -> List[Pair]:
from_coin = self.get_coin(from_coin)
session: Session
with self.db_session() as session:
pairs = session.query(Pair).filter(Pair.from_coin == from_coin)
if only_enabled:
pairs = pairs.filter(Pair.enabled.is_(True))
pairs = pairs.all()
session.expunge_all()
return pairs
def get_pairs(self, only_enabled=True) -> List[Pair]:
session: Session
with self.db_session() as session:
pairs = session.query(Pair)
if only_enabled:
pairs = pairs.filter(Pair.enabled.is_(True))
pairs = pairs.all()
session.expunge_all()
return pairs
def log_scout(
self,
pair: Pair,
target_ratio: float,
current_coin_price: float,
other_coin_price: float,
):
session: Session
with self.db_session() as session:
pair = session.merge(pair)
sh = ScoutHistory(pair, target_ratio, current_coin_price,
other_coin_price)
session.add(sh)
self.send_update(sh)
def prune_scout_history(self):
time_diff = datetime.now() - timedelta(
hours=self.config.SCOUT_HISTORY_PRUNE_TIME)
session: Session
with self.db_session() as session:
session.query(ScoutHistory).filter(
ScoutHistory.datetime < time_diff).delete()
def prune_value_history(self):
def _datetime_id_query(default, sqlite):
dt_column = if_dialect(
default=func.to_char(CoinValue.datetime, default),
sqlite=func.strftime(sqlite, CoinValue.datetime),
)
grouped = select(CoinValue, func.max(CoinValue.datetime),
dt_column).group_by(CoinValue.coin_id, CoinValue,
dt_column)
return select(grouped.c.id.label("id")).select_from(grouped)
def _update_query(datetime_query, interval):
return (update(CoinValue).where(
CoinValue.id.in_(datetime_query)).values(
interval=interval).execution_options(
synchronize_session="fetch"))
hourly_update_query = _update_query(
_datetime_id_query(default="HH24", sqlite="%H"), Interval.HOURLY)
weekly_update_query = _update_query(
_datetime_id_query(default="YYYY-WW", sqlite="%Y-%W"),
Interval.WEEKLY,
)
daily_update_query = _update_query(
_datetime_id_query(default="YYYY-DDD", sqlite="%Y-%j"),
Interval.DAILY,
)
session: Session
with self.db_session() as session:
# Sets the first entry for each coin for each hour as 'hourly'
session.execute(hourly_update_query)
# Sets the first entry for each coin for each day as 'daily'
session.execute(daily_update_query)
# Sets the first entry for each coin for each month as 'weekly'
# (Sunday is the start of the week)
session.execute(weekly_update_query)
# Early commit to make sure the delete statements work properly.
session.commit()
# The last 24 hours worth of minutely entries will be kept, so
# count(coins) * 1440 entries
time_diff = datetime.now() - timedelta(hours=24)
session.query(CoinValue).filter(
CoinValue.interval == Interval.MINUTELY,
CoinValue.datetime < time_diff).delete()
# The last 28 days worth of hourly entries will be kept, so count(coins) * 672 entries
time_diff = datetime.now() - timedelta(days=28)
session.query(CoinValue).filter(
CoinValue.interval == Interval.HOURLY,
CoinValue.datetime < time_diff).delete()
# The last years worth of daily entries will be kept, so count(coins) * 365 entries
time_diff = datetime.now() - timedelta(days=365)
session.query(CoinValue).filter(
CoinValue.interval == Interval.DAILY,
CoinValue.datetime < time_diff).delete()
# All weekly entries will be kept forever
def create_database(self):
Base.metadata.create_all(self.engine)
def start_trade_log(self, from_coin: Coin, to_coin: Coin, selling: bool):
return TradeLog(self, from_coin, to_coin, selling)
def send_update(self, model):
if not self.socketio_connect():
return
self.socketio_client.emit(
"update",
{
"table": model.__tablename__,
"data": model.info()
},
namespace="/backend",
)
def _scenario_task(self) -> None:
client = Client()
client.connect(os.environ[ENV_LOAD_TEST_TARGET_URL])
client.emit("session_request")
runner.run(scenario, client)
print('reset')
return self.get_state()
def step(self, action):
return s, r, done
def get_state(self):
return np.array([x1, y1, x2, y2, x3, y3, xt, yt])
"""
io = IO(
json=JSON(), #logger=True, engineio_logger=True
)
Emit = lambda *args: io.emit('Container', list(args), namespace=namespace)
def On(value, key=''):
global io, namespace
isFn = callable(value)
if not key:
key = value.__name__ if isFn else type(value).__name__
print(key)
@io.on(key, namespace=namespace)
def handler(data):
fn = value if isFn else getattr(value, data[0])
class Model:
def __init__(self,
render,
render_change,
endpoint='http://127.0.0.1:5000'):
self.endpoint = endpoint
self.render = render
self.render_change = render_change
# self.user = json.loads(
# requests.get(f"{endpoint}/register").text)['id']
self.socket = Client(logger=False)
self.socket.on('login', self.register_self)
self.socket.connect(endpoint)
self.socket.emit('login')
# def get_data(self):
# return json.loads(requests.get(self.endpoint + '/get_data',
# data={'id': self.user}).text)
def register_self(self, player):
# to do this correctly you would need to use rooms
self.namespace = f"/data{player}"
self.socket = Client(logger=True) # this is bad
self.socket.connect(self.endpoint, namespaces=[self.namespace])
self.socket.emit('logged_in', {'id': player})
self.socket.on('start', self.render, namespace=self.namespace)
self.socket.on('change', self.render_change, namespace=self.namespace)
def send_letter(self, letter):
self.socket.emit('type', {'key': letter}, namespace=self.namespace)
def remove_letter(self):
self.socket.emit('remove', namespace=self.namespace)
def publish_current_word(self):
self.socket.emit('publish', namespace=self.namespace)
def switch_typing_mode(self, event=None):
self.socket.emit('toggle', namespace=self.namespace)
