データベースとWebアプリケーション開発の基礎

SQLFiddleでの実行手順（第1-9回、第15回で使用）

1. SQLFiddleのSQLiteページ（https://sqlfiddle.com/sqlite/online-compiler）にアクセスする（アカウント登録不要）。
2. エディタに各回のSQLコード（テーブル作成、データ挿入、クエリ）をまとめて入力する。
3. 「Execute」をクリックして結果を確認する。

Python + Flask のローカル実行手順（第11-14回で使用）

第11-14回のWebアプリケーションは、Windowsにインストールした Python と Flask で実行する。手順は以下のとおりである。

1. 作業用フォルダを作成する（例：ドキュメント\flask_lessons）。
2. 各回のコードを main.py として保存する。テンプレートが必要な回（第12-14回）は、main.py と同じ場所に templates という名前のフォルダを作成し、その中に指定の HTML ファイルを保存する。
3. コマンドプロンプトを開き、cd コマンドで作業用フォルダに移動する（例：cd %USERPROFILE%\Documents\flask_lessons）。
4. python main.py を実行する。
5. Webブラウザで http://localhost:5000 にアクセスして結果を確認する。
6. サーバを停止するには、コマンドプロンプトで Ctrl + C を押す。

flask_lessons\
├── main.py
└── templates\
    └── products.html

（任意）Replitでのオンライン実行

パソコンへインストールせずブラウザだけで試す場合は、オンライン開発環境の Replit（https://replit.com/）を利用できる。ただし2026年現在、Replit の無料プラン（Starter）はAI機能向けのクレジット制が中心であり、公開したアプリのURLは一定期間後に自動停止するなどの制限がある。本教材の演習は前述のローカル実行だけで完結するため、Replit の利用は必須ではない。利用する場合は、アカウント登録後に「Python」テンプレートで Repl を作成し、main.py と templates フォルダを同様に用意して「Run」を実行する。最新の無料枠・制限は Replit の料金ページで確認する。

演習1．データベースの基本

• 概念・用語：
  • データベース（DB）：組織的に管理されたデータの集合
  • テーブル：データを行と列で表現した表
  • レコード（行）：テーブル内の1件のデータ
  • カラム（列）：データの属性（名前、価格など）
• 使用ツール：SQLFiddle

手順：「ツールの使い方」のSQLFiddleでの実行手順に従い、以下のSQLをエディタに貼り付けて「Execute」をクリックする。

-- 商品テーブルの作成
-- CREATE TABLE文でテーブルの構造を定義する
CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,  -- 主キー：各行を一意に識別
    name TEXT NOT NULL,              -- 商品名（必須）
    price INTEGER NOT NULL,          -- 価格（必須）
    stock INTEGER NOT NULL DEFAULT 0 -- 在庫数（デフォルト値0）
);

-- INSERT文でデータを挿入する
INSERT INTO products (product_id, name, price, stock) VALUES
(1, 'ノートパソコン', 89800, 15),
(2, 'ワイヤレスマウス', 2980, 50),
(3, 'キーボード', 5800, 30),
(4, 'モニター', 24800, 20),
(5, 'Webカメラ', 7800, 25);

-- 全データを取得
SELECT * FROM products;

-- 特定の列のみ取得
SELECT name, price FROM products;

ヒント：SELECT * の * はすべての列を表す。列名を並べると、その列だけを取得する。

考察ポイント：SELECT * と SELECT name, price で出力される列の違いを確認する。stock を指定しなかった行で、DEFAULT 0 がどう反映されているかを確認する。

演習2．リレーショナルデータベースとSQL入門

• 概念・用語：
  • リレーショナルデータベース：データを表（テーブル）形式で管理し、テーブル間の関係を定義できるデータベース
  • SQL（Structured Query Language）：データベースを操作するための言語
  • SELECT文：データを取得する命令
  • INSERT文：データを挿入する命令
• 使用ツール：SQLFiddle

手順：「ツールの使い方」のSQLFiddleでの実行手順に従い、以下のSQLをエディタに貼り付けて「Execute」をクリックする。

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price INTEGER NOT NULL,
    stock INTEGER NOT NULL DEFAULT 0
);

INSERT INTO products (product_id, name, price, stock) VALUES
(1, 'ノートパソコン', 89800, 15),
(2, 'ワイヤレスマウス', 2980, 50),
(3, 'キーボード', 5800, 30),
(4, 'モニター', 24800, 20),
(5, 'Webカメラ', 7800, 25);

-- 全データの取得
SELECT * FROM products;

-- 特定の列のみ取得
SELECT name, price FROM products;

-- 新しいデータの挿入
INSERT INTO products (product_id, name, price, stock)
VALUES (6, 'ヘッドセット', 4500, 40);

-- 挿入後の確認
SELECT * FROM products;

ヒント：INSERT文では、列名の並び（name, price, stock）と値の並びを対応させる。

