파이썬 sqlite3 완벽 가이드: 로컬 DB 구축과 CRUD 쿼리

파이썬에서 별도 데이터베이스 서버 없이 로컬 데이터를 저장하려면 표준 라이브러리 sqlite3가 가장 간단합니다. sqlite3.connect()로 파일 DB를 열고, 테이블을 만든 뒤 INSERT·SELECT·UPDATE·DELETE 문을 실행하면 됩니다. 사용자 값은 SQL 문자열에 직접 붙이지 말고 반드시 ? 또는 이름 있는 플레이스홀더로 바인딩해야 합니다.

Python sqlite3 공식 문서 확인하기

SQLite가 적합한 경우

SQLite는 데이터베이스 전체를 하나의 파일로 관리하며 별도 서버 프로세스가 필요하지 않습니다. 개인용 자동화 도구, 데스크톱 앱, 수집 데이터 캐시, 소규모 웹 서비스의 프로토타입처럼 설치와 배포를 단순하게 유지해야 할 때 유용합니다.

상황	SQLite 적합성	판단 이유
개인용 로컬 프로그램	높음	서버 설치 없이 파일 하나로 관리
크롤링 결과 중복 방지	높음	키·인덱스로 빠른 존재 확인
소규모 API 프로토타입	높음	구조 검증과 개발이 간단함
다수 사용자의 동시 쓰기	검토 필요	쓰기 경합과 잠금 가능성
분산 서버·고가용성	낮음	전용 DB 서버가 더 적합

SQLite가 작고 간단하다고 해서 테스트용으로만 제한되는 것은 아니지만, 동시에 많은 쓰기가 발생하거나 여러 서버에서 같은 파일을 공유해야 한다면 PostgreSQL이나 MySQL 같은 서버형 데이터베이스를 검토해야 합니다.

DB 연결과 테이블 생성

sqlite3.connect("app.db")를 호출하면 파일이 없을 때 새 DB가 생성됩니다. 임시 테스트는 :memory:를 사용하면 디스크에 파일을 남기지 않고 메모리에서 실행할 수 있습니다.

import sqlite3

con = sqlite3.connect("app.db")
con.execute("""
CREATE TABLE IF NOT EXISTS tasks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'queued',
    created_at TEXT NOT NULL
)
""")
con.commit()
con.close()

연결 객체는 사용 후 닫아야 합니다. 트랜잭션용 with con:은 성공 시 커밋하고 예외 시 롤백하지만 연결 자체를 닫지는 않습니다. 연결 종료까지 보장하려면 try/finally 또는 contextlib.closing()을 함께 사용할 수 있습니다.

Create: 데이터 안전하게 추가하기

파이썬 변수 값을 SQL 문자열 포매팅으로 합치면 따옴표 오류와 SQL 인젝션 위험이 생깁니다. SQL 구조는 코드에 두고 값은 두 번째 인수로 전달하세요.

import sqlite3
from datetime import datetime, timezone

con = sqlite3.connect("app.db")

with con:
    con.execute(
        "INSERT INTO tasks(title, status, created_at) VALUES(?, ?, ?)",
        ("데이터 백업", "queued", datetime.now(timezone.utc).isoformat())
    )

con.close()

여러 행을 추가할 때는 반복문에서 execute()를 계속 호출하기보다 executemany()와 하나의 트랜잭션을 사용하면 코드가 간결하고 효율적입니다.

rows = [
    ("보고서 생성", "queued"),
    ("메일 발송", "queued"),
    ("결과 백업", "done"),
]

with con:
    con.executemany(
        "INSERT INTO tasks(title, status) VALUES(?, ?)",
        rows
    )

Read: 조건 조회와 결과 처리

fetchone()은 한 행 또는 None, fetchall()은 남은 모든 행을 목록으로 반환합니다. 결과가 매우 많다면 커서를 직접 순회하거나 일정 크기로 나누어 읽는 편이 메모리에 안전합니다.

