insert_solido_prices_sqlite.py

#!/usr/bin/env python3

import csv, sys, sqlite3, datetime

csv_path = sys.argv[1]
sqlite_path = sys.argv[2]
with open(csv_path) as csvfile:
    conn = sqlite3.connect(sqlite_path)
    reader = csv.reader(csvfile, delimiter=',')
    # Skip 1st line
    next(reader)

    insert_stmt = ''' INSERT INTO exchange_rate (timestamp, slot, epoch, pool, price_lamports_numerator, price_lamports_denominator)
              VALUES(?,?,?,?,?,?) '''

    create_db = ''' CREATE TABLE IF NOT EXISTS exchange_rate (
                id          INTEGER PRIMARY KEY,
                --- timestamp is stored in ISO-8601 format.
                timestamp                   TEXT,
                slot                        INTEGER NOT NULL,
                epoch                       INTEGER NOT NULL,
                pool                        TEXT NOT NULL,
                price_lamports_numerator    INTEGER NOT NULL,
                price_lamports_denominator  INTEGER NOT NULL,
                CHECK (price_lamports_denominator>0)
                );
                CREATE INDEX IF NOT EXISTS ix_exchange_rate_timestamp ON exchange_rate (timestamp);
                CREATE INDEX IF NOT EXISTS ix_exchange_rate_slot ON exchange_rate (slot);
                '''
    conn.executescript(create_db)
    cur = conn.cursor()

    last_epoch = 0
    last_slot = 0
    last_blocktime = 0
    last_exchange_rate = 0.0

    for (line, row) in enumerate(reader):
        if len(row) == 0:
            continue
        (epoch, slot, blocktime, sol_deposited, st_sol_minted) = (
            int(row[0]),
            int(row[2]),
            int(row[3]),
            float(row[4]),
            float(row[5]),
        )
        sol_deposited = int(sol_deposited * 1e9)
        st_sol_minted = int(st_sol_minted * 1e9)
        if st_sol_minted == 0:
            print(f'WARNING: 0 stSOL minted at line {line}')
            continue
        assert (
            epoch >= last_epoch
        ), f'Observed epoch {epoch}, smaller than previous one {last_epoch} at line {line}'
        assert (
            slot >= last_slot
        ), f'Observed slot {slot}, smaller than previous one {last_slot} at line {line}'
        assert (
            blocktime >= last_blocktime
        ), f'Observed blocktime {blocktime}, smaller than previous one {last_blocktime} at line {line}'

        exchange_rate = sol_deposited / st_sol_minted
        if exchange_rate < last_exchange_rate:
            print(
                f'WARNING: Observed exchange rate {exchange_rate}, smaller than previous one {last_exchange_rate} at line {line}'
            )
        # assert (
        #     exchange_rate >= last_exchange_rate
        # ), f'Observed exchange rate {exchange_rate}, smaller than previous one {last_exchange_rate} at line {line}'

        (last_epoch, last_slot, last_blocktime, last_exchange_rate) = (
            epoch,
            slot,
            blocktime,
            exchange_rate,
        )
        date_iso8601 = (
            datetime.datetime.fromtimestamp(blocktime)
            .replace(tzinfo=datetime.timezone.utc)
            .isoformat()
        )
        cur.execute(
            insert_stmt,
            (date_iso8601, slot, epoch, 'solido', sol_deposited, st_sol_minted),
        )
    conn.commit()