def get_hist_data_as_dataframes_dict(metadata=None, limit=0, max_scrips=16000):
lscrips = get_all_scrips_names_in_db(metadata=metadata)
e = metadata.bind
hist_data = create_or_get_nse_equities_hist_data(metadata=metadata)
scripdata_dict = {}
scrips = 0
for scrip in lscrips:
sql_st = select_expr([hist_data.c.date,
hist_data.c.open, hist_data.c.high,
hist_data.c.low, hist_data.c.close,
hist_data.c.volume, hist_data.c.delivery]).\
where(hist_data.c.symbol == scrip).\
order_by(desc(hist_data.c.date))
if limit and isinstance(limit, int) and limit > 0:
sql_st = sql_st.limit(limit)
scripdata = pd.io.sql.read_sql(sql_st, e)
scripdata.columns = [
'date', 'open', 'high', 'low', 'close', 'volume', 'delivery'
]
scripdata.reset_index(inplace=True)
scripdata.set_index(pd.DatetimeIndex(scripdata['date']), inplace=True)
scripdata.drop('date', axis=1, inplace=True)
scripdata_dict[scrip] = scripdata
scrips += 1
if scrips == max_scrips:
break
return scripdata_dict
def _update_bhavcopy(curdate, stocks_dict):
"""update bhavcopy Database date in DD-MM-YYYY format."""
nse_eq_hist_data = create_or_get_nse_equities_hist_data(
metadata=_DB_METADATA)
# delete for today's date if there's anything FWIW
module_logger.debug("Deleting any old data for date %s.", curdate)
d = nse_eq_hist_data.delete(nse_eq_hist_data.c.date == curdate)
r = execute_one(d, engine=_DB_METADATA.bind)
module_logger.debug("Deleted %d rows.", r.rowcount)
insert_statements = []
for k, v in stocks_dict.items():
ins = nse_eq_hist_data.insert().values(symbol=k,
date=curdate,
open=v.open,
high=v.high,
low=v.low,
close=v.close,
volume=v.volume,
delivery=v.deliv)
insert_statements.append(ins)
module_logger.debug(ins.compile().params)
results = execute_many_insert(insert_statements, engine=_DB_METADATA.bind)
for r in results:
r.close()
def get_hist_data_as_dataframes_dict():
lscrips = get_all_scrips_names_in_db()
e = get_engine()
hist_data = create_or_get_nse_equities_hist_data()
scripdata_dict = {}
for scrip in lscrips:
sql_st = select_expr([hist_data.c.date,
hist_data.c.open, hist_data.c.high,
hist_data.c.low, hist_data.c.close,
hist_data.c.volume, hist_data.c.delivery]).\
where(hist_data.c.symbol == scrip).\
order_by(hist_data.c.date)
scripdata = pd.io.sql.read_sql(sql_st, e)
scripdata.columns = [
'date', 'open', 'high', 'low', 'close', 'volume', 'delivery'
]
scripdata.reset_index(inplace=True)
scripdata.set_index(pd.DatetimeIndex(scripdata['date']), inplace=True)
scripdata.drop('date', axis=1, inplace=True)
scripdata_dict[scrip] = scripdata
return scripdata_dict
def _apply_name_changes_to_db(syms):
"""Changes security names in nse_hist_data table so the name of the security
is always the latest."""
hist_data = create_or_get_nse_equities_hist_data(metadata=_DB_METADATA)
update_statements = []
for sym in syms:
old = sym[0]
new = sym[1]
chdate = sym[2]
chdt = dt.date(dt.strptime(chdate, '%d-%b-%Y'))
upd = hist_data.update().values(symbol=new).\
where(and_expr(hist_data.c.symbol == old,
hist_data.c.date < chdt))
update_statements.append(upd)
results = execute_many_insert(update_statements, engine=_DB_METADATA.bind)
for r in results:
r.close()
def _get_data_for_symbol(self, symbol):
"""
Internal function that reads DB for each symbol, we should see if we can make it parallel using Dask
"""
if not self._db_meta:
raise TickProcessWorkerExceptionInvalidDB(
"SQLAlchemy Metadata Not Initialized")
engine = self._db_meta.bind
if not engine:
raise TickProcessWorkerExceptionInvalidDB(
"SQLAlchemy Metadata not bound to an Engine.")
hist_data = create_or_get_nse_equities_hist_data(
metadata=self._db_meta)
sql_st = select_expr([hist_data.c.date,
hist_data.c.open, hist_data.c.high,
hist_data.c.low, hist_data.c.close,
hist_data.c.volume, hist_data.c.delivery]).\
where(hist_data.c.symbol == symbol).\
order_by(hist_data.c.date)
scripdata = pd.io.sql.read_sql(sql_st, engine)
if not scripdata.empty:
scripdata.columns = [
'date', 'open', 'high', 'low', 'close', 'volume', 'delivery'
]
scripdata.reset_index(inplace=True)
scripdata.set_index(pd.DatetimeIndex(scripdata['date']),
inplace=True)
scripdata.drop('date', axis=1, inplace=True)
return symbol, scripdata