con = sqlite3.connect("app.db")
con.row_factory = sqlite3.Row

cursor = con.execute(
    "SELECT id, title, status FROM tasks WHERE status = ? ORDER BY id",
    ("queued",)
)

for row in cursor:
    print(row["id"], row["title"], row["status"])

con.close()

sqlite3.Row는 튜플처럼 인덱스로도 읽을 수 있고 열 이름으로도 접근할 수 있습니다. SELECT 열 순서가 바뀌어도 의미가 분명해 유지보수가 쉬워집니다.

Update·Delete: 수정과 삭제

UPDATE와 DELETE는 WHERE 조건이 빠지면 전체 행에 적용됩니다. 실행 전 같은 WHERE 조건으로 SELECT하여 대상 수를 확인하고, 실행 후 rowcount를 기록하면 실수를 줄일 수 있습니다.

with con:
    cursor = con.execute(
        "UPDATE tasks SET status = ? WHERE id = ? AND status = ?",
        ("done", 3, "queued")
    )
    print("수정된 행:", cursor.rowcount)

with con:
    cursor = con.execute(
        "DELETE FROM tasks WHERE id = ?",
        (7,)
    )
    print("삭제된 행:", cursor.rowcount)

상태까지 조건에 포함하면 다른 프로세스가 이미 처리한 작업을 덮어쓰는 위험을 줄일 수 있습니다. 자동화 대기열이라면 WHERE id=? AND status='queued' 같은 조건부 변경이 특히 중요합니다.

트랜잭션과 예외 처리

여러 SQL 문이 하나의 업무 단위라면 모두 성공하거나 모두 취소되어야 합니다. 연결 객체를 컨텍스트 관리자로 사용하면 블록이 정상 종료될 때 커밋하고 예외가 발생할 때 롤백합니다.

import sqlite3

con = sqlite3.connect("app.db")

try:
    with con:
        con.execute(
            "UPDATE accounts SET balance = balance - ? WHERE id = ?",
            (1000, 1)
        )
        con.execute(
            "UPDATE accounts SET balance = balance + ? WHERE id = ?",
            (1000, 2)
        )
except sqlite3.IntegrityError as exc:
    print("무결성 오류:", exc)
except sqlite3.OperationalError as exc:
    print("DB 작업 오류:", exc)
finally:
    con.close()

IntegrityError는 UNIQUE·FOREIGN KEY 같은 무결성 제약 위반, OperationalError는 파일 경로·잠금·SQL 실행 환경과 관련된 문제에서 발생할 수 있습니다. 모든 오류를 무조건 같은 방식으로 재시도하지 말고 원인별 대응을 나누세요.

스키마와 인덱스 설계

SQLite는 유연한 타입 시스템을 사용하지만 컬럼 의미를 명확히 설계하는 편이 좋습니다. 기본키, NOT NULL, UNIQUE, CHECK 제약으로 잘못된 데이터가 들어오는 것을 데이터베이스 단계에서도 막을 수 있습니다.

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE,
    age INTEGER CHECK(age >= 0),
    active INTEGER NOT NULL DEFAULT 1
);

CREATE INDEX idx_tasks_status_id
ON tasks(status, id);

인덱스는 WHERE·JOIN·ORDER BY에서 자주 사용하는 열의 조회를 빠르게 할 수 있지만, 저장 공간을 사용하고 INSERT·UPDATE 비용을 늘립니다. 모든 열에 만들기보다 실제 쿼리를 기준으로 선택하세요.

날짜·시간을 TEXT로 저장한다면 정렬 가능한 ISO 8601 형식과 UTC 기준 여부를 일관되게 정해야 합니다. Python 3.12부터 sqlite3의 기본 날짜·시간 어댑터와 변환기는 deprecated 상태이므로 애플리케이션 요구에 맞는 변환 방식을 명시적으로 설계하는 것이 좋습니다.

백업과 운영 주의점

