Relational Modeling and Key Concepts
Conceptual analysis of the use of cursors and ACID transactions in the Python sqlite3 engine, with a practical example of one-to-many relational modeling.
To interact efficiently and securely with SQLite from programming languages such as Python, it is essential to grasp two fundamental architectural principles: database Cursors and Transactions.
4.1. Fundamental Concepts: Cursors and Transactions
Sección titulada «4.1. Conceptos Fundamentales: Cursores y Transacciones»What is a Cursor?
Sección titulada «¿Qué es un Cursor?»In the Python sqlite3 library, statement execution is not performed directly on the database connection (conn). Instead, it is necessary to instantiate an intermediate control structure called a Cursor (cursor).
- Technical Definition: A cursor acts as a memory pointer or control agent that executes SQL statements on the physical file on disk and manages the returned result set.
- Operational Functioning:
- Secure Execution: Receives and processes the query:
cursor.execute(sql). - Controlled Extraction: Temporarily stores the data resulting from reads, allowing for granular retrieval using
cursor.fetchone()(extracts a single row) orcursor.fetchall()(retrieves all resulting records).
- Secure Execution: Receives and processes the query:
# Conexión al motor de base de datos localconn = obtener_conexion(db_name)
# Instanciación del puntero o cursorcursor = conn.cursor()
# Ejecución de la sentencia relacionalcursor.execute("SELECT nombre, email FROM portel_usuarios;")
# Recuperación de resultados en memoria del procesoresultados = cursor.fetchall()What is a Transaction?
Sección titulada «¿Qué es una Transacción?»A transaction is a logical sequence of one or more SQL instructions that are executed as a single, indivisible unit of work. This behavior follows the Atomicity principle of the ACID standard: either all operations complete successfully, or the entire batch is reverted to its initial state.
Let's analyze the classic financial transaction where we transfer a balance between two records:
BEGIN TRANSACTION;-- 1. Restar importe del emisorUPDATE portel_cuentas SET saldo = saldo - 100 WHERE id = 'A';-- 2. Sumar importe al receptorUPDATE portel_cuentas SET saldo = saldo + 100 WHERE id = 'B';COMMIT;If the server were to experience a sudden power loss after completing operation 1 but before operation 2, the balance would vanish. Transactions mitigate this risk:
BEGIN TRANSACTION: Opens an isolated working area on disk.COMMIT: Makes all insertions or modifications made since the start of the transaction permanent.ROLLBACK: Cancels the modifications made and returns the database to its exact original state prior to the start of the block.
In Python, the sqlite3 module implicitly initializes transactions when executing write statements (INSERT, UPDATE, DELETE). To physically commit changes to the .db file, you must explicitly call conn.commit().
4.2. Practical Case: Project and Task Management
Sección titulada «4.2. Caso Práctico: Gestión de Proyectos y Tareas»Next, we will implement a One-to-Many (1:N) relational database model using foreign key constraints (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 }Step 1: Logical Schema Declaration (DDL)
Sección titulada «Paso 1: Declaración de Esquemas Lógicos (DDL)»We create the "parent" table (portel_proyectos) and subsequently the "child" table (portel_tareas), linking them via a foreign key:
-- Estructura PrincipalCREATE TABLE portel_proyectos ( id INTEGER PRIMARY KEY AUTOINCREMENT, nombre_proyecto TEXT NOT NULL, fecha_inicio TEXT DEFAULT (date('now')));
-- Estructura RelacionadaCREATE 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] Referential Integrity: The
ON DELETE RESTRICTdefinition tells the database engine to block the deletion of any project inportel_proyectosif orphaned tasks linked inportel_tareasstill exist.
Step 2: Insertion of Test Records (DML)
Sección titulada «Paso 2: Inserción de Registros de Prueba (DML)»We provision two projects and assign tasks by linking their respective relational IDs:
-- Inserción de proyectosINSERT 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);Step 3: Advanced Relational Queries (DQL - JOIN)
Sección titulada «Paso 3: Consultas Relacionales Avanzadas (DQL - JOIN)»To retrieve the consolidated tasks along with the name of the project they belong to, we implement a coupling using JOIN:
SELECT portel_proyectos.nombre_proyecto, portel_tareas.descripcion, portel_tareas.completadaFROM portel_proyectosJOIN portel_tareas ON portel_proyectos.id = portel_tareas.id_proyecto;Expected Formatted Output:
Sección titulada «Salida Formateada Esperada:»Hacer portal de documentación | Diseñar interfaz premium | 0Hacer portal de documentación | Sanear datos de prueba | 0Migrar base de datos local | Exportar esquemas SQL | 0Step 4: Controlled Update (DML)
Sección titulada «Paso 4: Actualización Controlada (DML)»To mark the design milestone as completed atomically:
UPDATE portel_tareasSET completada = 1WHERE id = 1;🏁 Secure Relational Modeling Checklist
Sección titulada «🏁 Checklist de Modelado Relacional Seguro»Ensure the logical consistency of your databases in the Portelcore labs by following this control checklist:
- ✓ Foreign Key Activation: Ensure that your Python script invokes the verification directive when opening the connection:
conn.execute("PRAGMA foreign_keys = ON;")(disabled by default in SQLite). - ✓ Foreign Key Mapping: Verify that all logical relationships between data tables contain declarative constraints of type
FOREIGN KEY. - ✓ Transactional Commit (
Commit): Validate that all write operations that alter the physical state of the.dbfile are explicitly confirmed viaconn.commit(). - ✓ Cascade Management: Correctly plan
ON DELETEdirectives (RESTRICTorCASCADE) when creating schemas to avoid orphaned records or accidental data deletion in cascade.

