from py_jftech import read, where, format_date, mapper_columns, write __COLUMNS__ = { 're_id': 'id', 're_ticker': 'ticker', 're_date': 'date', 're_close': 'close', } @write def batch_insert(datas): datas = [mapper_columns(x, __COLUMNS__, ignore_none=False) for x in datas] values = ','.join([f'''({','.join([(f"'{x[j]}'" if j in x and x[j] is not None else 'null') for j in __COLUMNS__.keys() if j != 're_id'])})''' for x in datas]) return f'''insert into robo_exrate({','.join([x for x in __COLUMNS__.keys() if x != 're_id'])}) values {values}''' @read def get_exrates(ticker=None, min_date=None, max_date=None): sqls = [] if min_date: sqls.append(f"re_date >= '{format_date(min_date)}'") if max_date: sqls.append(f"re_date <= '{format_date(max_date)}'") return f'''select {','.join([f"{x[0]} as {x[1]}" for x in __COLUMNS__.items()])} from robo_exrate {where(*sqls, re_ticker=ticker)}''' @read(one=True) def get_exrate(ticker, date): return f'''select {','.join([f"{x[0]} as {x[1]}" for x in __COLUMNS__.items()])} from robo_exrate {where(re_ticker=ticker, re_date=date)}''' @read(one=True) def get_last_one(ticker, max_date=None): sql = f"re_date <= '{format_date(max_date)}'" if max_date else None return f''' select {','.join([f"{x[0]} as {x[1]}" for x in __COLUMNS__.items()])} from robo_exrate {where(sql, re_ticker=ticker)} order by re_date desc limit 1 '''