SQLiteSQLite

-- WAL 모드 (Write-Ahead Logging): 동시 읽기 성능 향상
PRAGMA journal_mode=WAL;
PRAGMA synchronous=NORMAL;

-- JSON 지원 (SQLite 3.38+)
CREATE TABLE events (
  id   INTEGER PRIMARY KEY,
  data JSON
);
INSERT INTO events(data) VALUES ('{"type":"click","x":100}');

SELECT data->>'type' AS event_type,
       json_extract(data, '$.x') AS x_coord
FROM events;

-- FTS5: 전문 검색
CREATE VIRTUAL TABLE articles_fts USING fts5(
  title, content,
  tokenize="unicode61 remove_diacritics 1"
);
INSERT INTO articles_fts SELECT title, content FROM articles;

SELECT title, snippet(articles_fts, 1, '<b>', '</b>', '...', 20)
FROM articles_fts
WHERE articles_fts MATCH 'SQLite 데이터베이스'
ORDER BY rank;

-- GENERATED 컬럼
CREATE TABLE products (
  price     REAL,
  quantity  INTEGER,
  total     REAL GENERATED ALWAYS AS (price * quantity) STORED
);

import sqlite3
from contextlib import contextmanager

@contextmanager
def get_db(path: str):
    conn = sqlite3.connect(path)
    conn.row_factory = sqlite3.Row  # dict-like 접근
    conn.execute("PRAGMA journal_mode=WAL")
    conn.execute("PRAGMA foreign_keys=ON")
    try:
        yield conn
        conn.commit()
    except:
        conn.rollback()
        raise
    finally:
        conn.close()

with get_db("app.db") as db:
    db.execute("""
        CREATE TABLE IF NOT EXISTS users (
          id    INTEGER PRIMARY KEY,
          email TEXT UNIQUE NOT NULL,
          name  TEXT
        )
    """)
    db.execute("INSERT INTO users(email, name) VALUES (?,?)",
               ("user@example.com", "홍길동"))
    row = db.execute("SELECT * FROM users WHERE email=?",
                     ("user@example.com",)).fetchone()
    print(dict(row))  # {'id': 1, 'email': '...', 'name': '홍길동'}