考察ポイント：INSERT文の実行前と実行後の SELECT * FROM products の結果を比較し、行が1件増えていることを確認する。

演習3．データベース設計と正規化

• 概念・用語：
  • 正規化：データの重複を排除し、整合性を保ちやすくする設計手法
  • 冗長性：同じデータが複数箇所に存在する状態
  • データ整合性：データに矛盾がない状態
• 使用ツール：SQLFiddle

手順：「ツールの使い方」のSQLFiddleでの実行手順に従い、以下のSQLをエディタに貼り付けて「Execute」をクリックする。

-- ▼ 正規化前のテーブル（問題のある設計）
-- 同じ顧客情報（山田太郎）が複数回登場している
CREATE TABLE orders_before (
    order_id INTEGER PRIMARY KEY,
    customer_name TEXT,
    customer_email TEXT,
    product_name TEXT,
    product_price INTEGER,
    order_date TEXT
);

INSERT INTO orders_before VALUES
(1, '山田太郎', 'yamada@example.com', 'ノートパソコン', 89800, '2023-01-15'),
(2, '山田太郎', 'yamada@example.com', 'マウス', 2980, '2023-01-15'),
(3, '佐藤花子', 'sato@example.com', 'モニター', 24800, '2023-01-20');

-- ▼ 正規化後のテーブル（適切な設計）
-- 顧客・商品・注文を別テーブルに分離
CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE NOT NULL
);

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price INTEGER NOT NULL
);

CREATE TABLE orders_after (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    order_date TEXT NOT NULL
);

INSERT INTO customers VALUES (1, '山田太郎', 'yamada@example.com');
INSERT INTO customers VALUES (2, '佐藤花子', 'sato@example.com');

INSERT INTO products VALUES (1, 'ノートパソコン', 89800);
INSERT INTO products VALUES (2, 'マウス', 2980);
INSERT INTO products VALUES (3, 'モニター', 24800);

INSERT INTO orders_after VALUES (1, 1, 1, '2023-01-15');
INSERT INTO orders_after VALUES (2, 1, 2, '2023-01-15');
INSERT INTO orders_after VALUES (3, 2, 3, '2023-01-20');

-- 正規化前：顧客情報が重複している
SELECT * FROM orders_before;

-- 正規化後：顧客情報は1回のみ保存
SELECT * FROM customers;

-- 正規化後：テーブルを結合してデータを取得
SELECT c.name, p.name AS product, o.order_date
FROM orders_after o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id;

ヒント：末尾の JOIN と AS は演習6で扱う。ここでは、分離した3つのテーブルを再びつなげて表示するための記述として読む。

考察ポイント：orders_before では山田太郎の氏名とメールが2行に重複している。正規化後の customers では1回だけ保存されていることを確認し、重複が排除された点を読み取る。

演習4．データの検索（WHERE句）

• 概念・用語：
  • WHERE句：検索条件を指定する
  • 比較演算子：=（等しい）、>（より大きい）、<（より小さい）、>=、<=、<>（等しくない）
  • 論理演算子：AND（かつ）、OR（または）
• 使用ツール：SQLFiddle

手順：「ツールの使い方」のSQLFiddleでの実行手順に従い、以下のSQLをエディタに貼り付けて「Execute」をクリックする。

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price INTEGER NOT NULL,
    stock INTEGER NOT NULL DEFAULT 0
);

INSERT INTO products (product_id, name, price, stock) VALUES
(1, 'ノートパソコン', 89800, 15),
(2, 'ワイヤレスマウス', 2980, 50),
(3, 'キーボード', 5800, 30),
(4, 'モニター', 24800, 20),
(5, 'Webカメラ', 7800, 25),
(6, 'ヘッドセット', 4500, 40),
(7, 'USBメモリ', 1280, 100),
(8, '外付けHDD', 8900, 35);

-- 価格が5000円より高い商品
SELECT * FROM products WHERE price > 5000;

-- 在庫が30以上の商品
SELECT * FROM products WHERE stock >= 30;

-- 価格が1000円以上かつ5000円以下（AND）
SELECT * FROM products WHERE price >= 1000 AND price <= 5000;

-- 価格が3000円未満または80000円以上（OR）
SELECT * FROM products WHERE price < 3000 OR price >= 80000;

ヒント：AND は両方の条件を満たす行、OR はどちらか一方を満たす行を取得する。

考察ポイント：各クエリで取得される行数を比較し、条件を厳しくする（ANDを使う）と行数が減り、条件を緩める（ORを使う）と行数が増える傾向を読み取る。

演習5．SELECT文の応用（BETWEEN・LIKE・ORDER BY）

• 概念・用語：
  • BETWEEN：範囲を指定する（例：1000から5000の間）
  • LIKE：パターンマッチング（%は任意の文字列、_は任意の1文字）
  • ORDER BY：結果を並び替える（ASC：昇順、DESC：降順）
