Ir al contenido

関係データモデルと重要なコンセプト

Python の sqlite3 エンジンにおけるカーソルと ACID トランザクションの概念的分析と、1対多の関係データモデルの実践的な例。

0閲覧数

Pythonなどのプログラミング言語からSQLiteと効率的かつ安全にやり取りするには、データベース<strong>カーソル</strong>と<strong>トランザクション</strong>という2つの基本的なアーキテクチャ原則を習得することが不可欠です。


4.1. 基本概念: カーソルとトランザクション

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

カーソルとは何ですか?

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

Pythonのsqlite3ライブラリでは、文の実行はデータベース接続(conn)に対して直接行われません。その代わり、Cursor(cursor)と呼ばれる中間制御構造をインスタンス化する必要があります。

  • 技術的定義: カーソルは、ディスク上の物理ファイルでSQLステートメントを実行し、返されたレコードのセットを管理するメモリポインタまたは制御エージェントです。
  • 動作の仕組み:
    1. 安全な実行: クエリを受信・処理します: cursor.execute(sql).
    2. 制御された抽出: 読み出し結果のデータを一時的に保持し、cursor.fetchone()(1行のみを抽出)または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?»

トランザクションは、<strong>単一の不可分な作業ユニット</strong>として実行される1つ以上のSQLステートメントの論理的なシーケンスです。これは、ACID標準の<strong>アトミシティ</strong>原則に応じています: <em>「すべての操作が成功して完了するか、または全体のバッチが初期状態に戻される」</em>。

ここでは、2つのレコード間で残高を転送する典型的な金融トランザクションを分析します:

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»

次に、外部キー制約(FOREIGN KEY)を使って、1対多(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
}

ステップ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_tareasに紐づく孤児タスクがまだ存在する場合、portel_proyectos内のプロジェクトの削除をデータベースエンジンがブロックするよう指示されます。


ステップ2: テストレコードの挿入 (DML)

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

私たちは2つのプロジェクトをプロビジョニングし、それぞれの関係的な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);

ステップ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;

🏁 セキュアな関係モデリングチェックリスト

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

ポートルコアラボでデータベースの論理的一貫性を確保するには、この制御チェックリストに従ってください:

  • ✓ 外部キーの有効化: Pythonスクリプトが接続オープン時に検証ディレクティブを呼び出すようにしてください: conn.execute("PRAGMA foreign_keys = ON;")(SQLiteでは既定で無効です)。
  • ✓ 外部キーのマッピング: データテーブル間のすべての論理関係が、宣言型のFOREIGN KEY制約を含んでいることを確認してください。
  • ✓ トランザクションのコミット(Commit): .dbファイルの物理的な状態を変更するすべての書き込み操作が、conn.commit()を使って明示的にコミットされていることを確認してください。
  • ✓ カスケード管理: スキーマ作成時にON DELETE(RESTRICTまたはCASCADE)を正しく設計し、孤児レコードや意図しないカスケード削除を防ぎます。