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]