• 使用ツール：SQLFiddle

手順：「ツールの使い方」のSQLFiddleでの実行手順に従い、以下のSQLをエディタに貼り付けて「Execute」をクリックする。

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price INTEGER NOT NULL,
    stock INTEGER NOT NULL DEFAULT 0
);

INSERT INTO products (product_id, name, price, stock) VALUES
(1, 'ノートパソコン', 89800, 15),
(2, 'ワイヤレスマウス', 2980, 50),
(3, 'ワイヤレスキーボード', 5800, 30),
(4, 'モニター', 24800, 20),
(5, 'Webカメラ', 7800, 25),
(6, 'ヘッドセット', 4500, 40),
(7, 'USBメモリ', 1280, 100),
(8, '外付けHDD', 8900, 35);

-- BETWEEN：価格が1000円から5000円の範囲
SELECT * FROM products WHERE price BETWEEN 1000 AND 5000;

-- LIKE：「ワイヤレス」を含む商品名（%は任意の文字列）
SELECT * FROM products WHERE name LIKE '%ワイヤレス%';

-- ORDER BY：価格の高い順
SELECT * FROM products ORDER BY price DESC;

-- 複数条件での並び替え：在庫数の多い順、同じ場合は価格の安い順
SELECT * FROM products ORDER BY stock DESC, price ASC;

ヒント：BETWEEN の範囲には両端の値（1000と5000）を含む。ORDER BY に2つの列を指定すると、1列目が同じ値の行だけ2列目で並べ替える。

考察ポイント：LIKE の結果に「ワイヤレスマウス」と「ワイヤレスキーボード」が含まれることを確認する。複数条件の ORDER BY で、在庫数が同じ行が価格順に並んでいるかを読み取る。

演習6．テーブルの結合（JOIN）

• 概念・用語：
  • JOIN：複数のテーブルを関連付けて結合する
  • INNER JOIN：両方のテーブルに存在するデータのみ取得する（単にJOINと書いた場合はINNER JOINになる）
  • ON句：結合条件を指定する
  • 別名（AS）：テーブルや列に短い名前を付ける（例：customers c とすると、以降 c でそのテーブルを参照できる）
• 使用ツール：SQLFiddle

手順：「ツールの使い方」のSQLFiddleでの実行手順に従い、以下のSQLをエディタに貼り付けて「Execute」をクリックする。

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL
);

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price INTEGER NOT NULL
);

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL DEFAULT 1,
    order_date TEXT NOT NULL
);

INSERT INTO customers VALUES (1, '山田太郎', 'yamada@example.com');
INSERT INTO customers VALUES (2, '佐藤花子', 'sato@example.com');
INSERT INTO customers VALUES (3, '鈴木一郎', 'suzuki@example.com');

INSERT INTO products VALUES (1, 'ノートパソコン', 89800);
INSERT INTO products VALUES (2, 'マウス', 2980);
INSERT INTO products VALUES (3, 'モニター', 24800);

INSERT INTO orders VALUES (1, 1, 1, 1, '2023-01-15');
INSERT INTO orders VALUES (2, 1, 2, 2, '2023-01-15');
INSERT INTO orders VALUES (3, 2, 3, 1, '2023-01-20');
INSERT INTO orders VALUES (4, 3, 2, 3, '2023-01-22');

-- 2つのテーブルを結合：顧客名と注文日を表示
SELECT c.name, o.order_date
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;

-- 3つのテーブルを結合：注文詳細を表示
SELECT
    c.name AS customer_name,
    p.name AS product_name,
    o.quantity,
    p.price,
    (p.price * o.quantity) AS total
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id;

ヒント：ON句で「どの列どうしを対応させるか」を指定する。ここでは customer_id や product_id が共通の列である。

考察ポイント：注文テーブルには商品名や価格が含まれていないが、JOINによって商品テーブルから商品名・価格を取得できることを確認する。total 列が price × quantity で計算されている点を読み取る。

演習7．データの集計（GROUP BY）

• 概念・用語：
  • GROUP BY：指定した列の値が同じ行をグループ化する
  • 集計関数：COUNT（件数）、SUM（合計）、AVG（平均）、MAX（最大）、MIN（最小）
  • HAVING：グループに対する条件を指定する（WHERE句はグループ化前、HAVING句はグループ化後に適用される）
• 使用ツール：SQLFiddle

手順：「ツールの使い方」のSQLFiddleでの実行手順に従い、以下のSQLをエディタに貼り付けて「Execute」をクリックする。

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price INTEGER NOT NULL
);

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL,
    order_date TEXT NOT NULL
);

INSERT INTO customers VALUES (1, '山田太郎');
INSERT INTO customers VALUES (2, '佐藤花子');
INSERT INTO customers VALUES (3, '鈴木一郎');

