고유 제약과 원자적 적재

고유 키를 DB에도 선언하는 이유

브라우저 분류기가 키 중복을 발견해도 DB에 제약이 없다면 다른 입력 경로에서 중복 행이 들어갈 수 있습니다. 분석 담당자가 Python으로 검사하고 동료가 별도 SQL로 적재하는 상황에서는 검사 코드가 저장소의 유일한 문지기가 아닙니다. 저장 테이블 자체에 지역·날짜 고유성을 선언하면 관측 단위가 실제 데이터 구조에 남습니다. 이번 레슨에서는 애플리케이션에서 키를 판단하는 것과 DB가 고유성을 지키는 일을 연결합니다. 데이터 계약을 말로만 적지 않고 SQL과 테스트로 표현합니다.

observations 테이블은 region TEXT NOT NULL, date TEXT NOT NULL, total_vehicles INTEGER, rain_mm REAL 열을 갖습니다. PRIMARY KEY(region,date)는 두 열의 조합을 고유하게 만듭니다. 같은 날짜의 두 지역은 허용하고 같은 지역의 두 날짜도 허용합니다. 지역·날짜에 각각 별도 UNIQUE를 걸면 한 지역이 여러 날짜에 등장할 수 없게 되어 일별 표의 의미를 깨뜨립니다. 복합키는 두 컬럼을 동시에 비교한다는 뜻입니다. 키 열의 결측을 허용하지 않는 이유도 어느 관측인지 식별할 수 있어야 하기 때문입니다.

SQLite 일반 테이블의 자료형 선언만으로 모든 잘못된 값이 차단된다고 생각하지 않습니다. 이번 저장 함수는 앞 모듈의 errors를 먼저 호출해 날짜, 불리언 제외 정수, 비음수 유한 강수량을 검사합니다. DB 고유 제약은 중복 저장을 막고 스키마 검사는 값의 의미를 지킵니다. 고유키를 통과했다고 날짜 문자열이 실제 달력 날짜라는 보장이 생기지는 않습니다. 두 검사를 서로 보완하는 층으로 두고 어떤 오류가 어디서 발생하는지 테스트에 드러냅니다.

재입력과 정정을 한 문장으로 처리합니다

INSERT만 사용하면 처음 적재는 성공하지만 같은 키를 재입력할 때 UNIQUE constraint failed가 납니다. 매일 같은 파일을 처리하는 작업에서는 이 충돌이 예상 가능한 상황입니다. INSERT OR IGNORE는 오류를 없애지만 정정된 통행량도 무시하므로 최신 값을 반영하지 못합니다. 이번 계약은 없는 키를 추가하고 존재하는 키를 새 값으로 교체합니다. 이를 INSERT 뒤의 ON CONFLICT(region,date) DO UPDATE로 표현합니다. 충돌 대상은 앞에서 선언한 복합 고유키와 정확하게 맞춥니다.

갱신식의 excluded.total_vehicles는 이번 INSERT가 넣으려던 통행량을 뜻합니다. observations.total_vehicles는 기존 테이블 값입니다. 절대 합계인 통행량은 excluded 값으로 대입합니다. 기존 값과 excluded 값을 더하는 식은 두 번째 실행 때 값이 커지므로 같은 입력을 다시 실행해도 같은 결과여야 한다는 멱등성 요구에 어긋납니다. 강수량도 새 값으로 교체하며 incoming이 None이면 SQL NULL로 갱신합니다. 결측을 기존 값 유지로 해석하는 계약이 필요하다면 별도 정책을 작성해야 합니다.

UPSERT는 모든 제약 오류를 무시하는 명령이 아닙니다. 이 문장은 고유키 충돌에 대한 동작을 지정합니다. NOT NULL 등 다른 제약 위반이나 SQL 구문 오류는 실패할 수 있습니다. syntax error near ON이 보이면 문장 구성과 사용 SQLite 기능 지원을 확인합니다. ON CONFLICT clause does not match any PRIMARY KEY or UNIQUE constraint는 충돌 대상에 대응하는 고유 제약이 없다는 신호입니다. Python 버전만 확인하지 않고 sqlite3.sqlite_version_info도 확인하는 습관을 갖습니다.

SQL 값은 문자열 조립 대신 물음표 매개변수로 전달합니다. region에 따옴표가 있어도 문자열을 직접 이스케이프하지 않고 execute의 두 번째 인자로 넘깁니다. 컬럼 이름과 SQL 문장은 고정하고 데이터만 바인딩합니다. tuple(row[k] for k in 필드목록)의 순서는 INSERT에 적은 컬럼 순서와 같습니다. 딕셔너리의 우연한 삽입 순서에 의존하면 통행량과 강수량이 바뀌어 들어갈 수 있습니다. 작은 표본에서는 오류가 없어 보여도 값을 조회해 확인하면 잘못된 저장을 발견할 수 있습니다.

누적과 전체 교체의 차이

이번 merge 함수는 들어오지 않은 키를 지우지 않습니다. A 지역의 정정 한 행만 들어오면 B 지역의 관측은 그대로 남습니다. 앞 모듈의 load는 검증된 전체 스냅샷으로 교체하므로 DELETE 뒤 INSERT를 실행했습니다. 그 코드를 증분 입력에 그대로 사용하면 이번 파일에 없는 날짜를 모두 삭제합니다. 저장 전에 입력이 전체 목록인지 변경분인지 정의해야 합니다. 여기서는 증분 병합이며 삭제 이벤트는 지원하지 않습니다. 삭제가 필요하면 특정 키와 삭제 사유를 갖춘 별도 계약으로 확장합니다.

