Ir al contenido

Modelado Relacional y Conceptos Clave

Análisis conceptual del uso de cursores y transacciones ACID en el motor sqlite3 de Python, con ejemplo práctico de modelado relacional uno-a-muchos.

0lecturas

Para interactuar de forma eficiente y segura con SQLite desde lenguajes de programación como Python, es imprescindible asimilar dos principios arquitectónicos fundamentales: los cursores de base de datos y las transacciones transaccionales.


4.1. Conceitos Fundamentais: Cursores e Transações

Sección titulada «4.1. Conceptos Fundamentales: Cursores y Transacciones»

En la biblioteca sqlite3 de Python, la ejecución de sentencias no se realiza de forma directa sobre la conexión de la base de datos (conn). En su lugar, se requiere instanciar una estructura de control intermedia denominada Cursor (cursor).

  • Definición Técnica: Un cursor actúa como un puntero de memoria o agente de control que ejecuta las sentencias SQL sobre el archivo físico en disco y gestiona el conjunto de registros devueltos.
  • Funcionamento Operacional:
    1. Ejecución Segura: Recibe y procesa la consulta: cursor.execute(sql).
    2. Extracción Controlada: Almacena temporalmente los datos resultantes de las lecturas, permitiendo recuperarlos de forma granular utilizando cursor.fetchone() (extrae una sola fila) o cursor.fetchall() (recupera la totalidad de los registros resultantes).
# 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()

Uma transação é uma secuencia lógica de uma ou mais instruções SQL que se executam como uma única unidade de trabalho indivisível. Este comportamento responde ao princípio de Atomicidade do estándar ACID: “o se completam todas as operações com sucesso, ou se revierte a totalidade do lote ao estado inicial”.

Analisemos a clássica transação financeira onde transferimos saldo entre dois registros:

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;

Se o servidor sofresse uma perda repentina de energia após finalizar a operação 1, mas antes da operação 2, o saldo desapareceria. As transações mitigam esse risco:

  • BEGIN TRANSACTION: Abre uma zona de trabalho aislada em disco.
  • COMMIT: Faz permanentes todas as insercões ou modificações realizadas desde o início da transação.
  • ROLLBACK: Cancela as modificações realizadas e devolve a base de dados a seu estado original exato previo ao início do bloque.

Em Python, o módulo sqlite3 inicializa transações de forma implícita ao executar sentenças de escritura (INSERT, UPDATE, DELETE). Para confirmar os alterações físicas no arquivo .db, deve chamar explícitamente a conn.commit().


4.2. Caso Prático: Gestão de Projetos e Tarefas

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

A continuacao, implementaremos um modelo de base de dados relacional de tipo Uno-a-Muchos (1:N) utilizando restricoes de chave foránea (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
}

Passo 1: Declaração de Esquemas Lógicos (DDL)

Sección titulada «Paso 1: Declaración de Esquemas Lógicos (DDL)»

Creamos a tabela “padre” (portel_projetos) e posteriormente a tabela “hija” (portel_tareas) vinculándolas mediante chave foránea:

-- 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]\r\nIntegridade Referencial: A definição ON DELETE RESTRICT informa ao motor do banco de dados que bloqueie a exclusão de qualquer projeto em portel_proyectos se ainda existirem tarefas órfãs vinculadas em portel_tareas.


Passo 2: Inserção de Registros de Teste (DML)

Sección titulada «Paso 2: Inserción de Registros de Prueba (DML)»

Provisionamos dois projetos e atribuímos tarefas vinculando seus respectivos IDs relacionais:

-- 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);

Passo 3: Consultas Relacionais Avançadas (DQL - JOIN)

Sección titulada «Paso 3: Consultas Relacionales Avanzadas (DQL - JOIN)»

Para recuperar as tarefas consolidadas junto ao nome do projeto ao qual pertencem, implementamos um acoplamento mediante 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;
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

Para marcar o marco de design como concluído de forma atômica:

UPDATE portel_tareas
SET completada = 1
WHERE id = 1;

🏁 Checklist de Modelagem Relacional Segura

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

Garanta a consistência lógica dos seus bancos de dados nos laboratórios da Portelcore seguindo este checklist de controle:

  • ✓ Activacao de Chaves Foráneas: Asegurar que o script de Python invoque a directiva de verificação ao abrir a conexao: conn.execute("PRAGMA foreign_keys = ON;") (desativada por defeito em SQLite).
  • ✓ Mapeo de Chaves Foráneas: Verificar que todas as relacoes logicas entre tablas de dados contem restricoes declarativas de tipo FOREIGN KEY.
  • ✓ Confirmacao Transaccional (Commit): Validar que todas as operacoes de escritura que alteram o estado fisico do arquivo .db se confirmem explícitamente mediante conn.commit().
  • ✓ Gestao em Cascada: Planificar correctamente as directivas ON DELETE (RESTRICT ou CASCADE) ao criar esquemas para evitar registros huérfanos ou borrados accidentais de dados em cascada.