INSERT INTO products VALUES (1, 'ノートパソコン', 89800);
INSERT INTO products VALUES (2, 'マウス', 2980);
INSERT INTO products VALUES (3, 'モニター', 24800);

INSERT INTO orders VALUES (1, 1, 1, 1, '2023-01-15');
INSERT INTO orders VALUES (2, 1, 2, 2, '2023-01-15');
INSERT INTO orders VALUES (3, 2, 3, 1, '2023-02-20');
INSERT INTO orders VALUES (4, 1, 2, 1, '2023-02-25');
INSERT INTO orders VALUES (5, 3, 1, 1, '2023-03-10');
INSERT INTO orders VALUES (6, 2, 2, 3, '2023-03-15');

-- 顧客別の注文回数
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;

-- 顧客別の合計金額
SELECT
    c.name,
    COUNT(o.order_id) AS order_count,
    SUM(p.price * o.quantity) AS total_amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id
GROUP BY c.customer_id;

-- HAVING：2回以上注文した顧客のみ
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 2;

ヒント：COUNT(*) はグループ内の行数を数える。HAVING はグループ化した後の結果に対する条件を指定する。

考察ポイント：顧客別の注文回数と、HAVINGで絞り込んだ結果を比較し、注文回数が1回の顧客が除外されていることを確認する。

演習8．副問い合わせ（サブクエリ）

• 概念・用語：
  • サブクエリ（副問い合わせ）：クエリの中に含まれるクエリ。内側のクエリが先に実行され、その結果を外側のクエリが使用する
• 使用ツール：SQLFiddle

手順：「ツールの使い方」のSQLFiddleでの実行手順に従い、以下のSQLをエディタに貼り付けて「Execute」をクリックする。

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price INTEGER NOT NULL,
    stock INTEGER NOT NULL
);

INSERT INTO products VALUES (1, 'ノートパソコン', 89800, 15);
INSERT INTO products VALUES (2, 'マウス', 2980, 50);
INSERT INTO products VALUES (3, 'キーボード', 5800, 30);
INSERT INTO products VALUES (4, 'モニター', 24800, 20);
INSERT INTO products VALUES (5, 'Webカメラ', 7800, 25);

-- 平均価格を確認
SELECT AVG(price) AS avg_price FROM products;

-- 平均価格より高い商品を取得（サブクエリ使用）
SELECT * FROM products
WHERE price > (SELECT AVG(price) FROM products);

-- 最高価格の商品を取得
SELECT * FROM products
WHERE price = (SELECT MAX(price) FROM products);

-- 最低価格の商品を取得
SELECT * FROM products
WHERE price = (SELECT MIN(price) FROM products);

ヒント：カッコ内のサブクエリが先に実行され、その結果（平均値や最大値）を外側のWHERE句が条件として使う。

考察ポイント：1番目のクエリで表示される平均価格と、2番目のクエリで取得される商品の価格を見比べ、平均より高い商品だけが取得されていることを確認する。

演習9．主キーと外部キー

• 概念・用語：
  • 主キー（PRIMARY KEY）：テーブル内で各行を一意に識別する列。重複と空値（NULL）は許可されない
  • 外部キー（FOREIGN KEY）：他のテーブルの主キーを参照する列。テーブル間の関係を定義する
  • 参照整合性：外部キーが参照する値が必ず存在することを保証する仕組み
• 使用ツール：SQLFiddle

手順：「ツールの使い方」のSQLFiddleでの実行手順に従い、以下のSQLをエディタに貼り付けて「Execute」をクリックする。

-- SQLiteで外部キー制約を有効化する（接続ごとに必要）
PRAGMA foreign_keys = ON;

-- 親テーブル（主キーを持つ）
CREATE TABLE categories (
    category_id INTEGER PRIMARY KEY,
    category_name TEXT NOT NULL UNIQUE
);

-- 子テーブル（外部キーで親テーブルを参照）
CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price INTEGER NOT NULL,
    category_id INTEGER,
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
);

INSERT INTO categories VALUES (1, 'コンピュータ');
INSERT INTO categories VALUES (2, '周辺機器');
INSERT INTO categories VALUES (3, 'ストレージ');

INSERT INTO products VALUES (1, 'ノートパソコン', 89800, 1);
INSERT INTO products VALUES (2, 'マウス', 2980, 2);
INSERT INTO products VALUES (3, 'キーボード', 5800, 2);
INSERT INTO products VALUES (4, 'USBメモリ', 1280, 3);

-- カテゴリと商品を結合して表示
SELECT
    p.name AS product_name,
    p.price,
    c.category_name
FROM products p
JOIN categories c ON p.category_id = c.category_id;

-- カテゴリ別の商品数（LEFT JOINで商品ゼロのカテゴリも表示）
SELECT
    c.category_name,
    COUNT(p.product_id) AS product_count
FROM categories c
LEFT JOIN products p ON c.category_id = p.category_id
GROUP BY c.category_id;

