def delete_by_market_type_name(market_type_name): """ @return [True] """ market_type_query = select(MarketType) \ .where(MarketType.name == market_type_name) market_type = Session() \ .scalars(market_type_query) \ .first() delete_command = delete(FactorReturn) \ .where(FactorReturn.market_type_id == market_type.id) Session().execute(delete_command) return True
def fetch(ticker_symbol, start, end): """ @param [string] ticker_symbol Ticker symbol of the stock @param [Date] start Start date of the range (inclusive) of desired data @param [Date] end End date of the range (inclusive) of desired data @raise [pandas_datareader._utils.RemoteDataError] If Yahoo API response is not 200 @raise [requests.exceptions.ConnectionError] If unable to connect to the Yahoo API @return [pandas.core.frame.DataFrame] """ session = Session() query = session.query(InvestmentReturn).\ filter(InvestmentReturn.ticker_symbol == ticker_symbol).\ filter(InvestmentReturn.occurred_at >= start).\ filter(InvestmentReturn.occurred_at <= end) return pd.read_sql(query.statement, query.session.bind)
def write(market_type_name, data_frame): session = Session() factor_returns = FactorReturn.query_by_market_type_name(market_type_name) if not session.query(factor_returns.exists()).scalar(): market_type = session.query(MarketType).filter(MarketType.name == market_type_name).one() data_frame['market_type_id'] = market_type.id session.add_all([FactorReturn(**row) for _, row in data_frame.iterrows()]) session.commit()
def fetch(market_type_name, force_refresh=False): """ @param [String] market_type_name The market type, e.g. Emerging @param [Boolean] force_refresh If True, will wipe data and reimport it. @raise [???] If file can't be found @raise [???] If the file isn't a CSV @return [pandas.core.frame.DataFrame] """ session = Session() # If force_refresh, delete the data so that the rest of the code here # picks up on the need to re-download and write it. if force_refresh: FactorReturn.delete_by_market_type_name(market_type_name) factor_returns = FactorReturn.query_by_market_type_name(market_type_name) if not session.query(factor_returns.exists()).scalar(): FactorReturns.download_and_write_data() return pd.read_sql(factor_returns.statement, factor_returns.session.bind)
def all(self): items = Session().query(Model).all() result = [] for item in items: model = {"id" : item.id, "name" : item.name, "location" : item.location} result.append(model) return result
def createUser(userId, name, ses=Session()): dbUser = getUserById(userId, ses) if dbUser is not None: return user = User(message_count=0, karma=0, name=name, id=userId) ses.add(user) ses.commit()
def updateUserKarma(userId, val, ses=Session()): dbUser = getUserById(userId, ses) if dbUser is None: raise EntityNotFound('user not found') else: dbUser.karma += val ses.commit()
def createRoleReaction(serverId: str, reaction: str, role: str, ses=Session()): server = getServerById(serverId, ses) if server is None: raise EntityNotFound dbRR = ses.query(RoleReaction).filter(serverId == RoleReaction.server_id, RoleReaction.reaction == reaction, RoleReaction.role == role).first() if dbRR is not None: return False roleReaction = RoleReaction( reaction=reaction, server_id=serverId, role=role) ses.add(roleReaction) ses.commit()
def save(self): session = Session() session.add(self) try: session.commit() return True except InvalidRequestError: return False
def saveServer(serverId: str, channelId: str = None): session = Session() server = getServerById(serverId, session) if server is None: server = Server(id=serverId, role_reaction_channel_id=channelId) session.add(server) elif channelId is not None: setattr(server, 'role_reaction_channel_id', channelId) session.commit()
def populate_db(): session = Session() categories = [ 'gold', 'toman', ] session.add_all([Category(name=name) for name in categories]) session.commit()
def saveKarmaReaction(serverId: str, reaction: str, change: int): session = Session() karmaReaction = getKarmaReactionByServerAndReaction( serverId, reaction, session) if karmaReaction is None: karmaReaction = KarmaReaction(reaction=reaction, server_id=serverId, karmaChange=change) session.add(karmaReaction) else: karmaReaction.karmaChange = change session.commit()
def incUserMsgCount(user): ses = Session() dbUser = getUserById(str(user.id), ses) if dbUser is None: dbUser = User(message_count=1, karma=0, name=str(user), id=str(user.id)) ses.add(dbUser) else: dbUser.message_count += 1 ses.commit()
def update_amount(category_id, amount, delta): """Updates asset amount and returns the new amount""" session = Session() asset = _get_or_create_asset( session=session, category_id=category_id, ) if delta: amount = asset.amount + amount if amount < 0: raise NotEnoughAssetAmount() asset.amount = amount if asset.amount == 0: session.delete(asset) session.commit()
def createUsers(userList, ses=Session()): for u in userList: createUser(str(u.id), str(u), ses)
def find_by_id(self, identifier): item = Session().query(Model).filter(Model.id == identifier).one() return {"id" : item.id, "name" : item.name, "location" : item.location}
def removeKarmaReaction(serverId: str, kr: str): ses = Session() kr = getKarmaReactionByServerAndReaction(serverId, kr, ses) ses.delete(kr) ses.commit()
def removeAllKarmaReactions(serverId: str, ses=Session()): for k in getKarmaReactionsByServer(serverId, ses): ses.delete(k) ses.commit()
def removeRoleReaction(serverId: str, reaction: str, ses=Session()): reaction = getRoleReactionByServerAndReaction(serverId, reaction, ses) ses.delete(reaction) ses.commit()
def get_all_categories(): session = Session() return session.query(Category).all()
def getKarmaReactionsByServer(serverId: str, ses=Session()): return ses.query(KarmaReaction).filter( serverId == KarmaReaction.server_id).all()
def backfill_returns(ticker_symbol, start, end): """ Looks for investment returns for the ticker between the given start and end date. If some are present, checks the last time it was backfilled to see if there is a gap. If so, backfills either of these gaps. If none are present, backfills starting with the given start date. @param [string] ticker_symbol Ticker symbol of the stock @param [Date] start Start date of the range (inclusive) of desired data @param [Date] end End date of the range (inclusive) of desired data @raise [pandas_datareader._utils.RemoteDataError] If Yahoo API response is not 200 @raise [requests.exceptions.ConnectionError] If unable to connect to the Yahoo API """ session = Session() query = session.query(InvestmentReturn).\ filter(InvestmentReturn.ticker_symbol == ticker_symbol).\ filter(InvestmentReturn.occurred_at >= start).\ filter(InvestmentReturn.occurred_at <= end) if session.query(query.exists()).scalar(): max_occurred_at = session.query(func.max(InvestmentReturn.occurred_at)).filter(InvestmentReturn.ticker_symbol == ticker_symbol).scalar() if max_occurred_at < end: print(f'Ticker price data for ({ticker_symbol}, {max_occurred_at}, {end}) not found in the DB, backfilling it from the Yahoo API') new_start = date(max_occurred_at.year, max_occurred_at.month, 1) percentage_change_data = InvestmentReturns.get_percentage_change_data(ticker_symbol, new_start, end) if percentage_change_data.empty: print(f'WARNING: No new ticker price data found for ({ticker_symbol}, {max_occurred_at}, {end}) in the Yahoo API') session.add_all([InvestmentReturn(**row) for _, row in percentage_change_data.iterrows()]) session.commit() else: print(f'Ticker price data for ({ticker_symbol}, {start}, {end}) not found in the DB, backfilling it from the Yahoo API') one_month_ago = date.today() + pd.offsets.DateOffset(months=-1) _, last_day = monthrange(one_month_ago.year, one_month_ago.month) end = date(one_month_ago.year, one_month_ago.month, last_day) percentage_change_data = InvestmentReturns.get_percentage_change_data(ticker_symbol, start, end) session.add_all([InvestmentReturn(**row) for _, row in percentage_change_data.iterrows()]) session.commit()
def removeAllRoleReactions(serverId: str, ses=Session()): for r in getRoleReactionByServer(serverId, ses): ses.delete(r) ses.commit()
def getRoleReactionByServer(serverId: str, ses=Session()): return ses.query(RoleReaction).filter(serverId == RoleReaction.server_id,).all()
def getRoleReactionByServerAndReaction(serverId: str, reaction: str, ses=Session()): return ses.query(RoleReaction).filter(serverId == RoleReaction.server_id, RoleReaction.reaction == reaction).first()
def getUsers(count=20, page=0): ses = Session() return ses.query(User).filter(User.message_count > 0).order_by( desc(User.message_count)).offset(page * count).limit(count)
def getUserById(userId: str, session=Session()): return session.query(User).filter(User.id == userId).first()
def get_all_assets(): session = Session() return session.query(Asset).all()
def getUserCountWithMoreMessages(count): ses = Session() return ses.query(User).filter(User.message_count > count).count()
def getKarmaReactionByServerAndReaction(serverId: str, reaction: str, ses=Session()): return ses.query(KarmaReaction).filter( serverId == KarmaReaction.server_id, KarmaReaction.reaction == reaction).first()