database-sqlite

Database Best Practices

Connection Management

ALWAYS use the db_connection() context manager:

from src.data.db_connection import db_connection

with db_connection() as conn:
    c = conn.cursor()
    c.execute("SELECT * FROM trades")
    # Commit happens automatically on success

• NEVER call conn.commit() manually.
• Deadlock Prevention: When calling write functions (like execute_trade) from within an existing transaction, MUST pass the active cursor.

Migration System

The migration system tracks versions in the schema_version table.