ヒント：LEFT JOIN は左側のテーブル（categories）の全行を残し、対応する右側の行がなければ空欄として結合する。

考察ポイント：カテゴリ別の商品数を確認し、各カテゴリに何件の商品が属しているかを読み取る。

演習10．HTMLの基礎とフォーム

• 概念・用語：
  • HTML（HyperText Markup Language）：Webページの構造を記述するマークアップ言語
  • タグ：HTMLの構成要素（例：<h1>、<p>、<form>）
  • フォーム：ユーザからの入力を受け付ける要素
  • input要素：テキスト入力、数値入力、送信ボタンなど
• 使用ツール：Webブラウザ

手順：以下の内容を index.html という名前で保存し、そのファイルをダブルクリックしてWebブラウザで開く。この段階ではフォームの送信先（/add）は動作しない（演習11以降のFlaskで処理する）。

<!DOCTYPE html>
<html lang="ja">
<head>
    <meta charset="UTF-8">
    <title>商品管理</title>
</head>
<body>
    <h1>商品一覧</h1>
    <table border="1">
        <tr>
            <th>商品名</th>
            <th>価格</th>
        </tr>
        <tr>
            <td>ノートパソコン</td>
            <td>89,800円</td>
        </tr>
        <tr>
            <td>マウス</td>
            <td>2,980円</td>
        </tr>
    </table>

    <h2>商品登録フォーム</h2>
    <form action="/add" method="post">
        <p>
            <label for="name">商品名：</label>
            <input type="text" id="name" name="name" required>
        </p>
        <p>
            <label for="price">価格：</label>
            <input type="number" id="price" name="price" required>
        </p>
        <p>
            <input type="submit" value="登録">
        </p>
    </form>
</body>
</html>

ヒント：required属性は入力必須を表す。method="post" はフォームデータをPOSTメソッドで送信する指定である。

考察ポイント：表とフォームがブラウザにどう表示されるかを確認する。商品名や価格を空のまま「登録」を押すと、required属性により送信が止まることを確認する。

演習11．PythonとFlask入門

• 概念・用語：
  • Flask：Python用の軽量Webフレームワーク
  • Webフレームワーク：Webアプリケーション開発のための基盤ソフトウェア
  • ルーティング：URLとプログラムの対応付け（例：/aboutにアクセスするとabout関数が実行される）
  • デコレータ（@app.route）：関数にURLを対応付ける記法
• 使用ツール：Python + Flask（Windowsのローカル環境）

手順：「ツールの使い方」のPython + Flask のローカル実行手順に従う。以下を main.py として保存し、コマンドプロンプトで python main.py を実行後、ブラウザで http://localhost:5000 にアクセスする。

from flask import Flask

app = Flask(__name__)

# ルーティング：URLと関数を対応付ける
@app.route('/')
def home():
    return '''
    <h1>Flaskアプリケーション</h1>
    <p>Flaskで作成したWebページです。</p>
    <ul>
        <li><a href="/about">このサイトについて</a></li>
        <li><a href="/products">商品一覧</a></li>
    </ul>
    '''

@app.route('/about')
def about():
    return '''
    <h1>このサイトについて</h1>
    <p>Flaskの学習用サンプルです。</p>
    <p><a href="/">トップに戻る</a></p>
    '''

@app.route('/products')
def products():
    return '''
    <h1>商品一覧</h1>
    <ul>
        <li>ノートパソコン - 89,800円</li>
        <li>マウス - 2,980円</li>
        <li>キーボード - 5,800円</li>
    </ul>
    <p><a href="/">トップに戻る</a></p>
    '''

if __name__ == '__main__':
    app.run(host='127.0.0.1', port=5000, debug=True)

ヒント：@app.route('/about') の /about がURLの末尾に対応する。http://localhost:5000/about にアクセスすると about 関数が実行される。

考察ポイント：トップページのリンクから /about と /products に移動し、URLの末尾と表示内容の対応を確認する。

演習12．データベースとWebの連携

• 概念・用語：
  • SQLite：ファイルベースの軽量データベース。サーバ不要で使用でき、Pythonに標準同梱されている（sqlite3モジュール）
  • テンプレートエンジン（Jinja2）：HTMLにPythonの変数やロジックを埋め込む仕組み。Flaskに同梱されている。{{ }}は値を表示するために、{% %}は繰り返しや条件分岐などの制御のために使う
  • 動的Webページ：データベースの内容に応じて表示が変わるページ
• 使用ツール：Python + Flask + SQLite（Windowsのローカル環境）

手順：「ツールの使い方」のPython + Flask のローカル実行手順に従う。main.py と、同じフォルダ内の templates フォルダに products.html を作成し、python main.py を実行後、ブラウザで http://localhost:5000 にアクセスする。

main.py：

from flask import Flask, render_template
import sqlite3

app = Flask(__name__)

