def __init__(self, name):
task.Task.__init__(self, name)
self.name = name
#-- fetch DB info
db_dict = get_yaml(DB_YML)
#-- open db connection
self.conn = get_conn(db_dict["DB"], db_dict["Username"], db_dict["Password"], db_dict["Host"], db_dict["Port"])
try:
download_file = self.download_to_local()
self.save_formatted_data()
os.remove(download_file)
self.update_log_table(is_success=True)
except:
traceback.print_exc()
self.update_log_table(is_success=False)
raise RuntimeError('Download {stock_id} for {date} failed.'.format(
stock_id=self.stock_id, date=self.date))
finally:
queue_name = self.queue.get()
if __name__ == '__main__':
queue = Queue()
#-- fetch DB info
db_dict = get_yaml(DB_YML)
#-- open db connection
conn = get_conn(db_dict["DB"], db_dict["Username"], db_dict["Password"],
db_dict["Host"], db_dict["Port"])
s = Stock_trans_downloader(queue, conn, 'Netease_stock_transaction',
'000423', '20160415')
s.start()
s.join()
print 'All done.'
# if is_skip_holiday=True, return the most recent non-weekend day AND holiday will be skipped as well
holidays = []
if re.match("^\d{8}$", in_date):
date_date = datetime.datetime.strptime(in_date, '%Y%m%d')
else:
date_date = get_date(in_date, to_date=True)
if is_skip_holiday:
if conn is None:
raise RuntimeError(
'connection is None which must be available when skip_holiday mode is on.'
)
else:
cur = get_cur(conn)
cur.execute('select date from dw.holiday') # yyyymmdd
rows = list(cur)
for row in rows:
holidays.append(row['date'])
cur.close()
while date_date.isoweekday() >= 6 or date_date.strftime(
'%Y%m%d') in holidays:
date_date = date_date + datetime.timedelta(-1)
return date_date.strftime('%Y%m%d')
if __name__ == "__main__":
conn = get_conn("StockDb", "hong", "hong", "192.168.122.131", "5432")
print recent_working_day(is_skip_holiday=True, conn=conn)
print_log("There are %(num)s stock bankuai combination will be marked valid. %(combination)s" % {"num": len(codes_to_valid), "combination": codes_to_valid_str})
upd_sql = "update dw.dim_stock_bankuai t set is_valid = 'Y', upd_time = now() where %(combinations)s" % {"combinations": codes_to_valid_str}
cur.execute(upd_sql)
db_conn.commit()
else:
print_log("No stock bankuai combinations need to be marked valid.")
#---- insert stocks into dim_stock_bankuai
if len(bk_st_pairs_dict.keys()) > 0:
values = []
print_log("There are %(num)s stock bankuai combination will be inserted." % {"num": len(bk_st_pairs_dict.keys())})
for pk in bk_st_pairs_dict:
print_log(pk)
values.append("('%(stock_id)s', '%(bankuai_id)s', now(), 'Y')" % {"stock_id": bk_st_pairs_dict[pk]["st"], "bankuai_id": bk_st_pairs_dict[pk]["bk"]} )
values_str = ",".join(values)
ins_sql = "insert into dw.dim_stock_bankuai(stock_id, bankuai_id, upd_time, is_valid) values %(values)s" % {"values": values_str}
cur.execute(ins_sql)
db_conn.commit()
else:
print_log("No new stock bankuai combination.")
print_log("dw.dim_stock_bankuai has been refreshed successfully.")
if __name__ == "__main__":
db_dict = get_yaml('D:\\workspace\\Stock\\bin\\..\\etc\\db.yml')
conn = get_conn(db_dict["DB"], db_dict["Username"], db_dict["Password"], db_dict["Host"], db_dict["Port"])
load_into_stock_bankuai(conn, 'D:\\workspace\\Stock\\bin\\..\\log\\bankuai_stock_20160104.csv')
conn.close()
def recent_working_day(in_date='today', is_skip_holiday=False, conn=None): # date=yyyymmdd
# if is_skip_holiday=False, return the most recent non-weekend day
# if is_skip_holiday=True, return the most recent non-weekend day AND holiday will be skipped as well
holidays = []
if re.match("^\d{8}$", in_date):
date_date = datetime.datetime.strptime(in_date, '%Y%m%d')
else:
date_date = get_date(in_date, to_date=True)
if is_skip_holiday:
if conn is None:
raise RuntimeError('connection is None which must be available when skip_holiday mode is on.')
else:
cur = get_cur(conn)
cur.execute('select date from dw.holiday') # yyyymmdd
rows = list(cur)
for row in rows:
holidays.append(row['date'])
cur.close()
while date_date.isoweekday() >= 6 or date_date.strftime('%Y%m%d') in holidays:
date_date = date_date + datetime.timedelta(-1)
return date_date.strftime('%Y%m%d')
if __name__ == "__main__":
conn = get_conn("StockDb", "hong", "hong", "192.168.122.131", "5432")
print recent_working_day(is_skip_holiday=True, conn=conn)
