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()
