async def insert_trade_dates_prediction_data(data: Collection[dict]):
async with connection() as cur:
for item in data:
await cur.execute(
psql_insert(trade_date, item).on_conflict_do_update(
index_elements=['date'],
set_={
'predicted': item['predicted'],
'prediction_error': item['prediction_error'],
'accumulated_error': item['accumulated_error'],
}))
async def insert_bcse(data: Iterable[BcseData]):
values = [{
'currency': x.currency,
'timestamp': x.timestamp_operation,
'timestamp_received': x.timestamp_received,
'rate': x.rate,
} for x in data]
if not values:
return
async with connection() as cur:
await cur.execute(psql_insert(bcse, values).on_conflict_do_nothing())
async def insert_rolling_average(date: datetime.date, duration: int,
data: Sequence[Decimal]):
data = dict(
date=date,
duration=duration,
eur=data[0],
rub=data[1],
uah=data[2],
dxy=data[3],
)
async with connection() as cur:
await cur.execute(
psql_insert(rolling_average, data).on_conflict_do_update(
index_elements=['date', 'duration'], set_=data))
async def insert_nbrb(data: Iterable[dict], *, kind: NbrbKind):
data = [{x.lower(): item[x] for x in item} for item in data]
if not data:
return
async with connection() as conn:
for item in data:
date = item.pop('date')
await conn.execute(
psql_insert(nbrb, {
'kind': kind.value,
'date': date,
**item
}).on_conflict_do_update(index_elements=['kind', 'date'],
set_=item))
async def insert_external_rate_live(row: ExternalRateData):
async with connection() as cur:
await cur.execute(
psql_insert(
external_rate_live,
dict(
currency=row.currency,
timestamp=row.timestamp_open,
volume=row.volume,
timestamp_received=row.timestamp_received,
rate=row.close,
)).on_conflict_do_update(
index_elements=['currency', 'timestamp', 'volume'],
set_={
'timestamp_received': row.timestamp_received,
'rate': row.close,
}))
async def insert_external_rates(currency: str,
data: Iterator[Tuple[int, str, str, str, str,
int]]):
values = [{
'currency': currency,
'timestamp': x[0],
'open': x[1],
'close': x[2],
'low': x[3],
'high': x[4],
'volume': x[5],
} for x in data]
if not values:
return
async with connection() as cur:
await cur.execute(
psql_insert(external_rate, values).on_conflict_do_nothing())
def upsert(table, data, conn, index_elements=["id"], exclude_columns=["id"]):
"""
table: sqlalchemy table ojbect
data: list of {colname: value} dicts
conn: db connection to execute upsert on
index_elements: index columns to check for conflicts on
exclude_columns: don't update these columns
"""
print("First row in data:", data[0])
insert_stmt = psql_insert(table).values(data)
update_columns = {
col.name: col
for col in insert_stmt.excluded if col.name not in exclude_columns
}
upsert_stmt = insert_stmt.on_conflict_do_update(
index_elements=index_elements, set_=update_columns)
r = conn.execute(upsert_stmt)
print(r.rowcount, "rows added/updated in", table.name)
async def insert_trade_dates(trade_dates: Collection[str]):
async with connection() as cur:
await cur.execute(
psql_insert(trade_date, [{
'date': x
} for x in trade_dates]).on_conflict_do_nothing())