Creación de Tablas en SQLite con Python | Capítulo 5

La definición del esquema en SQLite presenta una particularidad arquitectónica única: el motor utiliza un sistema de Afinidad de Tipos (Type Affinity) en lugar de tipos rígidos tradicionales. Comprender cómo SQLite parsea las declaraciones DDL (Data Definition Language) es crítico para evitar comportamientos inesperados en la capa de la aplicación Python.

5.1. Afinidad de Columnas y Declaración de Tipos

Cuando ejecutas un CREATE TABLE, SQLite no impone el tipo exacto declarado, sino que asigna una “afinidad” basada en subcadenas presentes en la definición.

• Afinidad INTEGER: Se aplica si el tipo contiene la cadena “INT” (ej. INT, INTEGER, TINYINT).
• Afinidad TEXT: Se aplica si contiene “CHAR”, “CLOB” o “TEXT” (ej. VARCHAR(255) se ignora el límite de longitud y se trata como TEXT ilimitado).
• Afinidad REAL: Se aplica si contiene “REAL”, “FLOA” o “DOUB”.
• Afinidad NUMERIC: Se aplica a tipos como DECIMAL o BOOLEAN. Intenta guardar el dato como entero o flotante; si no puede, lo guarda como texto.
• Afinidad BLOB: Si no se especifica tipo o no coincide con los anteriores.

Nota de Arquitectura: Debido a esta flexibilidad histórica, declarar una columna como VARCHAR(50) en SQLite no truncará una cadena de 100 caracteres insertada desde Python. La validación de longitud máxima debe implementarse a nivel de lógica de negocio o mediante restricciones CHECK en el esquema.

5.2. Modo Estricto (STRICT Tables)

Para proyectos nuevos con SQLite >= 3.37.0, el estándar de la industria es forzar el tipado rígido utilizando la cláusula STRICT. Esto alinea el comportamiento de SQLite con motores como PostgreSQL, rechazando silenciosamente la conversión de tipos implícita y previniendo la corrupción lógica de datos.

En tablas STRICT, solo se permiten estos tipos: INT, INTEGER, REAL, TEXT, BLOB, y la palabra clave ANY.

5.3. Ejecución de DDL en Python

Las sentencias DDL (como CREATE TABLE) no pueden ser parametrizadas en la especificación DB-API 2.0. No puedes usar el marcador ? para inyectar nombres de tablas o columnas. Por seguridad y mantenibilidad, el esquema debe definirse como literales de cadena estáticos.

Para inicializar esquemas complejos con múltiples tablas y relaciones, el método Cursor.executescript() es significativamente más eficiente que múltiples llamadas a execute(), ya que procesa un bloque SQL completo en una sola operación C.

import sqlite3
import logging

def inicializar_esquema(conn: sqlite3.Connection) -> None:
    """
    Ejecuta el DDL base utilizando idempotencia (IF NOT EXISTS) 
    y restricciones relacionales.
    """
    esquema_sql = """
    -- Tabla principal con tipado estricto
    CREATE TABLE IF NOT EXISTS usuarios (
        id INTEGER PRIMARY KEY,
        uuid TEXT UNIQUE NOT NULL,
        email TEXT UNIQUE NOT NULL,
        fecha_registro TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) STRICT;

    -- Tabla relacional demostrando restricciones CHECK y Claves Foráneas
    CREATE TABLE IF NOT EXISTS sesiones (
        id INTEGER PRIMARY KEY,
        usuario_id INTEGER NOT NULL,
        token TEXT NOT NULL,
        estado TEXT DEFAULT 'activa',

        -- Integridad referencial
        FOREIGN KEY (usuario_id) REFERENCES usuarios (id) ON DELETE CASCADE,

        -- Validación a nivel de base de datos para emular un ENUM
        CHECK (estado IN ('activa', 'expirada', 'revocada'))
    ) STRICT;

    -- Índices para optimizar las operaciones de lectura (Read)
    CREATE INDEX IF NOT EXISTS idx_sesiones_usuario ON sesiones(usuario_id);
    """

    try:
        # executescript emite automáticamente un COMMIT si no hay errores
        conn.executescript(esquema_sql)
        logging.info("Esquema de base de datos verificado/inicializado correctamente.")
    except sqlite3.OperationalError as e:
        logging.critical(f"Error de DDL al construir el esquema: {e}")
        raise

import sqlite3
import logging

def inicializar_esquema(conn: sqlite3.Connection) -> None:
    """
    Ejecuta el DDL base utilizando idempotencia (IF NOT EXISTS) 
    y restricciones relacionales.
    """
    esquema_sql = """
    -- Tabla principal con tipado estricto
    CREATE TABLE IF NOT EXISTS usuarios (
        id INTEGER PRIMARY KEY,
        uuid TEXT UNIQUE NOT NULL,
        email TEXT UNIQUE NOT NULL,
        fecha_registro TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    ) STRICT;

    -- Tabla relacional demostrando restricciones CHECK y Claves Foráneas
    CREATE TABLE IF NOT EXISTS sesiones (
        id INTEGER PRIMARY KEY,
        usuario_id INTEGER NOT NULL,
        token TEXT NOT NULL,
        estado TEXT DEFAULT 'activa',

        -- Integridad referencial
        FOREIGN KEY (usuario_id) REFERENCES usuarios (id) ON DELETE CASCADE,

        -- Validación a nivel de base de datos para emular un ENUM
        CHECK (estado IN ('activa', 'expirada', 'revocada'))
    ) STRICT;

    -- Índices para optimizar las operaciones de lectura (Read)
    CREATE INDEX IF NOT EXISTS idx_sesiones_usuario ON sesiones(usuario_id);
    """

    try:
        # executescript emite automáticamente un COMMIT si no hay errores
        conn.executescript(esquema_sql)
        logging.info("Esquema de base de datos verificado/inicializado correctamente.")
    except sqlite3.OperationalError as e:
        logging.critical(f"Error de DDL al construir el esquema: {e}")
        raise

5.4. Consideraciones de Diseño Estructural

1. Idempotencia Obligatoria: El uso sistemático de IF NOT EXISTS permite que el script de inicialización se ejecute en cada arranque de la aplicación de Python sin lanzar excepciones OperationalError si la base de datos ya está montada.
2. INTEGER PRIMARY KEY vs AUTOINCREMENT: Por defecto, una columna INTEGER PRIMARY KEY actúa como alias del ROWID interno de SQLite y se autoincrementa. Evita usar la palabra clave explícita AUTOINCREMENT a menos que necesites garantizar matemáticamente que un ID eliminado nunca se reciclará, ya que AUTOINCREMENT incurre en un sobrecosto de CPU y memoria interno.
3. ON DELETE CASCADE: Si habilitaste el soporte de claves foráneas (PRAGMA foreign_keys = ON; configurado en el Punto 1), delegar las eliminaciones en cascada al motor SQLite mediante el esquema es mucho más eficiente que iterar y eliminar registros relacionados manualmente desde Python.