빈 배열은 새 변경이 없다는 의미이며 기존 저장 행을 유지합니다. 빈 전체 스냅샷은 전체 데이터가 없다는 의미일 수 있으므로 같은 배열이라도 저장 계약에 따라 결과가 달라집니다. 실습의 빈 배치 테스트는 기존 A 행을 먼저 적재하고 빈 입력 후 그 행이 남는지 검사합니다. 단순히 새 빈 DB에서 0행이 나오는지만 보면 잘못된 DELETE 구현도 통과합니다. 테스트 준비 상태를 어떻게 만드는지가 검증의 품질을 결정합니다.

고유키가 있어도 한 배치에 같은 키가 여러 번 나오면 UPSERT가 순서대로 적용될 수 있습니다. 마지막 값을 채택하는 것이 이번 미션의 승인 규칙은 아니므로 merge는 쓰기 전에 키 목록의 길이와 set의 길이를 비교해 중복을 거절합니다. 같은 값 중복도 모호한 공급 형식으로 취급합니다. 배치 간 같은 키의 재처리는 허용하면서 배치 내부 중복은 거절한다는 경계를 명시합니다. 앞 단계 transform의 KEY_CONFLICT도 유지하여 충돌 데이터가 DB로 넘어오는 것을 막습니다.

짧은 배치에 트랜잭션을 적용합니다

고유키 UPSERT 한 문장은 키 선택과 갱신을 DB에서 처리합니다. 하지만 배치에 여러 행이 있으면 각 행의 성공을 묶는 별도 경계가 필요합니다. 예제 구현은 자동 트랜잭션 시작을 끄는 isolation_level=None을 지정한 뒤 BEGIN IMMEDIATE와 COMMIT을 직접 실행합니다. 이 연결 옵션은 이후 Python의 다른 기본값에 기대지 않고 예제의 시작 시점을 드러냅니다. 수집과 변환은 DB 쓰기 전에 마치며 저장 중 네트워크 요청을 하지 않습니다. 배치 실패와 롤백의 상세 검증은 다음 레슨에서 다룹니다.

조회에는 ORDER BY region,date를 붙입니다. 기본키가 있다고 모든 SELECT의 출력 순서가 보장되는 것은 아닙니다. 같은 입력을 두 번 처리한 후 정렬된 키·값을 비교하고, 정정값을 한 행만 넣었을 때 해당 키만 바뀌는지 확인합니다. SUM은 NULL을 합산에서 제외하므로 합계와 함께 null_vehicles를 기록합니다. 이번 감사 함수의 sum_vehicles는 유효 통행량을 더한 값이며 모든 값이 NULL인 경우도 비교 편의를 위해 0으로 표현합니다. 이 수치를 전체 관측이 모두 영점이라는 뜻으로 읽지 않습니다.

실습의 수정 범위를 지킵니다

starter의 merge.py는 일반 INSERT를 사용하여 첫 적재 등 일부 검사는 통과하고 재입력·정정 검사가 실패합니다. 테스트를 삭제하지 말고 저장 문장을 계약에 맞게 고칩니다. 같은 입력 두 번, 수정 한 키, 새 날짜, 빈 변경분, NULL·영점 보존, 배치 중복 거절, 잘못된 범위 차단, DB 고유 제약 검사가 모두 통과하면 완료입니다. 실습의 데이터 값은 앞 프로젝트 도메인을 유지하며 SQL 방언 전반의 차이는 더 읽기로 보냅니다. SQLite에서 검증한 문장을 다른 DB에서 그대로 실행할 수 있다고 일반화하지 않습니다.

문법 근거: SQLite UPSERT 공식 문서에서 고유 제약 충돌 대상과 excluded의 의미를 확인합니다.

import sqlite3
from contextlib import closing
with closing(sqlite3.connect(':memory:')) as db:
    db.execute('CREATE TABLE observations(region TEXT NOT NULL,date TEXT NOT NULL,n INTEGER,PRIMARY KEY(region,date))')
    db.execute('INSERT INTO observations VALUES(?,?,?)',('A','2026-09-01',100))
    try:
        db.execute('INSERT INTO observations VALUES(?,?,?)',('A','2026-09-01',105))
    except sqlite3.IntegrityError as e:
        print(str(e))

UNIQUE constraint failed: observations.region, observations.date

import sqlite3
from contextlib import closing
print('upsert_supported',sqlite3.sqlite_version_info >= (3,24,0))
with closing(sqlite3.connect(':memory:')) as db:
    db.execute('CREATE TABLE observations(region TEXT NOT NULL,date TEXT NOT NULL,n INTEGER,PRIMARY KEY(region,date))')
    sql='INSERT INTO observations VALUES(?,?,?) ON CONFLICT(region,date) DO UPDATE SET n=excluded.n'
    for n in [100,100,105]:
        db.execute(sql,('A','2026-09-01',n))
        print(db.execute('SELECT COUNT(*),SUM(n) FROM observations').fetchone())

upsert_supported True
(1, 100)
(1, 100)
(1, 105)

import sqlite3
from contextlib import closing
with closing(sqlite3.connect(':memory:')) as db:
    db.execute('CREATE TABLE traffic(n INTEGER)')
    db.executemany('INSERT INTO traffic VALUES(?)',[(None,),(0,),(100,)])
    print(db.execute('SELECT COUNT(*),COUNT(n),SUM(n) FROM traffic').fetchone())

(3, 2, 100)

python3 -m unittest discover -s tests -v

python3 -m unittest discover -s tests -v