def _add_df(df, DestClass, fields=[], session=session):
"""Generic function for adding to a table from a dataframe.
args:
----
df - pandas dataframe - the records to be added to the database
DestClass - sqla table class - the class of the receiving table
session - sqla session:None - the offers db session object
returns:
----
None
"""
#Get table columns
tab_cols = DestClass.__table__.columns
#Remove table name prefix
tab_name = DestClass.__table__.name
tab_cols = [re.sub(fr"^{tab_name}\.", "", str(c)) for c in tab_cols]
cols = overlap([tab_cols, df.columns])
if len(fields) > 0:
cols = overlap([cols, fields])
df = df[cols]
objects = [DestClass(**r) for _, r in df.iterrows()]
session.bulk_save_objects(objects)
session.commit()
def _update_df(df, DestClass, session=session):
"""Function for updating records from a dataframe"""
#Get table columns
tab_cols = DestClass.__table__.columns
#Remove table name prefix
tab_name = DestClass.__table__.name
tab_cols = [re.sub(fr"^{tab_name}\.", "", str(c)) for c in tab_cols]
cols = overlap([tab_cols, df.columns])
session.bulk_update_mappings(DestClass, df[cols].to_dict(orient="records"))
session.commit()
def remove(self,
ids=[],
ticker_ids=[],
from_date=None,
to_date=None,
del_all=False):
"""Function to delete records from the weekly prices table.
args:
----
ids - list:[] - the ids of the records to be extracted
ticker_ids - list:[] - the ids of the records to be extracted
from_date - dtatime:None - the min date for filtering records
to_date - dtatime:None - the max date for filtering records
del_all - bool:False - safety to prevet deleting the whole table
returns:
----
sqlalchemy query
"""
try:
#Preform check to prevent del_all
if not del_all and not len(ids) and not len(
ticker_ids) and not from_date and not to_date:
log.warning(
"Delete not performed as no attributes given and del_all is False"
)
return False
query = session.query(WeeklyPrice)
if len(ids):
query = query.filter(WeeklyPrice.id.in_(ids))
if len(ticker_ids):
query = query.filter(WeeklyPrice.ticker_id.in_(ticker_ids))
if from_date:
query = query.filter(WeeklyPrice.date >= from_date)
if to_date:
query = query.filter(WeeklyPrice.date <= to_date)
query.delete(synchronize_session=False)
session.commit()
return True
except:
return False
def fetch_latest(self,
session,
ticker_ids=[],
from_date=None,
to_date=None):
"""Function to get that last entry for each item
args:
----
session - sqla session
ticker_ids - list:[] - the ids of the records to be extracted
from_date - dtatime:None - the min date for filtering records
to_date - dtatime:None - the max date for filtering records
returns:
----
sqla query
"""
#create the sub-query
subq = session.query(WeeklyPrice.ticker_id,
func.max(WeeklyPrice.date).label("max_date"))
#filter for dates
if from_date:
subq = subq.filter(WeeklyPrice.date >= from_date)
if to_date:
subq = subq.filter(WeeklyPrice.date <= to_date)
#order the results
subq = subq.order_by(WeeklyPrice.ticker_id, WeeklyPrice.date.desc()) \
.group_by(WeeklyPrice.ticker_id) \
.subquery("t2")
#build the main query
query = session.query(Ticker, subq.c.max_date) \
.outerjoin(
subq,
subq.c.ticker_id == Ticker.id
)
#filter on ticker ids wanted
if len(ticker_ids):
query = query.filter(Ticker.id.in_(ticker_ids))
return query
def fetch(self, ticker_ids=[], from_date=None, to_date=None):
"""Function to create a query to grab all sub-jobs from the offers db.
Open sub-jobs are set to status_id 1.
args:
----
ticker_ids - list:[] - the ids of the records to be extracted
returns:
----
sqlalchemy query
"""
query = session.query(TickerMarket)
if len(ticker_ids):
query = query.filter(TickerMarket.ticker_id.in_(ticker_ids))
if from_date:
query = query.filter(TickerMarket.first_seen_date >= from_date)
if to_date:
query = query.filter(TickerMarket.first_seen_date <= to_date)
return query
def fetch(self, ticker_ids=[], from_date=None, to_date=None):
"""Function to create a query to grab all tickers from the offers db.
args:
----
session - sqla session
ticker_ids - list:[] - the ids of the records to be extracted
from_date - dtatime:None - the min date for filtering records
to_date - dtatime:None - the max date for filtering records
returns:
----
sqlalchemy query
"""
query = session.query(Ticker)
if len(ticker_ids):
query = query.filter(Ticker.id.in_(ticker_ids))
if from_date:
query = query.filter(Ticker.last_seen_date >= from_date)
if to_date:
query = query.filter(Ticker.last_seen_date <= to_date)
return query