Ir al contenido

Реляционное моделирование и ключевые понятия

Концептуальный анализ использования курсоров и транзакций ACID в движке sqlite3 Python с практическим примером реляционного моделирования один-ко-многим.

0просмотры

Для эффективного и безопасного взаимодействия с SQLite из языков программирования, таких как Python, необходимо усвоить два основных архитектурных принципа: курсоры базы данных и транзакционные транзакции.


4.1. Основные понятия: курсоры и транзакции

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

Что такое курсор?

Sección titulada «¿Qué es un Cursor?»

В библиотеке sqlite3 Python выполнение операторов не производится напрямую над соединением с базой данных (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()

Что такое транзакция?

Sección titulada «¿Qué es una Transacción?»

Транзакция представляет собой логическую последовательность одной или нескольких инструкций 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: Отменяет внесенные изменения и возвращает базу данных в исходное состояние до начала блока.

В Python модуль 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
}

Шаг 1: объявление логических схем (DDL)

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

Мы создаем таблицу «родитель» (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_proyectos, если существуют связанные осиротевшие задачи в portel_tareas.


Шаг 2: вставка тестовых записей (DML)

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

Мы обеспечиваем два проекта и назначаем задачи, связывая их соответствующие реляционные идентификаторы:

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

Шаг 3: расширенные реляционные запросы (DQL - JOIN)

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

Чтобы получить консолидированные задачи вместе с именем проекта, к которому они принадлежат, мы реализуем связь через 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

Шаг 4: контролируемое обновление (DML)

Sección titulada «Paso 4: Actualización Controlada (DML)»

Чтобы пометить этап проектирования как завершенный атомарно:

UPDATE portel_tareas
SET completada = 1
WHERE id = 1;

✅ Checklist безопасного реляционного моделирования

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

Обеспечьте логическую последовательность своих баз данных в лабораториях Portelcore, следуя этому checklist контроля:

  • ✓ Активация внешних ключей: Обеспечьте, чтобы ваш скрипт Python вызывал директиву проверки при открытии соединения: conn.execute("PRAGMA foreign_keys = ON;") (отключена по умолчанию в SQLite).
  • ✓ Отображение внешних ключей: Проверьте, что все логические отношения между таблицами данных содержат декларативные ограничения типа FOREIGN KEY.
  • ✓ Транзакционное подтверждение (Commit): Подтвердите, что все операции записи, изменяющие физическое состояние файла .db, подтверждаются явно с помощью conn.commit().
  • ✓ Каскадное управление: Правильно спланируйте директивы ON DELETE (RESTRICT или CASCADE) при создании схем, чтобы избежать появления孤ных записей или случайного удаления данных в каскаде.