def init_db():
    """データベースを初期化する"""
    conn = sqlite3.connect('shop.db')
    conn.execute('''
        CREATE TABLE IF NOT EXISTS products (
            product_id INTEGER PRIMARY KEY,
            name TEXT NOT NULL,
            price INTEGER NOT NULL
        )
    ''')
    # テーブルが空の場合のみサンプルデータを挿入
    count = conn.execute('SELECT COUNT(*) FROM products').fetchone()[0]
    if count == 0:
        conn.execute("INSERT INTO products VALUES (1, 'ノートパソコン', 89800)")
        conn.execute("INSERT INTO products VALUES (2, 'マウス', 2980)")
        conn.execute("INSERT INTO products VALUES (3, 'キーボード', 5800)")
        conn.commit()
    conn.close()

@app.route('/')
def index():
    """商品一覧を表示する"""
    conn = sqlite3.connect('shop.db')
    conn.row_factory = sqlite3.Row  # 列名でアクセス可能にする
    products = conn.execute('SELECT * FROM products').fetchall()
    conn.close()
    return render_template('products.html', products=products)

if __name__ == '__main__':
    init_db()
    app.run(host='127.0.0.1', port=5000, debug=True)

templates/products.html：

<!DOCTYPE html>
<html lang="ja">
<head>
    <meta charset="UTF-8">
    <title>商品一覧</title>
</head>
<body>
    <h1>商品一覧</h1>
    <table border="1">
        <tr>
            <th>ID</th>
            <th>商品名</th>
            <th>価格</th>
        </tr>
        <!-- {% for %}でループ処理 -->
        {% for product in products %}
        <tr>
            <td>{{ product.product_id }}</td>
            <td>{{ product.name }}</td>
            <td>{{ product.price }}円</td>
        </tr>
        {% endfor %}
    </table>
</body>
</html>

ヒント：conn.row_factory = sqlite3.Row を設定すると、テンプレート内で product.name のように列名でデータを参照できる。

考察ポイント：ブラウザに表示される表の各行が、データベースの3件のデータと対応していることを確認する。{% for %} のループによって、データ件数だけ行が生成される点を読み取る。

演習13．CRUD操作の実装

• 概念・用語：
  • CRUD：データ操作の4つの基本機能（Create：作成、Read：読取、Update：更新、Delete：削除）。本回ではこの4機能すべてをFlaskとSQLiteで実装する
  • POSTメソッド：フォームデータをサーバに送信するHTTPメソッド
  • SQLインジェクション：悪意のある入力によって意図しないSQLを実行させる攻撃手法
  • プレースホルダ（?）：値をSQL文に直接埋め込まず安全に渡す仕組み。SQLインジェクションの防止に有効
• 使用ツール：Python + Flask + SQLite（Windowsのローカル環境）

手順：「ツールの使い方」のPython + Flask のローカル実行手順に従う。main.py と、同じフォルダ内の templates フォルダに index.html、edit.html を作成し、python main.py を実行後、ブラウザで http://localhost:5000 にアクセスする。

main.py：

from flask import Flask, render_template, request, redirect, url_for
import sqlite3

app = Flask(__name__)

def get_db():
    """データベース接続を取得する"""
    conn = sqlite3.connect('shop.db')
    conn.row_factory = sqlite3.Row
    return conn

def init_db():
    """データベースを初期化する"""
    conn = get_db()
    conn.execute('''
        CREATE TABLE IF NOT EXISTS products (
            product_id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            price INTEGER NOT NULL
        )
    ''')
    conn.commit()
    conn.close()

@app.route('/')
def index():
    """商品一覧を表示する（Read）"""
    conn = get_db()
    products = conn.execute('SELECT * FROM products').fetchall()
    conn.close()
    return render_template('index.html', products=products)

@app.route('/add', methods=['POST'])
def add_product():
    """商品を追加する（Create）"""
    name = request.form['name']
    price = request.form['price']
    conn = get_db()
    # プレースホルダ（?）でSQLインジェクションを防止
    conn.execute('INSERT INTO products (name, price) VALUES (?, ?)', (name, price))
    conn.commit()
    conn.close()
    return redirect(url_for('index'))

@app.route('/edit/')
def edit_form(product_id):
    """更新フォームを表示する（Updateの入力画面）"""
    conn = get_db()
    product = conn.execute(
        'SELECT * FROM products WHERE product_id = ?', (product_id,)
    ).fetchone()
    conn.close()
    return render_template('edit.html', product=product)

@app.route('/update/', methods=['POST'])
def update_product(product_id):
    """商品を更新する（Update）"""
    name = request.form['name']
    price = request.form['price']
    conn = get_db()
    conn.execute(
        'UPDATE products SET name = ?, price = ? WHERE product_id = ?',
        (name, price, product_id)
    )
    conn.commit()
    conn.close()
    return redirect(url_for('index'))

