Ir al contenido

관계형 모델링 및 핵심 개념

Python의 sqlite3 엔진에서 커서 및 ACID 트랜잭션 개념 분석, 일대다 관계형 모델링 실습 예제

0조회

Python과 같은 프로그래밍 언어에서 SQLite와 효율적이고 안전하게 상호작용하려면, 데이터베이스 커서와 트랜잭셔널 트랜잭션이라는 두 가지 기초 아키텍처 원칙을 숙지하는 것이 필수적입니다.


Python의 sqlite3 라이브러리에서, 명령문 실행은 데이터베이스 연결(conn)에 직접 이루어지지 않습니다. 대신, 중간 제어 구조인 커서 (cursor)를 인스턴스화해야 합니다.

  • 기술적 정의: 커서는 물리적 디스크 파일에서 SQL 명령문을 실행하고 반환된 레코드 집합을 관리하는 메모리 포인터 또는 제어 에이전트 역할을 합니다.
  • 운영 방식:
    1. 안전한 실행: cursor.execute(sql) 쿼리를 수신하고 처리합니다.
    2. 제어된 추출: 읽기에서 생성된 데이터를 일시적으로 저장하여 cursor.fetchone()(단일 행 추출) 또는 cursor.fetchall()(모든 결과 레코드 검색)을 사용하여 세부적으로 검색할 수 있습니다.
# Conexión al motor de base de datos local
conn = obtener_conexion(db_name)
# Instanciación del puntero o cursor
cursor = conn.cursor()
# Ejecución de la sentencia relacional
cursor.execute("SELECT nombre, email FROM portel_usuarios;")
# Recuperación de resultados en memoria del proceso
resultados = cursor.fetchall()

트랜잭션은 불가분한 단일 작업 단위로 실행되는 하나 이상의 SQL 명령문의 논리적 시퀀스입니다. 이 동작은 ACID 표준의 원자성 원칙에 따라 작동합니다: “모든 작업이 성공적으로 완료되거나, 전체 레코드 세트가 초기 상태로 롤백됩니다”.

다음은 두 레코드 간 잔액 이전을 포함하는 전형적인 트랜잭션을 분석해 봅시다:

BEGIN TRANSACTION;
-- 1. Restar importe del emisor
UPDATE portel_cuentas SET saldo = saldo - 100 WHERE id = 'A';
-- 2. Sumar importe al receptor
UPDATE portel_cuentas SET saldo = saldo + 100 WHERE id = 'B';
COMMIT;

서버가 작업 1을 완료한 후 작업 2 전에 갑작스러운 전원 손실을 경험하면 잔액이 사라집니다. 트랜잭션은 이러한 위험을 완화합니다:

  • BEGIN TRANSACTION: 디스크에 고립된 작업 영역을 엽니다.
  • COMMIT: 트랜잭션 시작 이후에 수행된 모든 삽입 또는 수정을 영구히 저장합니다.
  • ROLLBACK: 실행한 변경 사항을 취소하고, 블록 시작 전의 정확한 원래 상태로 데이터베이스를 복원합니다.

파이썬에서 sqlite3 모듈은 쓰기 명령문(INSERT, UPDATE, DELETE)을 실행할 때 암시적으로 트랜잭션을 초기화합니다. 파일 .db에 물리적으로 변경 사항을 확인하려면 conn.commit()를 명시적으로 호출해야 합니다.


4.2. 실전 사례: 프로젝트 및 작업 관리

Sección titulada «4.2. Caso Práctico: Gestión de Proyectos y Tareas»

다음으로, 일대다(1:N) 유형의 관계형 데이터베이스 모델을 외래 키 제약 조건(FOREIGN KEY)을 사용하여 구현할 것입니다.

