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 _update_dload_success(fdate, bhav_ok, deliv_ok, error_code=None):
""" Update whether bhavcopy download and delivery data download for given
date is successful"""
tbl = create_or_get_nse_bhav_deliv_download_info(metadata=_DB_METADATA)
sel_st = select_expr([tbl]).where(tbl.c.download_date == fdate)
res = execute_one(sel_st, engine=_DB_METADATA.bind)
# res.first closes the result
first_row = res.first()
# Following is the closest to what I wanted for an 'upsert' support in
# DB agnostic way. Clearly this is not most ideal, but as of now I do not
# know of better way of doing this.
# This issue discusses something similar
# https://groups.google.com/forum/#!topic/sqlalchemy/63OnY_ZFmic
if not first_row:
ins_or_upd_st = tbl.insert().values(download_date=fdate,
bhav_success=bhav_ok,
deliv_success=deliv_ok,
error_type=error_code)
else:
module_logger.info("Found row. Updating %s", str(first_row))
ins_or_upd_st = tbl.update().where(tbl.c.download_date == fdate).\
values(download_date=fdate,
bhav_success=bhav_ok,
deliv_success=deliv_ok,
error_type=error_code)
module_logger.debug(ins_or_upd_st.compile().params)
result = execute_one_insert(ins_or_upd_st, engine=_DB_METADATA.bind)
result.close()
def get_all_scrips_names_in_db(metadata=None):
all_scrips_table = create_or_get_all_scrips_table(metadata=metadata)
scrips_select_st = select_expr([all_scrips_table.c.nse_symbol]).\
where(all_scrips_table.c.nse_traded == True)
result = execute_one(scrips_select_st, engine=metadata.bind)
symbols = [row[0] for row in result.fetchall()]
return symbols
def _do_read_symbols(self):
"""
Reads symbols from database.
"""
if not self._db_meta.bind:
raise TickProcessWorkerExceptionInvalidDB(
"SQLAlchemy Metadata not bound to an Engine.")
all_scrips_table = create_or_get_all_scrips_table(
metadata=self._db_meta)
scrips_select_st = select_expr([all_scrips_table.c.nse_symbol]).\
where(all_scrips_table.c.nse_traded == True)
result = execute_one(scrips_select_st, engine=self._db_meta.bind)
symbols = [row[0] for row in result.fetchall()]
return symbols
def _bhavcopy_downloaded(fdate):
"""
Returns success/failure for a given date if bhav/delivery data.
"""
tbl = create_or_get_nse_bhav_deliv_download_info(metadata=_DB_METADATA)
select_st = tbl.select().where(tbl.c.download_date == fdate)
result = execute_one(select_st.compile(), engine=_DB_METADATA.bind)
result = result.fetchone()
if not result:
return False
# For anything older than 7 days from now, if Error is not found,
# we ignore this.
d = dt.date(dt.today())
delta = d - fdate
ignore_error = False
if (delta.days > 7) and result.error_type == 'NOT_FOUND':
ignore_error = True
return (result[1] and result[2]) or ignore_error