• 백업: 실행 중인 DB 파일을 단순 복사하기보다 SQLite 백업 API나 안전한 종료 시점을 이용합니다.
• 잠금: 쓰기 트랜잭션을 짧게 유지하고 네트워크 파일시스템 공유를 신중히 검토합니다.
• 외래키: 연결마다 필요한 설정이 적용되는지 확인합니다.
• 민감정보: SQLite 파일은 암호화 저장소가 아니므로 접근권한과 별도 보호 대책이 필요합니다.
• 마이그레이션: 운영 데이터가 생긴 뒤에는 CREATE TABLE만 수정하지 말고 버전별 변경 절차를 기록합니다.

데이터를 CSV나 엑셀로 내보낼 때는 모든 행을 한꺼번에 메모리에 올리지 말고 커서를 나누어 읽는 방법을 검토하세요.

실수 방지 체크리스트

• DB 파일의 절대 경로와 실행 위치를 확인합니다.
• 사용자 값은 플레이스홀더로 바인딩합니다.
• INSERT·UPDATE·DELETE 뒤 커밋 여부를 확인합니다.
• 여러 변경은 하나의 트랜잭션으로 묶습니다.
• UPDATE·DELETE 전에 대상 행을 SELECT로 확인합니다.
• 연결을 사용한 뒤 명시적으로 닫습니다.
• 날짜·시간 저장 형식과 UTC 기준을 통일합니다.
• 정기 백업과 복원 테스트를 준비합니다.
• 동시 쓰기가 많아지면 서버형 DB 이전을 검토합니다.

자주 묻는 질문

1. sqlite3는 pip로 설치해야 하나요?

일반적인 CPython 배포판에는 표준 라이브러리로 포함됩니다. 다만 일부 특수 배포판에서는 선택 모듈로 빠질 수 있습니다.

2. DB 파일은 어디에 생성되나요?

상대 경로를 사용하면 프로그램의 현재 작업 디렉터리를 기준으로 생성됩니다. 혼동을 피하려면 애플리케이션에서 절대 경로를 구성하세요.

3. 메모리 DB는 어떻게 만드나요?

sqlite3.connect(":memory:")를 사용하며 연결이 닫히면 데이터가 사라집니다.

4. cursor를 반드시 만들어야 하나요?

연결 객체의 execute() 같은 단축 메서드를 사용할 수 있어 간단한 작업은 별도 cursor 변수가 없어도 됩니다.

5. commit을 하지 않으면 어떻게 되나요?

트랜잭션의 변경 내용이 영구 저장되지 않고 연결 종료나 롤백 과정에서 사라질 수 있습니다.

6. SQL 인젝션은 어떻게 막나요?

SQL 구조와 값을 분리하고 ? 또는 이름 있는 플레이스홀더로 값을 전달하세요.

7. 조회 결과를 딕셔너리처럼 받을 수 있나요?

연결의 row_factory를 sqlite3.Row로 설정하면 열 이름으로 접근할 수 있습니다.

8. database is locked 오류는 왜 발생하나요?

다른 연결이 쓰기 트랜잭션을 오래 유지하거나 동시 쓰기가 겹칠 때 발생할 수 있습니다. 트랜잭션을 짧게 유지하고 동시성 구조를 점검하세요.

9. SQLite를 웹 서비스에 사용해도 되나요?

읽기 중심의 소규모 서비스에는 가능하지만 동시 쓰기량, 백업, 확장 요구를 검토해야 합니다.

10. PostgreSQL로 나중에 이전할 수 있나요?

가능하지만 타입, SQL 문법, 자동 증가, 트랜잭션과 동시성 차이가 있으므로 DB 접근 코드를 분리하고 마이그레이션을 계획해야 합니다.

공식 출처와 확인일

• Python 공식 문서: sqlite3 — 확인일 2026년 8월 31일
• SQLite 공식 문서: SQL Language — 확인일 2026년 8월 31일
• Python DB-API 2.0 명세 PEP 249 — 확인일 2026년 8월 31일