@app.route('/delete/', methods=['POST'])
def delete_product(product_id):
    """商品を削除する（Delete）"""
    conn = get_db()
    conn.execute('DELETE FROM products WHERE product_id = ?', (product_id,))
    conn.commit()
    conn.close()
    return redirect(url_for('index'))

if __name__ == '__main__':
    init_db()
    app.run(host='127.0.0.1', port=5000, debug=True)

templates/index.html：

<!DOCTYPE html>
<html lang="ja">
<head>
    <meta charset="UTF-8">
    <title>商品管理</title>
</head>
<body>
    <h1>商品管理</h1>

    <h2>商品追加</h2>
    <form action="/add" method="post">
        <p>商品名: <input type="text" name="name" required></p>
        <p>価格: <input type="number" name="price" required></p>
        <p><input type="submit" value="追加"></p>
    </form>

    <h2>商品一覧</h2>
    <table border="1">
        <tr>
            <th>ID</th>
            <th>商品名</th>
            <th>価格</th>
            <th>操作</th>
        </tr>
        {% for product in products %}
        <tr>
            <td>{{ product.product_id }}</td>
            <td>{{ product.name }}</td>
            <td>{{ product.price }}円</td>
            <td>
                <a href="/edit/{{ product.product_id }}">編集</a>
                <form action="/delete/{{ product.product_id }}" method="post" style="display:inline">
                    <input type="submit" value="削除">
                </form>
            </td>
        </tr>
        {% endfor %}
    </table>
</body>
</html>

templates/edit.html：

<!DOCTYPE html>
<html lang="ja">
<head>
    <meta charset="UTF-8">
    <title>商品の編集</title>
</head>
<body>
    <h1>商品の編集（ID: {{ product.product_id }}）</h1>

    <!-- 更新内容を /update に送信する -->
    <form action="/update/{{ product.product_id }}" method="post">
        <p>商品名: <input type="text" name="name" value="{{ product.name }}" required></p>
        <p>価格: <input type="number" name="price" value="{{ product.price }}" required></p>
        <p><input type="submit" value="更新"></p>
    </form>

    <p><a href="/">一覧に戻る</a></p>
</body>
</html>

ヒント：編集リンクを押すと /edit/<ID> で現在の値が入った更新フォームが開き、更新ボタンで /update/<ID> に送信される。

考察ポイント：商品を追加・編集・削除した後、一覧表示がどう変化するかを確認する。それぞれの操作がCRUDのどの機能（INSERT・SELECT・UPDATE・DELETE）に対応するかを読み取る。

演習14．データ集計とレポート機能

• 概念・用語：
  • 集計クエリ：GROUP BYと集計関数を組み合わせたデータ集計
  • JSON（JavaScript Object Notation）：データ交換に用いられる軽量なテキスト形式
  • API（Application Programming Interface）：プログラム間でデータをやり取りする仕組み
• 使用ツール：Python + Flask + SQLite（Windowsのローカル環境）

手順：「ツールの使い方」のPython + Flask のローカル実行手順に従う。main.py と、同じフォルダ内の templates フォルダに report.html を作成し、python main.py を実行後、ブラウザで http://localhost:5000 にアクセスする。

main.py：

from flask import Flask, render_template, jsonify
import sqlite3

app = Flask(__name__)

def init_db():
    """データベースを初期化し、サンプルデータを挿入する"""
    conn = sqlite3.connect('sales.db')
    conn.execute('''
        CREATE TABLE IF NOT EXISTS orders (
            order_id INTEGER PRIMARY KEY,
            product_name TEXT NOT NULL,
            quantity INTEGER NOT NULL,
            price INTEGER NOT NULL,
            order_month TEXT NOT NULL
        )
    ''')
    count = conn.execute('SELECT COUNT(*) FROM orders').fetchone()[0]
    if count == 0:
        orders = [
            (1, 'ノートパソコン', 2, 89800, '2023-01'),
            (2, 'マウス', 10, 2980, '2023-01'),
            (3, 'キーボード', 5, 5800, '2023-01'),
            (4, 'ノートパソコン', 3, 89800, '2023-02'),
            (5, 'マウス', 8, 2980, '2023-02'),
            (6, 'モニター', 4, 24800, '2023-02'),
        ]
        conn.executemany('INSERT INTO orders VALUES (?, ?, ?, ?, ?)', orders)
        conn.commit()
    conn.close()

@app.route('/')
def report():
    """売上レポートを表示する"""
    conn = sqlite3.connect('sales.db')
    conn.row_factory = sqlite3.Row

    # 月別売上集計
    monthly = conn.execute('''
        SELECT order_month, SUM(quantity * price) AS total
        FROM orders
        GROUP BY order_month
        ORDER BY order_month
    ''').fetchall()

    # 商品別売上集計
    by_product = conn.execute('''
        SELECT product_name, SUM(quantity) AS qty, SUM(quantity * price) AS total
        FROM orders
        GROUP BY product_name
        ORDER BY total DESC
    ''').fetchall()

    conn.close()
    return render_template('report.html', monthly=monthly, by_product=by_product)

