def register_username_with_token(user_id, user_email, candidate_username):
with engine.connect() as conn:
matches = conn.execute("SELECT id FROM users WHERE username = %s",
candidate_username).fetchone()
if matches is None:
with engine.connect() as conn:
conn.execute("UPDATE users SET username = %s WHERE id = %s;",
(candidate_username, user_id))
return create_jwt(user_email, user_id, candidate_username)
return None
def get_index_portfolio_value_data(game_id: int,
symbol: str,
start_time: float = None,
end_time: float = None) -> pd.DataFrame:
"""In single-player mode a player competes against the indexes. This function just normalizes a dataframe of index
values by the starting value for when the game began
"""
start_time, end_time = get_time_defaults(game_id, start_time, end_time)
base_value = get_index_reference(game_id, symbol)
with engine.connect() as conn:
df = pd.read_sql("""
SELECT timestamp, `value` FROM indexes
WHERE symbol = %s AND timestamp >= %s AND timestamp <= %s;""",
conn,
params=[symbol, start_time, end_time])
index_info = query_to_dict(
"SELECT * FROM index_metadata WHERE symbol = %s", symbol)[0]
# normalizes index to the same starting scale as the user
df["value"] = STARTING_VIRTUAL_CASH * df["value"] / base_value
df["username"] = index_info["name"]
# When a game kicks off, it will generally be that case that there won't be an index data point at exactly that
# time. We solve this here, create a synthetic "anchor" data point that starts at the same time at the game
trade_start = make_index_start_time(start_time)
return pd.concat([
pd.DataFrame(
dict(username=index_info["name"],
timestamp=[trade_start],
value=[STARTING_VIRTUAL_CASH])), df
])
def get_game_balances(game_id: int, user_id: int, start_time: float = None, end_time: float = None):
start_time, end_time = get_time_defaults(game_id, start_time, end_time)
with engine.connect() as conn:
return pd.read_sql("""
SELECT timestamp, symbol, balance, transaction_type, order_status_id, stock_split_id FROM game_balances
WHERE game_id = %s AND user_id = %s AND balance_type = 'virtual_stock' AND timestamp >= %s AND timestamp <= %s
ORDER BY symbol, id;""", conn, params=[game_id, user_id, start_time, end_time])
def scrape_dividends(date: dt = None, timeout: int = 120) -> pd.DataFrame:
if date is None:
date = dt.now().replace(hour=0, minute=0, second=0, microsecond=0)
if get_trading_calendar(date, date).empty:
return pd.DataFrame()
day_of_month = str(date.day)
month = date.strftime('%B %Y')
driver = get_web_driver()
driver.get('https://www.thestreet.com/dividends/index.html')
table_x_path = '//*[@id="listed_divdates"]/table/tbody[2]'
if day_of_month != str(dt.now().day):
click_calendar(day_of_month, month, driver, timeout)
next_page = True
first_page = True
dividends_table = []
while next_page:
table = WebDriverWait(driver, timeout).until(
EC.visibility_of_element_located((By.XPATH, table_x_path)))
dividends_table += get_table_values(table)
next_page = click_next_page(table, first_page)
first_page = False
df = pd.DataFrame(
dividends_table,
columns=['symbol', 'company', 'amount', 'yield', 'exec_date'])
del df['yield']
df["amount"] = df['amount'].replace('[\$,]', '', regex=True).astype(float)
dividend_exdate = pd.to_datetime(df['exec_date'].iloc[0])
posix_dividend_exdate = datetime_to_posix(dividend_exdate)
df['exec_date'] = posix_dividend_exdate
df.drop_duplicates(inplace=True)
with engine.connect() as conn:
df.to_sql("dividends", conn, if_exists="append", index=False)
def get_open_game_ids_past_window():
"""This function returns game IDs for the subset of games that are both open and past their invite window. We pass
the resulting IDs to service_open_game to figure out whether to activate or close the game, and identify who's
participating
"""
with engine.connect() as conn:
result = conn.execute(
"""
SELECT g.id
FROM games g
INNER JOIN
(
SELECT gs.game_id, gs.status
FROM game_status gs
INNER JOIN
(SELECT game_id, max(id) as max_id
FROM game_status
GROUP BY game_id) grouped_gs
ON
gs.id = grouped_gs.max_id
WHERE gs.status = 'pending'
) pending_game_ids
ON
g.id = pending_game_ids.game_id
WHERE invite_window < %s;
""", time.time()
).fetchall(
) # yep, I know about UNIX_TIMESTAMP() -- this is necessary for test mocking
return [x[0] for x in result]
def get_external_email_invite_list(game_id: int):
with engine.connect() as conn:
result = conn.execute(
"""
SELECT ex.invited_email
FROM external_invites ex
INNER JOIN
(SELECT game_id, invited_email, max(id) as max_id
FROM external_invites
WHERE type = 'game'
GROUP BY game_id, invited_email) grouped_ex
ON
ex.id = grouped_ex.max_id
INNER JOIN
(SELECT ex2.invited_email, ex2.status, ex2.type
FROM external_invites ex2
INNER JOIN
(SELECT invited_email, max(id) as max_id
FROM external_invites
WHERE type = 'platform'
GROUP BY invited_email) grouped_ex2
ON
ex2.id = grouped_ex2.max_id) platform_ex
ON platform_ex.invited_email = ex.invited_email
WHERE
ex.game_id = %s AND
ex.status = 'invited' AND
platform_ex.status != 'accepted';""", game_id).fetchall()
return [x[0] for x in result]
def update_index_value(symbol):
value = get_index_value(symbol)
if during_trading_day():
add_row("indexes", symbol=symbol, value=value, timestamp=time.time())
return True
# a bit of logic to get the close of day price
with engine.connect() as conn:
max_time = conn.execute(
"SELECT MAX(timestamp) FROM indexes WHERE symbol = %s;",
symbol).fetchone()[0]
if max_time is None:
max_time = 0
ref_day = time.time()
eod = get_end_of_last_trading_day(ref_day)
while eod > ref_day:
ref_day -= SECONDS_IN_A_DAY
eod = get_end_of_last_trading_day(ref_day)
if max_time < eod <= time.time():
add_row("indexes", symbol=symbol, value=value, timestamp=eod)
return True
return False
def mark_invites_expired(game_id, status_list: List[str], update_time):
"""For a given game ID and list of statuses, this function will convert those invitations to "expired." This
happens when games past their invite window still have pending invitations, or when games pass their invite window
without meeting the minimum user count to kick off
"""
if not status_list:
return
with engine.connect() as conn:
result = conn.execute(
f"""
SELECT gi.user_id
FROM game_invites gi
INNER JOIN
(SELECT game_id, user_id, max(id) as max_id
FROM game_invites
GROUP BY game_id, user_id) grouped_gi
ON
gi.id = grouped_gi.max_id
WHERE
gi.game_id = %s AND
status IN ({','.join(['%s'] * len(status_list))});
""", game_id, *status_list)
ids_to_close = [x[0] for x in result]
for user_id in ids_to_close:
add_row("game_invites",
game_id=game_id,
user_id=user_id,
status="expired",
timestamp=update_time)
def get_user_invite_statuses_for_pending_game(game_id: int):
sql = f"""
SELECT creator_id, users.username, gi_status.status, users.profile_pic
FROM game_status gs
INNER JOIN
(SELECT game_id, max(id) as max_id
FROM game_status
GROUP BY game_id) grouped_gs
ON gs.id = grouped_gs.max_id
INNER JOIN
(SELECT gi.game_id, gi.user_id, gi.status
FROM game_invites gi
INNER JOIN
(SELECT game_id, user_id, max(id) as max_id
FROM game_invites
GROUP BY game_id, user_id) gg_invites
ON gi.id = gg_invites.max_id) gi_status
ON gi_status.game_id = gs.game_id
INNER JOIN users ON users.id = gi_status.user_id
INNER JOIN games g ON g.id = gs.game_id
WHERE gs.game_id = %s;
"""
with engine.connect() as conn:
return pd.read_sql(sql, conn,
params=[game_id]).to_dict(orient="records")
def get_current_stock_holding(user_id: int, game_id: int, symbol: str):
"""Get the user's current virtual cash balance for a given game. Expects a valid database connection for query
execution to be passed in from the outside
"""
sql_query = """
SELECT balance
FROM game_balances gb
INNER JOIN
(SELECT user_id, game_id, balance_type, max(id) as max_id
FROM game_balances
WHERE
user_id = %s AND
game_id = %s AND
symbol = %s AND
balance_type = 'virtual_stock'
GROUP BY game_id, balance_type, user_id) grouped_gb
ON
gb.id = grouped_gb.max_id;
"""
with engine.connect() as conn:
results = conn.execute(sql_query,
(user_id, game_id, symbol)).fetchall()
assert len(results) in [0, 1]
if len(results) == 1:
return results[0][0]
return 0
def get_active_balances(game_id: int, user_id: int):
"""It gets a bit messy, but this query also tacks on the price that the last order for a stock cleared at.
"""
sql = """
SELECT g.symbol, g.balance, g.timestamp, os.clear_price
FROM order_status os
RIGHT JOIN
(
SELECT gb.symbol, gb.balance, gb.balance_type, gb.timestamp, gb.order_status_id
FROM game_balances gb
INNER JOIN
(SELECT symbol, user_id, game_id, balance_type, max(id) as max_id
FROM game_balances
WHERE
game_id = %s AND
user_id = %s AND
balance_type = 'virtual_stock'
GROUP BY symbol, game_id, balance_type, user_id) grouped_gb
ON
gb.id = grouped_gb.max_id
WHERE balance > 0
) g
ON g.order_status_id = os.id;"""
with engine.connect() as conn:
return pd.read_sql(sql, conn, params=[game_id, user_id])
def get_user_ids(usernames: List[str]) -> List[int]:
with engine.connect() as conn:
res = conn.execute(
f"""
SELECT id FROM users WHERE username in ({",".join(['%s'] * len(usernames))});
""", usernames).fetchall()
return [x[0] for x in res]
def get_active_game_user_ids(game_id: int):
with engine.connect() as conn:
result = conn.execute(
"""
SELECT users FROM game_status
WHERE game_id = %s AND status = 'active'
ORDER BY id DESC LIMIT 0, 1;""", game_id).fetchone()[0]
return json.loads(result)
def update_symbols(n_rows=None):
symbols_table = get_symbols_table(n_rows=n_rows)
if symbols_table.empty:
raise SeleniumDriverError
with engine.connect() as conn:
conn.execute("TRUNCATE TABLE symbols;")
symbols_table.to_sql("symbols", conn, if_exists="append", index=False)
def get_requester_ids_from_email(email):
with engine.connect() as conn:
friend_ids = conn.execute(
"SELECT requester_id FROM external_invites WHERE invited_email = %s",
email).fetchall()
if friend_ids:
return list(set([x[0] for x in friend_ids]))
return []
def write_table_cache(cache_table_name: str, df: pd.DataFrame, **identifiers):
"""cache_table_name is where we want to save the dataframe to. additional identifiers for the cache, such as game_id
and user_id, are supplied as **identifiers keyword args
"""
for key, value in identifiers.items():
df[key] = value
with engine.connect() as conn:
df.to_sql(cache_table_name, conn, if_exists="append", index=False)
def get_index_reference(game_id: int, symbol: str) -> float:
ref_time, _ = get_game_start_and_end(game_id)
with engine.connect() as conn:
ref_val = conn.execute(
"""
SELECT value FROM indexes
WHERE symbol = %s AND timestamp <= %s
ORDER BY id DESC LIMIT 0, 1;""", symbol, ref_time).fetchone()
return ref_val[0]
def get_friend_rejections(user_id):
with engine.connect() as conn:
sql = """
SELECT DISTINCT invited_id
FROM friends WHERE
requester_id = %s AND
status = 'declined'
"""
result = conn.execute(sql, user_id).fetchall()
return [x[0] for x in result]
def check_platform_invite_exists(requester_id: int, invited_user_email: str):
with engine.connect() as conn:
res = conn.execute(
"""
SELECT id FROM external_invites
WHERE requester_id = %s AND LOWER(REPLACE(invited_email, '.', '')) = %s AND type = 'platform';""",
requester_id, standardize_email(invited_user_email)).fetchone()
if res:
return True
return False
def get_price_histories(symbols: List, min_time: float, max_time: float):
sql = f"""
SELECT timestamp, price, symbol FROM prices
WHERE
symbol IN ({','.join(['%s'] * len(symbols))}) AND
timestamp >= %s AND timestamp <= %s ORDER BY symbol, timestamp;"""
params_list = list(symbols) + [min_time, max_time]
with engine.connect() as conn:
df = pd.read_sql(sql, conn, params=params_list)
return df.sort_values("timestamp")
def get_usernames(user_ids: List[int]) -> Union[str, List[str]]:
"""If a single user_id is passed the function will return a single username. If an array is passed, it will
return an array of names
"""
with engine.connect() as conn:
usernames = conn.execute(
f"""
SELECT username FROM users WHERE id IN ({', '.join(['%s'] * len(user_ids))})
""", user_ids).fetchall()
return [x[0] for x in usernames]
def get_order_details(game_id: int,
user_id: int,
start_time: float = None,
end_time: float = None):
"""Retrieves order and fulfillment information for all orders for a game/user that have not been either cancelled
or expired
"""
start_time, end_time = get_time_defaults(game_id, start_time, end_time)
query = """
SELECT
o.id as order_id,
relevant_orders.status,
relevant_orders.order_status_id,
symbol,
relevant_orders.timestamp,
buy_or_sell,
quantity,
order_type,
time_in_force,
price,
relevant_orders.clear_price
FROM orders o
INNER JOIN (
SELECT os_full.id,
os_full.timestamp,
os_full.order_id,
os_full.clear_price,
os_full.status,
os_relevant.order_status_id
FROM order_status os_full
INNER JOIN (
SELECT os.order_id, grouped_os.max_id as order_status_id
FROM order_status os
INNER JOIN
(SELECT order_id, max(id) as max_id
FROM order_status
GROUP BY order_id) grouped_os
ON
os.id = grouped_os.max_id
WHERE os.status NOT IN ('cancelled', 'expired')
) os_relevant
ON os_relevant.order_id = os_full.order_id
) relevant_orders
ON relevant_orders.order_id = o.id
WHERE game_id = %s AND user_id = %s AND relevant_orders.timestamp >= %s AND relevant_orders.timestamp <= %s;"""
with engine.connect() as conn:
df = pd.read_sql(query,
conn,
params=[game_id, user_id, start_time, end_time])
df = pivot_order_details(df)
df["status"] = "fulfilled"
df.loc[df["timestamp_fulfilled"].isna(), "status"] = "pending"
return df
def get_last_sidebet_payout(game_id: int):
# when was the last time that a payout was made/that the game was started?
with engine.connect() as conn:
last_payout_date = conn.execute("""
SELECT end_time FROM winners
WHERE game_id = %s AND type = 'sidebet'
ORDER BY end_time DESC LIMIT 0, 1
""", game_id).fetchone()
if last_payout_date:
return last_payout_date[0]
return None
def add_row(table_name, **kwargs):
"""Convenience wrapper for DB inserts
"""
columns = list(kwargs.keys())
values = jsonify_inputs(list(kwargs.values()))
sql = f"""
INSERT INTO {table_name} ({','.join(columns)})
VALUES ({','.join(['%s'] * len(values))});
"""
with engine.connect() as conn:
result = conn.execute(sql, values)
return result.lastrowid
def get_user_ids_from_passed_emails(
invited_user_emails: List[str]) -> List[int]:
standardized_emails = [standardize_email(x) for x in invited_user_emails]
with engine.connect() as conn:
res = conn.execute(
f"""
SELECT id FROM users WHERE LOWER(REPLACE(email, '.', ''))
IN ({','.join(['%s'] * len(standardized_emails))})""",
standardized_emails).fetchall()
if res:
return [x[0] for x in res]
return []
def get_order_expiration_status(order_id):
"""Before processing an order, we'll use logic to determine whether that order is still active. This function
return True if an order is expired, or false otherwise.
"""
with engine.connect() as conn:
time_in_force = conn.execute(
"SELECT time_in_force FROM orders WHERE id = %s;",
order_id).fetchone()[0]
if time_in_force == "until_cancelled":
return False
# posix_to_datetime
current_time = time.time()
with engine.connect() as conn:
time_placed = conn.execute(
"""SELECT timestamp
FROM order_status
WHERE order_id = %s
ORDER BY id LIMIT 0, 1;""",
order_id).fetchone()[0]
time_placed_nyc = posix_to_datetime(time_placed)
cal_ref_time = time_placed_nyc.date()
schedule = get_trading_calendar(cal_ref_time, cal_ref_time)
if schedule.empty:
next_day_schedule = get_next_trading_day_schedule(time_placed_nyc)
_, cutoff_time = get_schedule_start_and_end(next_day_schedule)
else:
if time_placed_nyc.hour >= END_OF_TRADE_HOUR:
next_day_schedule = get_next_trading_day_schedule(time_placed_nyc +
timedelta(
days=1))
_, cutoff_time = get_schedule_start_and_end(next_day_schedule)
else:
_, cutoff_time = get_schedule_start_and_end(schedule)
if current_time > cutoff_time:
return True
return False
def check_external_game_invite(requester_id: int,
invited_user_email: str,
game_id: int = None):
with engine.connect() as conn:
res = conn.execute(
"""
SELECT id FROM external_invites
WHERE requester_id = %s AND LOWER(REPLACE(invited_email, '.', '')) = %s AND game_id = %s AND type='game'""",
requester_id, standardize_email(invited_user_email),
game_id).fetchone()
if res:
return True
return False
def scrape_stock_splits():
driver = get_web_driver()
_included_symbols = query_to_dict("SELECT symbol FROM symbols")
included_symbols = [x["symbol"] for x in _included_symbols]
nasdaq_raw_splits = retrieve_nasdaq_splits(driver, included_symbols)
nasdaq_splits = parse_nasdaq_splits(nasdaq_raw_splits)
nasdaq_symbols = nasdaq_splits["symbol"].to_list()
yahoo_raw_splits = retrieve_yahoo_splits(driver, included_symbols)
yahoo_splits = parse_yahoo_splits(yahoo_raw_splits, nasdaq_symbols)
df = pd.concat([nasdaq_splits, yahoo_splits])
if not df.empty:
with engine.connect() as conn:
df.to_sql("stock_splits", conn, if_exists="append", index=False)
def get_splits(start_time: float, end_time: float) -> pd.DataFrame:
if start_time is None:
start_time = datetime_to_posix(dt.utcnow().replace(hour=0, minute=0))
if end_time is None:
end_time = time.time()
# get active games and symbols
with engine.connect() as conn:
return pd.read_sql(
"SELECT * FROM stock_splits WHERE exec_date >= %s AND exec_date <= %s",
conn,
params=[start_time, end_time])
def get_suggested_friend_ids(text: str, excluded_ids: List[int]):
"""The excluded_ids list should be a list of user ids that we _don't_ want appearing as suggestions. Naturally, the
user's own ID should be a part of that list
"""
to_match = f"{text}%"
with engine.connect() as conn:
suggest_query = """
SELECT id FROM users
WHERE username LIKE %s
LIMIT 10;
"""
result = conn.execute(suggest_query, to_match).fetchall()
return [x[0] for x in result if x[0] not in excluded_ids]
