def load_batch_symbols(batch_id: str) -> pd.DataFrame:
query = f"""
SELECT
symbol
FROM
trending_tickers
WHERE
batch_id = '{batch_id}'
"""
loop = asyncio.get_event_loop()
return loop.run_until_complete(fetch_as_dataframe(query))
def load_traded_symbols(batch_id: str) -> pd.DataFrame:
query = f"""
SELECT
DISTINCT t.symbol
FROM
new_trades as t, algo_run as a
WHERE
t.algo_run_id = a.algo_run_id AND
a.batch_id = '{batch_id}'
"""
loop = asyncio.get_event_loop()
return loop.run_until_complete(fetch_as_dataframe(query))
def load_batch_list(day: date, env: str) -> pd.DataFrame:
query = f"""
SELECT DISTINCT a.batch_id, a.start_time
FROM
new_trades as t, algo_run as a
WHERE
t.algo_run_id = a.algo_run_id AND
t.tstamp >= '{day}' AND
t.tstamp < '{day + timedelta(days=1)}'AND
t.expire_tstamp is null
"""
loop = asyncio.get_event_loop()
return loop.run_until_complete(fetch_as_dataframe(query))
def load_trades(day: date, end_date: date = None) -> pd.DataFrame:
query = f"""
SELECT t.*, a.batch_id, a.algo_name
FROM
new_trades as t, algo_run as a
WHERE
t.algo_run_id = a.algo_run_id AND
t.tstamp >= '{day}' AND
t.tstamp < '{day + timedelta(days=1) if not end_date else end_date}' AND
t.expire_tstamp is null
ORDER BY symbol, tstamp
"""
loop = asyncio.get_event_loop()
return loop.run_until_complete(fetch_as_dataframe(query))
def load_runs(day: date, end_date: date = None) -> pd.DataFrame:
query = f"""
SELECT *
FROM
algo_run as t
WHERE
start_time >= '{day}' AND
start_time < '{day + timedelta(days=1) if not end_date else end_date}'
ORDER BY start_time
"""
loop = asyncio.get_event_loop()
df = loop.run_until_complete(fetch_as_dataframe(query))
df.set_index("algo_run_id", inplace=True)
return df
def load_trades_for_period(env: str, from_date: date,
to_date: date) -> pd.DataFrame:
query = f"""
SELECT client_time, symbol, operation, qty, price, algo_name
FROM
new_trades as t, algo_run as a
WHERE
t.algo_run_id = a.algo_run_id AND
t.tstamp >= '{from_date}' AND
t.tstamp < '{to_date}' AND
t.expire_tstamp is null AND
a.algo_env = '{env}'
ORDER BY symbol, tstamp
"""
loop = asyncio.get_event_loop()
return loop.run_until_complete(fetch_as_dataframe(query))
def load_trades_by_batch_id(batch_id: str) -> pd.DataFrame:
query = f"""
SELECT
t.*, a.batch_id, a.start_time, a.algo_name
FROM
new_trades as t, algo_run as a
WHERE
t.algo_run_id = a.algo_run_id AND
a.batch_id = '{batch_id}' AND
t.expire_tstamp is null
ORDER BY symbol, tstamp
"""
loop = asyncio.get_event_loop()
df: pd.DataFrame = loop.run_until_complete(fetch_as_dataframe(query))
try:
df["client_time"] = pd.to_datetime(df["client_time"])
except Exception:
tlog(
f"[Error] load_trades_by_batch_id({batch_id}) can't convert 'client_time' column to datetime"
)
return df
