Ir al contenido

关系建模与关键概念

分析 Python sqlite3 引擎中游标和 ACID 事务的使用概念,结合一对多关系建模的实践示例。

0阅读

要安全高效地通过 Python 等编程语言与 SQLite 交互,必须掌握两个关键架构原则:数据库游标 和 事务性操作。


在 Python 的 sqlite3 库中,语句执行并非直接在数据库连接(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()

事务是一组逻辑上的一条或多条 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()。


接下来,我们将使用外键约束(FOREIGN KEY)实现一个 一对多(1:N) 关系型数据库模型。

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
}

我们创建“父”表(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_tareas 中仍存在关联的孤儿任务,则禁止删除 portel_proyectos 中的任何项目。


我们创建两个项目并分配任务,通过关联的相对 ID 进行绑定:

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

为了检索已合并的任务及其所属项目名称,我们通过 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

要将设计里程碑标记为原子完成:

UPDATE portel_tareas
SET completada = 1
WHERE id = 1;

确保在Portelcore实验室中数据库的逻辑一致性,按照此控制清单进行操作:

  • ✓ 启用外键:确保你的 Python 脚本在打开连接时调用验证指令:conn.execute("PRAGMA foreign_keys = ON;")(在 SQLite 中默认禁用)。
  • ✓ 外键映射:验证数据表之间的所有逻辑关系都包含 FOREIGN KEY 类型的声明性约束。
  • ✓ 事务提交(Commit):验证所有改变 .db 文件物理状态的写入操作都通过 conn.commit() 显式提交。
  • ✓ 级联管理:在创建模式时正确规划 ON DELETE(RESTRICT 或 CASCADE)指令,以避免孤儿记录或意外的级联数据删除。