SQLite 실전 가이드 — CLI · 스키마 · 백업 · Python 연동

# 설치
sudo apt install -y sqlite3   # Ubuntu
brew install sqlite           # macOS

# 데이터베이스 열기 (없으면 생성)
sqlite3 myapp.db

# 도움말
sqlite> .help

.tables              테이블 목록
.schema users        테이블 스키마 확인
.mode column         열 정렬 출력
.headers on          헤더 표시
.width 20 10 30      열 너비 설정
.output result.txt   출력을 파일로
.read script.sql     SQL 파일 실행
.quit                종료

-- 테이블 생성
CREATE TABLE IF NOT EXISTS users (
  id        INTEGER PRIMARY KEY AUTOINCREMENT,
  name      TEXT    NOT NULL,
  email     TEXT    NOT NULL UNIQUE,
  age       INTEGER CHECK(age >= 0),
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS posts (
  id        INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id   INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  title     TEXT    NOT NULL,
  body      TEXT,
  published INTEGER DEFAULT 0
);

-- 삽입
INSERT INTO users (name, email, age) VALUES
  ('Alice', '[email protected]', 30),
  ('Bob', '[email protected]', 25);

-- 조회
SELECT * FROM users WHERE age > 20 ORDER BY name;

SELECT u.name, COUNT(p.id) AS post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id
ORDER BY post_count DESC;

-- 수정
UPDATE users SET age = 31 WHERE email = '[email protected]';

-- 삭제
DELETE FROM users WHERE id = 2;

-- 인덱스 생성
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_posts_user ON posts(user_id, published);

-- 업데이트 시간 자동 갱신 트리거
ALTER TABLE users ADD COLUMN updated_at DATETIME;

CREATE TRIGGER users_updated_at
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
  UPDATE users SET updated_at = CURRENT_TIMESTAMP WHERE id = OLD.id;
END;

-- 인덱스 목록
.indexes users

-- 실행 계획
EXPLAIN QUERY PLAN
SELECT * FROM posts WHERE user_id = 1 AND published = 1;

# 텍스트 덤프 (SQL 형식)
sqlite3 myapp.db .dump > backup_$(date +%Y%m%d).sql

# 복원
sqlite3 restored.db < backup_20250527.sql

# 바이너리 복사 (DB 잠금 상태 확인 후)
sqlite3 myapp.db "VACUUM INTO 'backup.db'"

# WAL 모드에서 안전한 온라인 백업
sqlite3 myapp.db ".backup backup.db"

import sqlite3
from contextlib import contextmanager

@contextmanager
def get_db(path="myapp.db"):
    conn = sqlite3.connect(path)
    conn.row_factory = sqlite3.Row  # 딕셔너리처럼 접근
    conn.execute("PRAGMA foreign_keys = ON")
    conn.execute("PRAGMA journal_mode = WAL")
    try:
        yield conn
        conn.commit()
    except Exception:
        conn.rollback()
        raise
    finally:
        conn.close()

# 사용 예시
with get_db() as db:
    # 삽입
    db.execute(
        "INSERT INTO users (name, email, age) VALUES (?, ?, ?)",
        ("Carol", "[email protected]", 35)
    )

    # 조회
    rows = db.execute("SELECT * FROM users WHERE age > ?", (20,)).fetchall()
    for row in rows:
        print(dict(row))

    # executemany (배치 삽입)
    data = [
        ("Dave", "[email protected]", 28),
        ("Eve", "[email protected]", 32),
    ]
    db.executemany(
        "INSERT OR IGNORE INTO users (name, email, age) VALUES (?, ?, ?)",
        data
    )