Best-Effort SQLite Logging for a Trading Bot | Toss Dev #3-2

Last time I only nailed down the schema and interface, no code yet. This time I actually built db.py. Leaving a day or two between design and implementation made the coding go a lot smoother.

What I built

Following the design doc, I implemented five functions: get_connection(), init_db(), insert_trade(), insert_alert(), and insert_price_snapshot(). I also added a DB_PATH setting to config.py so the database file path isn’t hardcoded anywhere.

Core design principle: best-effort logging

For readers unfamiliar with Toss Securities: it’s one of the major brokerage APIs in Korea, and this bot trades against it using the Korean won. The part I cared about most was making sure a DB write failure could never block an actual trade. So every insert function catches sqlite3.OperationalError internally, logs a warning, and quietly moves on instead of propagating the exception. Same philosophy as the existing Telegram alert sender — a failed notification shouldn’t kill the trading logic.

def insert_trade(conn, trade_data: dict) -> None:
    try:
        cursor = conn.cursor()
        cursor.execute(
            "INSERT INTO trades (symbol, side, price, quantity, is_dry_run, strategy_name) "
            "VALUES (?, ?, ?, ?, ?, ?)",
            (trade_data["symbol"], trade_data["side"], trade_data["price"],
             trade_data["quantity"], trade_data["is_dry_run"], trade_data["strategy_name"])
        )
        conn.commit()
    except sqlite3.OperationalError as e:
        logger.warning(f"Trade logging failed, continuing execution: {e}")

The is_dry_run column separates real trades from simulated ones in the same table. A simulated buy of a masked ticker like Samsung Electronics (005930) gets stored with is_dry_run=1, a live buy with 0.

Testing: in-memory first, then real files

First pass: 12 tests against an in-memory database via sqlite3.connect(':memory:'), so nothing touches disk. These covered normal inserts, correct storage of is_dry_run true/false, decimal-precision prices for overseas tickers not getting truncated, and confirming DB errors never propagate as exceptions.

In-memory tests alone can miss file I/O issues though, so the second pass ran the same flow — init, write, read — against an actual .db file. Re-running the full suite gave 46 existing tests plus 12 new ones, 58 passing total.

The bug hunt: a column-index mistake in my own tests

The first run of test_정상_거래_기록 failed with an AssertionError. I assumed db.py itself was broken and spent a while staring at it — turned out the real cause was somewhere else entirely.

The test was pulling a row with cursor.execute("SELECT * FROM trades") and indexing into it manually. I’d assumed strategy_name sat at position 6 (row[5]), but it’s actually the 7th column (row[6]). So the test kept comparing against the wrong value entirely. Once I separated “implementation bug vs. test bug” and confirmed db.py was fine from the start, fixing the test itself took two minutes.

Having that habit — figure out which side the bug is actually on before diving into the implementation code — saved me from re-reading db.py for no reason.

What’s next

The db.py module works standalone, verified. But it’s not wired into auto_trader.py or toss_portfolio.py yet, so live trades don’t get logged automatically right now. Next post covers hooking execute_order() and send_notification() up to call insert_trade() and insert_alert() respectively. Once that’s done, Phase 3 wraps up, and Phase 4 (backtesting) will actually put this data to use.

Note: ticker symbols and amounts mentioned above are masked placeholders per project convention and don’t reflect actual holdings or balances.