@app.route('/api/sales')
def api_sales():
    """売上データをJSON形式で返す"""
    conn = sqlite3.connect('sales.db')
    conn.row_factory = sqlite3.Row
    rows = conn.execute('''
        SELECT product_name, SUM(quantity * price) AS total
        FROM orders
        GROUP BY product_name
    ''').fetchall()
    conn.close()
    # dict(row)でsqlite3.RowをPythonの辞書に変換
    return jsonify([dict(row) for row in rows])

if __name__ == '__main__':
    init_db()
    app.run(host='127.0.0.1', port=5000, debug=True)

templates/report.html：

<!DOCTYPE html>
<html lang="ja">
<head>
    <meta charset="UTF-8">
    <title>売上レポート</title>
</head>
<body>
    <h1>売上レポート</h1>

    <h2>月別売上</h2>
    <table border="1">
        <tr><th>月</th><th>売上合計</th></tr>
        {% for row in monthly %}
        <tr>
            <td>{{ row.order_month }}</td>
            <td>{{ row.total }}円</td>
        </tr>
        {% endfor %}
    </table>

    <h2>商品別売上</h2>
    <table border="1">
        <tr><th>商品名</th><th>販売数</th><th>売上合計</th></tr>
        {% for row in by_product %}
        <tr>
            <td>{{ row.product_name }}</td>
            <td>{{ row.qty }}</td>
            <td>{{ row.total }}円</td>
        </tr>
        {% endfor %}
    </table>

    <h2>API</h2>
    <p><a href="/api/sales">JSON形式で取得</a></p>
</body>
</html>

ヒント：月別売上は order_month でグループ化し、商品別売上は product_name でグループ化している。SUM(quantity * price) がグループごとの売上合計になる。

考察ポイント：画面の表と、/api/sales が返すJSONを見比べ、同じ集計結果が表とJSONの2つの形式で出力されていることを確認する。

演習15．パフォーマンス最適化とまとめ

• 概念・用語：
  • インデックス：検索を高速化するためのデータ構造。書籍の索引と同様に、目的のデータを効率的に検索できる
  • ビュー：仮想テーブル。クエリを保存して再利用できる
  • 実行計画：クエリがどのように処理されるかを示す情報
• 使用ツール：SQLFiddle

手順：「ツールの使い方」のSQLFiddleでの実行手順に従い、以下のSQLをエディタに貼り付けて「Execute」をクリックする。

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price INTEGER NOT NULL,
    category TEXT NOT NULL
);

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    product_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL,
    order_date TEXT NOT NULL
);

INSERT INTO products VALUES (1, 'ノートパソコン', 89800, 'PC');
INSERT INTO products VALUES (2, 'マウス', 2980, '周辺機器');
INSERT INTO products VALUES (3, 'キーボード', 5800, '周辺機器');
INSERT INTO products VALUES (4, 'モニター', 24800, '周辺機器');
INSERT INTO products VALUES (5, 'USBメモリ', 1280, 'ストレージ');

INSERT INTO orders VALUES (1, 1, 2, '2023-01-15');
INSERT INTO orders VALUES (2, 2, 5, '2023-01-20');
INSERT INTO orders VALUES (3, 1, 1, '2023-02-10');
INSERT INTO orders VALUES (4, 3, 3, '2023-02-15');
INSERT INTO orders VALUES (5, 4, 2, '2023-03-01');

-- インデックスの作成（検索性能の向上）
CREATE INDEX idx_products_price ON products(price);
CREATE INDEX idx_orders_date ON orders(order_date);

-- ビューの作成（クエリの簡素化）
CREATE VIEW order_summary AS
SELECT
    p.name AS product_name,
    p.category,
    SUM(o.quantity) AS total_qty,
    SUM(o.quantity * p.price) AS total_amount
FROM orders o
JOIN products p ON o.product_id = p.product_id
GROUP BY p.product_id;

-- ビューを使用：JOINと集計を簡潔に記述
SELECT * FROM order_summary;

-- インデックスが使用される検索
SELECT * FROM products WHERE price > 5000;

-- カテゴリ別の売上（ビューを使用）
SELECT category, SUM(total_amount) AS category_total
FROM order_summary
GROUP BY category;

-- 実行計画の確認
EXPLAIN QUERY PLAN
SELECT * FROM products WHERE price > 5000;

ヒント：ビューは保存したクエリで、通常のテーブルと同じように SELECT できる。EXPLAIN QUERY PLAN はクエリの処理方法を表示する。

考察ポイント：ビュー order_summary を使ったクエリが、JOINと集計を毎回書かずに済むことを確認する。EXPLAIN QUERY PLAN の出力で、作成したインデックス（idx_products_price）が検索に使われているかを読み取る。