erDiagram
PORTEL_PROYECTOS ||--o{ PORTEL_TAREAS : contiene
PORTEL_PROYECTOS {
int id PK
string nombre_proyecto
string fecha_inicio
}
PORTEL_TAREAS {
int id PK
string descripcion
int completada
int id_proyecto FK
}

“부모” 테이블(portel_proyectos)을 생성하고, 이후 “자식” 테이블(portel_tareas)을 외래 키로 연결하여 생성합니다:

-- Estructura Principal
CREATE TABLE portel_proyectos (
id INTEGER PRIMARY KEY AUTOINCREMENT,
nombre_proyecto TEXT NOT NULL,
fecha_inicio TEXT DEFAULT (date('now'))
);
-- Estructura Relacionada
CREATE TABLE portel_tareas (
id INTEGER PRIMARY KEY AUTOINCREMENT,
descripcion TEXT NOT NULL,
completada INTEGER DEFAULT 0, -- 0 representa falso, 1 verdadero
id_proyecto INTEGER,
FOREIGN KEY (id_proyecto) REFERENCES portel_proyectos (id) ON DELETE RESTRICT
);

[!TIP] 참조 무결성: ON DELETE RESTRICT 정의에 따라 portel_tareas에 고아 작업(orphaned tasks)이 남아 있는 경우 portel_proyectos의 프로젝트 삭제를 데이터베이스 엔진이 차단합니다.


두 프로젝트를 준비하고, 해당 관계형 ID를 연결하여 작업을 할당합니다:

-- Inserción de proyectos
INSERT INTO portel_proyectos (nombre_proyecto) VALUES ('Hacer portal de documentación');
INSERT INTO portel_proyectos (nombre_proyecto) VALUES ('Migrar base de datos local');
-- Inserción de tareas vinculadas (Proyecto ID 1 y Proyecto ID 2)
INSERT INTO portel_tareas (descripcion, id_proyecto) VALUES ('Diseñar interfaz premium', 1);
INSERT INTO portel_tareas (descripcion, id_proyecto) VALUES ('Sanear datos de prueba', 1);
INSERT INTO portel_tareas (descripcion, id_proyecto) VALUES ('Exportar esquemas SQL', 2);

소속된 프로젝트 이름과 통합된 작업을 검색하기 위해 JOIN를 통해 결합을 구현합니다:

SELECT
portel_proyectos.nombre_proyecto,
portel_tareas.descripcion,
portel_tareas.completada
FROM portel_proyectos
JOIN portel_tareas ON portel_proyectos.id = portel_tareas.id_proyecto;

예상되는 형식화된 출력:

Sección titulada «Salida Formateada Esperada:»
Hacer portal de documentación | Diseñar interfaz premium | 0
Hacer portal de documentación | Sanear datos de prueba | 0
Migrar base de datos local | Exportar esquemas SQL | 0

디자인 마일스톤을 원자적으로 완료로 표시하기 위해:

UPDATE portel_tareas
SET completada = 1
WHERE id = 1;

🏁 안전한 관계 모델링 체크리스트

Sección titulada «🏁 Checklist de Modelado Relacional Seguro»

Portelcore 실험실에서 데이터베이스의 논리적 일관성을 보장하기 위해 이 체크리스트를 따르세요:

  • ✓ 외래 키 활성화: Python 스크립트가 연결을 열 때 검증 지시를 호출하도록 보장하세요: conn.execute("PRAGMA foreign_keys = ON;") (기본적으로 SQLite에서 비활성화됨).
  • ✓ 외래 키 매핑: 데이터 테이블 간의 모든 논리적 관계가 FOREIGN KEY 유형의 선언적 제약 조건을 포함하는지 확인하세요.
  • ✓ 거래 확인 (Commit): .db 파일의 물리적 상태를 변경하는 모든 쓰기 작업이 conn.commit()를 통해 명시적으로 확인되었는지 검증하세요.
  • ✓ 카스케이드 관리: ON DELETE (RESTRICT 또는 CASCADE) 지시문을 올바르게 계획하여 빈 키 또는 의도치 않은 데이터 삭제를 방지하세요.