Ir al contenido

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.

0views

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»

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:
    1. Secure Execution: Receives and processes the query: cursor.execute(sql).
    2. Controlled Extraction: Temporarily stores the data resulting from reads, allowing for granular retrieval using cursor.fetchone() (extracts a single row) or cursor.fetchall() (retrieves all resulting records).
# 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()

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

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().


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
}

We create the "parent" table (portel_proyectos) and subsequently the "child" table (portel_tareas), linking them via a foreign key:

-- 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] Referential Integrity: The ON DELETE RESTRICT definition tells the database engine to block the deletion of any project in portel_proyectos if orphaned tasks linked in portel_tareas still exist.


We provision two projects and assign tasks by linking their respective relational IDs:

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

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.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

To mark the design milestone as completed atomically:

UPDATE portel_tareas
SET completada = 1
WHERE id = 1;

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 .db file are explicitly confirmed via conn.commit().
  • ✓ Cascade Management: Correctly plan ON DELETE directives (RESTRICT or CASCADE) when creating schemas to avoid orphaned records or accidental data deletion in cascade.