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

概要と学習目標

データベースとは

データベース（DB）は組織的に管理されたデータの集合である。データベース管理システム（DBMS）はデータベースを効率的に管理するソフトウェアで、多数のユーザが同時に安全にデータへアクセスできる環境を提供する。

学習目標

• データベースシステムの基本概念を理解する
• SQLを用いたデータ操作スキルを習得する
• Webアプリケーション開発の基礎を習得する

3つの学習フェーズ

基礎学習フェーズ（第1-10回）

• 使用言語：SQL、HTML
• 使用ツール：SQLFiddle、Replit
• 内容：データベースの基本概念、SQL文法、HTMLの基礎

実践学習フェーズ（第11-13回）

• 使用言語：SQL、Python、HTML
• 使用ツール：Replit、Flask、SQLite
• 内容：Webアプリケーション構築、データベース連携、CRUD操作

応用学習フェーズ（第14-15回）

• 使用言語：Python、SQL、HTML
• 使用ツール：Replit、Flask、SQLite、SQLFiddle
• 内容：データ集計、レポート機能、パフォーマンス最適化

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

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

Replitでのオンライン実行手順（第10-14回で使用）

1. Replit（https://replit.com/）にアクセスし、アカウントにログインする。
2. 「+ Create Repl」ボタンをクリックする。
3. テンプレートを選択する（第10回は「HTML, CSS, JS」、第11-14回は「Python」）。
4. Replに名前を付け、「Create Repl」ボタンをクリックしてプロジェクトを作成する。
5. 左側のファイルツリーで対象ファイルを開き、コードを貼り付ける。
6. テンプレートファイルが必要な場合は、左側のファイルツリーで「+ Add folder」をクリックし、「templates」フォルダを作成してHTMLファイルを配置する。
7. 画面上部の「Run」ボタンをクリックして結果を確認する。

注意：Replitの無料版では、一定時間操作がない場合、アプリケーションが自動的に停止する。再度実行するには「Run」ボタンをクリックする。

ローカル環境での代替実行手順

Replitのアカウント登録を行わない場合、ローカル環境で以下の手順により実行できる。

1. 演習準備の手順に従い、PythonとFlaskをインストールする。
2. 各回のコードをファイルとして保存し、python main.pyで実行する。
3. ブラウザでhttp://localhost:5000にアクセスして結果を確認する。

第1回 データベースの基本

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

実行するSQL：

以下のSQLをすべてエディタに貼り付け、「Execute」をクリックする。

-- 第1回：商品テーブルの作成
-- 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文：全データを取得
SELECT * FROM products;

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

第2回 リレーショナルデータベースとSQL入門

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

実行するSQL：

以下のSQLをすべてエディタに貼り付け、「Execute」をクリックする。

-- 第2回：SQL入門
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;

第3回 データベース設計と正規化

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

実行するSQL：

以下のSQLをすべてエディタに貼り付け、「Execute」をクリックする。

-- 第3回：正規化の学習

-- ▼ 正規化前のテーブル（問題のある設計）
-- 同じ顧客情報（山田太郎）が複数回登場している
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;

第4回 データの検索（WHERE句）

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

実行するSQL：

以下のSQLをすべてエディタに貼り付け、「Execute」をクリックする。

-- 第4回：WHERE句の学習
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;

第5回 SELECT文の応用

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

実行するSQL：

以下のSQLをすべてエディタに貼り付け、「Execute」をクリックする。

-- 第5回：SELECT文応用
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;

第6回 テーブルの結合（JOIN）

• 概念・用語：
  • JOIN：複数のテーブルを関連付けて結合する
  • INNER JOIN：両方のテーブルに存在するデータのみ取得
  • ON句：結合条件を指定する
• 使用ツール：SQLFiddle

実行するSQL：

以下のSQLをすべてエディタに貼り付け、「Execute」をクリックする。

-- 第6回：JOINの学習
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;

第7回 データの集計（GROUP BY）

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

実行するSQL：

以下のSQLをすべてエディタに貼り付け、「Execute」をクリックする。

-- 第7回：集計の学習
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 order_count >= 2;

第8回 副問い合わせ（サブクエリ）

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

実行するSQL：

以下のSQLをすべてエディタに貼り付け、「Execute」をクリックする。

-- 第8回：サブクエリの学習
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);

第9回 主キーと外部キー

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

実行するSQL：

以下のSQLをすべてエディタに貼り付け、「Execute」をクリックする。

-- 第9回：キー制約の学習

-- 親テーブル（主キーを持つ）
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;

-- カテゴリ別の商品数
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;

第10回 HTMLの基礎とフォーム

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

index.html：

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

第11回 PythonとFlask入門

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

main.py：

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='0.0.0.0', port=5000)

第12回 データベースとWebの連携

• 概念・用語：
  • SQLite：ファイルベースの軽量データベース。サーバ不要で使用できる
  • テンプレートエンジン（Jinja2）：HTMLにPythonの変数やロジックを埋め込む仕組み
  • 動的Webページ：データベースの内容に応じて表示が変わるページ
• 使用ツール：Replit（「Python」テンプレートを選択）、Flask、SQLite

ファイル構成：main.pyとtemplatesフォルダ内のproducts.htmlを作成する。

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='0.0.0.0', port=5000)

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>
        <!-- Jinja2テンプレート：{% for %}でループ処理 -->
        {% for product in products %}
        <tr>
            <td>{{ product.product_id }}</td>
            <td>{{ product.name }}</td>
            <td>{{ product.price }}円</td>
        </tr>
        {% endfor %}
    </table>
</body>
</html>

第13回 CRUD操作の実装

• 概念・用語：
  • CRUD：データ操作の4つの基本機能（Create：作成、Read：読取、Update：更新、Delete：削除）
  • POSTメソッド：フォームデータをサーバに送信するHTTPメソッド
  • SQLインジェクション：悪意のあるSQL文を挿入する攻撃手法
  • プレースホルダ（?）：SQLインジェクションを防ぐ安全なパラメータ指定方法
• 使用ツール：Replit（「Python」テンプレートを選択）、Flask、SQLite

ファイル構成：main.pyとtemplatesフォルダ内のindex.htmlを作成する。

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('/delete/')
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='0.0.0.0', port=5000)

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="/delete/{{ product.product_id }}">削除</a></td>
        </tr>
        {% endfor %}
    </table>
</body>
</html>

第14回 データ集計とレポート機能

• 概念・用語：
  • 集計クエリ：GROUP BYと集計関数を組み合わせたデータ集計
  • JSON（JavaScript Object Notation）：データ交換に広く使用される軽量なテキスト形式
  • API（Application Programming Interface）：プログラム間でデータをやり取りする仕組み
• 使用ツール：Replit（「Python」テンプレートを選択）、Flask、SQLite

ファイル構成：main.pyとtemplatesフォルダ内のreport.htmlを作成する。

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='0.0.0.0', port=5000)

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>

第15回 パフォーマンス最適化とまとめ

• 概念・用語：
  • インデックス：検索を高速化するためのデータ構造。本の索引のように、目的のデータを素早く見つけられる
  • ビュー：仮想テーブル。複雑なクエリを保存して再利用できる
  • 実行計画：クエリがどのように処理されるかを示す情報
• 使用ツール：SQLFiddle

実行するSQL：

以下のSQLをすべてエディタに貼り付け、「Execute」をクリックする。

-- 第15回：パフォーマンス最適化
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;