Lite Sqlite

Basic Database Operations

from sqlite_connector import SQLiteDB
Create database (auto-wal mode enabled)
db = SQLiteDB("agent_data.db")
Create table
db.create_table("memos", {
    "id": "INTEGER PRIMARY KEY AUTOINCREMENT",
    "title": "TEXT NOT NULL",
    "content": "TEXT",
    "created_at": "TEXT DEFAULT CURRENT_TIMESTAMP",
    "tags": "TEXT"
})
Insert data
db.insert("memos", [title="First memo", content="Hello world", tags="test"])
Query data
results = db.query("SELECT * FROM memos WHERE tags = ?", ("test",))
Update data
db.update("memos", "id = ?", [content="Updated content"], (1,))
Delete data
db.delete("memos", "id = ?", (1,))
Close connection
db.close()

In-Memory Database (Fastest)

# Fastest mode - RAM only, no disk I/O
db = SQLiteDB(":memory:")
Perfect for temporary operations
db.create_table("temp", {...})
Data persists only during session
Use for caching, computations, temporary storage

Database Locked Error

# Use WAL mode for concurrent access
conn.execute("PRAGMA journal_mode=WAL")
Or use connection pool
pool = ConnectionPool("db.db", timeout=5.0)

Slow Queries

# Check query plan
plan = conn.execute("EXPLAIN QUERY PLAN SELECT * FROM ...").fetchall()
Add indexes
db.create_index("table", "column")
Use ANALYZE
conn.execute("ANALYZE")

Large Database Size

# Check size info
size_info = conn.execute("PRAGMA page_count, page_size").fetchone()
print(f"Size: {(page_count * page_size) / (1024*1024):.2f} MB")
Vacuum to reclaim space
db.vacuum()