12. SQL の基礎¶

第 12 回では、SQL の基本的な操作方法について学びます。

12.1 SQL とは¶

SQL (Structured Query Language) は、データベースを操作するための最も一般的なプログラミング言語です。基本的な構文は比較的理解しやすく、英語に近い形で書けるため、プログラミング初心者でも学習しやすい言語となっています。

SQL は国際標準化されており、さまざまなデータベース管理システム（DBMS: MySQL、PostgreSQL、Oracle など）で使用することができます。膨大なデータを効率的に扱うことのできる SQL は、実践的なシステム開発において欠かせないスキルとなります。
※ 細かい書き方は DBMS ごとに異なることがあります。

12.1.1 SQL の主な機能¶

データベースにおける基本的な 4 つの操作を表す用語として、CRUD (Create/Read/Update/Delete) があります。

C - Create（作成）

新しいデータを作成・追加する操作
SQL では INSERT 文を使用

R - Read（読み取り）

既存のデータを取得・表示する操作
SQL では SELECT 文を使用

U - Update (更新)

既存のデータを修正・変更する操作
SQL では UPDATE 文を使用

D - Delete（削除）

不要なデータを削除する操作
SQL では DELETE 文を使用

CRUD（クラッド）は、データベース操作に限らず、Web アプリケーションや API 設計全体の基本概念としても広く使われています。

12.1.2 SQLite¶

通常のデータベース（MySQL、PostgreSQL など）は、高性能かつ高機能ですが、サーバのセットアップやセキュリティ設定が複雑で、専門的な知識とスキル、さらにはサーバの維持管理費用が必要となります。一方で、小規模なプロジェクトや個人開発においては、サーバが不要な単一ファイルベースの軽量データベースとして、SQLite が選択されることも多くあります。SQLite は設定不要ですぐに使い始められるため、プロトタイプ開発や学習用途にも適しています。本講義では、SQLite を用いた簡単なデータベース操作について学びます。

12.2 Python によるデータベース操作¶

Python から SQLite のデータベースを操作する基本的な流れを見ていきましょう。

12.2.1 SQLite のインポート¶

SQLite は Python に標準でインストールされているため（モジュール名: sqlite3）、インポートするだけで使用することができます。

import sqlite3

12.2.2 データベースへの接続¶

SQLite データベースを操作するには、まず sqlite3.connect() 関数でデータベースファイルに接続します。このとき、ファイルが存在しない場合は新規作成されます。SQLite の拡張子としては .db, .sqlite, .sqlite3 などが用いられますが、拡張子無しでも動作します。データベースへの接続・操作後は、sqlite3.Connection.close() メソッドで接続を閉じることを忘れないようにしてください。接続を閉じないと、メモリ使用量の増加や、データの損失などの問題が生じることがあります。

import sqlite3

# データベースに接続
conn = sqlite3.connect("practice.db")

# 接続が成功したことを確認
print("データベースに接続しました")

# 接続を閉じる
conn.close()

メモリ上でのデータベース接続

メモリ上でデータベースを使いたい場合は、sqlite3.connect(':memory:') と書きます。メモリ上での操作はディスクへの読み書きが発生しないため、非常に高速な操作が可能となります。しかし、データがディスクに残らないため、プロセス終了と同時にデータが完全に削除されます。このため、試験的なコードの実行や学習用途、機密データの処理には適していますが、永続化が必要なデータには適していません。本講義では、万が一データベースファイルの作成がうまく実行できない場合に、メモリ上でのデータベース接続を検討してください。

12.2.3 カーソルオブジェクトの作成¶

SQLite では、カーソルと呼ばれるオブジェクト（インタフェース）を通じてデータベース操作を実行します。カーソルオブジェクトは、sqlite3.Connection.cursor() メソッドを用いて以下のように作成することができます。

cursor = conn.cursor()

sqlite3.Cursor と sqlite3.Connection オブジェクトの主なメソッド一覧は以下のようになります。SQL 文の実行には Cursor.execute() メソッド、変更の反映には Connection.commit() メソッドという流れを押さえておきましょう。

`sqlite3.Cursor` オブジェクトの主なメソッド¶

メソッド名	説明
`execute(sql, params)`	単一の SQL 文を実行（パラメータを使う場合は`?`プレースホルダを使用）
`executemany(sql, seq)`	複数のデータセットで SQL を繰り返し実行（主に INSERT/UPDATE 時に便利）
`fetchone()`	結果セットから 1 行だけ取得（1 レコード）
`fetchall()`	結果セットから全ての行を取得（全レコード）
`fetchmany(size)`	指定した数の行を取得（部分的に結果を取りたい場合に使用）

`sqlite3.Connection` オブジェクトの主なメソッド¶

メソッド名	説明
`cursor()`	`Cursor` オブジェクトを生成
`commit()`	データベースの変更を反映
`close()`	接続を閉じる（リソースの解放）

12.2.4 テーブルの作成（`CREATE TABLE`）¶

データベースのテーブルを作成してみましょう。ここからいよいよ SQL 文を記述し、Cursor.execute() メソッドで実行していくことになります。

例えば、id（ID：整数型）, item（品名：文字列型）, category（分類：文字列型）, price（価格：整数型）の列を持つ oden というデータベーステーブルを作成する場合、SQL 文は以下のようになります。

CREATE TABLE IF NOT EXISTS oden (
    id INTEGER PRIMARY KEY,
    item TEXT NOT NULL,
    category TEXT,
    price INTEGER
)

テーブルの作成は CREATE TABLE テーブル名 (...) という構文で行います。
IF NOT EXISTS をつけることで、テーブルが存在しない場合にだけ作成を行います。
テーブルの中身は、(列名データの型, ...) の形式で記述します。
INTEGER は整数型、TEXT は文字列型であることを意味します。
PRIMARY KEY は、各行を一意に識別するための列であることを表し、重複する値が存在しないことを保証します。
NOT NULL は、値が入っていること（NULL でないこと）を保証します。

Python で実行する場合は、以下のように Cursor.execute() メソッド内に SQL 文を記述します。

cursor.execute("""
    CREATE TABLE IF NOT EXISTS oden (
        id INTEGER PRIMARY KEY,
        item TEXT,
        category TEXT,
        price INTEGER
    )
""")

データ型の概要¶

SQLite におけるデータ型には大きく以下の 5 つがあります。

INTEGER: 整数
REAL: 浮動小数点数
TEXT: 文字列
BLOB: バイナリデータ
NUMERIC: 日付・時間・真偽値などさまざまな数値

SQLite には厳密な型があるわけではなく、緩く柔軟な特徴を持ちますが、独特な挙動を示すこともあるので注意が必要です。

12.2.5 データの挿入（`INSERT` 文）¶

テーブルを作成したら、データを挿入します。データの挿入は以下の INSERT 文を用います。

INSERT INTO テーブル名 (列1, 列2, ...) VALUES (値1, 値2, ...)

例えば、oden テーブルに (item, category, price) = ("ちくわ", "練り物", 120) のデータを挿入したい場合、以下のようにします。このとき、id には自動的に連番が振られます。

INSERT INTO oden (item, category, price) VALUES ("ちくわ", "練り物", 120)

ただし、このように SQL 文の中に直接データを入力することは、安全性の面から推奨されません。意図しないコマンドが実行される危険性（SQL インジェクション）があるためです。この対策として、プレースホルダ ? を使用し、パラメータを渡す方法が一般的に用いられます。以下は、実際に Python でプレースホルダを使用して Cursor.execute() メソッドでデータを挿入するコード例です。

cursor.execute("INSERT INTO oden (item, category, price) VALUES (?, ?, ?)", ("ちくわ", "練り物", 120))

短い SQL 文であればこのように一行に収めてしまっても問題ありませんが、少し長くなる場合は以下のようにトリプルクォートを使い改行を含めるスタイルにすると、構造がわかりやすくなるためおすすめです。

cursor.execute("""
    INSERT INTO oden (item, category, price)
    VALUES (?, ?, ?)
""", ("ちくわ", "練り物", 120))

データを挿入（または更新）したら、変更を反映させるために Connection.commit() メソッドを呼び出すことを忘れないようにしましょう。ソースコードの最後に一度だけ実行するだけで問題ありません。

conn.commit()

12.2.6 データの取得（`SELECT` 文）¶

データの挿入をしたら、正しくテーブルが更新されているかどうかを確認してみましょう。データベースのテーブルからデータを取得するには、以下のような形で SELECT 文を用います。

SELECT 列名1, 列名2, ... FROM テーブル名

以下では、全項で作成した oden テーブルからデータを取得する例を示します。

すべての列を取得¶

すべての列を取得したい場合は、ワイルドカード * を用いて以下のように書きます。

SELECT * FROM oden

特定の列を取得¶

特定の列（例えば item と price）を取得したい場合は、以下のように書きます。

SELECT item, price FROM oden

取得条件の指定¶

条件を指定してデータを絞り込みたい場合は、WHERE 句を使用します。例えば、価格（price）が 100 より大きいデータのみを抽出したい場合は、以下のように書きます。

SELECT * FROM oden WHERE price > 100

複数の条件を指定したい場合は、AND や OR を使用して条件を組み合わせることができます。

SELECT * FROM oden WHERE price > 100 AND category = "練り物"

ただし、INSERT 文と同様、条件の値を SQL 文に直接書き込むことは推奨されません。Python では以下のように、プレースホルダ ? を使って条件の値を渡すようにしましょう。

cursor.execute("SELECT * FROM oden WHERE price > ? AND category = ?", (100, "練り物"))

データのソート¶

テーブルからデータを取得する際に、特定の列でソートして表示したい場合は、ORDER BY 句を使用します。

データを price の昇順で表示したい場合は、以下のように ASC キーワードを使用します。

SELECT * FROM oden ORDER BY price ASC

降順の場合は、DESC キーワードを使用します。

SELECT * FROM oden ORDER BY price DESC

Python での `SELECT` 文の実行¶

Python で SELECT 文を実行してデータを取得したい場合は、これまでと同様 Cursor.execute() メソッドで SQL 文を実行した後、Cursor.fetchall() メソッド（または Cursor.fetchone(), Cursor.fetchmany(n)）でデータをフェッチ（取得）します。

以下は、oden テーブルからすべてのデータを取得して表示するコード例です。

cursor.execute("SELECT * FROM oden")
rows = cursor.fetchall()
print(rows)

Output

[(1, 'ちくわ', '練り物', 120)]

なお、Cursor.fetchall() は結果をタプルのリストとして返し、Cursor.fetchone() は先頭の 1 行をタプルとして返します。条件に一致するデータが存在しない場合、Cursor.fetchone() は None を返すため、「データが見つかったかどうか」の判定に利用することができます。

cursor.execute("SELECT * FROM oden WHERE item = ?", ("しらたき",))
row = cursor.fetchone()
print(row)

Output

None

12.2.7 データの更新（`UPDATE` 文）¶

既存のデータを更新したい場合は、UPDATE 文を使用します。UPDATE 文の基本構文は以下になります。

UPDATE テーブル名
SET 列1 = 新しい値1, 列2 = 新しい値2, ...
WHERE 条件

Python で oden テーブルの id が 1 のデータを対象に、price を更新したい場合は、以下のようにします。

cursor.execute("""
    UPDATE oden
    SET price = ?
    WHERE id = ?
""", (150, 1))

また、SET price = price + 10 のように、SET 句では現在の値を使った計算を行うこともできます。以下は、「練り物」カテゴリの具材をまとめて 10 円値上げする例です。

cursor.execute("""
    UPDATE oden
    SET price = price + 10
    WHERE category = ?
""", ("練り物",))

12.2.8 データの削除（`DELETE` 文）¶

データベースから不要なデータを削除したい場合は、DELETE 文を使用します。DELETE 文の基本構文は以下になります。

DELETE FROM テーブル名 WHERE 条件

Python で oden テーブルの id が 1 のデータを削除したい場合は、以下のようにします。

cursor.execute("""
    DELETE FROM oden
    WHERE id = ?
""", (1,))

ここで、プレースホルダ ? に渡すデータはタプルである必要があるため、(1) ではなく (1,) としています。

DELETE 文を使う場合の注意

DELETE 文を使う場合は、必ず WHERE 句を使用するようにしてください。WHERE 句を指定しないと、テーブル内のすべてのデータが削除されます。また、間違って重要なデータを削除してしまわないように、データのバックアップを取る習慣をつけましょう。

12.2.9 テーブルの削除（`DROP TABLE`）¶

テーブル自体を削除したい場合は、DROP TABLE という SQL 文を書きます。

DROP TABLE テーブル名

Python で oden テーブルを削除したい場合は、以下のようにします。

cursor.execute("DROP TABLE oden")

なお、存在しないテーブルを削除しようとするとエラーになります。CREATE TABLE のときの IF NOT EXISTS と同様に、DROP TABLE IF EXISTS テーブル名 と書くことで、テーブルが存在する場合にだけ削除を行うことができます。

12.2.10 Python によるデータベース操作のまとめ¶

以下は、Python で SQLite のデータベースに接続し、CRUD の一連の動作を行うコード例です。データベースがどのような流れで作成・変更され、どのような結果が出力されるか、考えてみましょう。

import sqlite3

# データベースに接続
conn = sqlite3.connect("practice.db")

# カーソルオブジェクトの作成
cursor = conn.cursor()

# oden テーブルの作成
cursor.execute("""
    CREATE TABLE IF NOT EXISTS oden (
        id INTEGER PRIMARY KEY,
        item TEXT NOT NULL,
        category TEXT,
        price INTEGER
    )
""")

# データの挿入（executemany で一括挿入も可能）
cursor.execute("""
    INSERT INTO oden (item, category, price)
    VALUES (?, ?, ?)
""", ("ちくわ", "練り物", 120))

cursor.execute("""
    INSERT INTO oden (item, category, price)
    VALUES (?, ?, ?)
""", ("だいこん", "野菜", 100))

cursor.execute("""
    INSERT INTO oden (item, category, price)
    VALUES (?, ?, ?)
""", ("たまご", "たまご", 90))

# データの更新
cursor.execute("""
    UPDATE oden
    SET price = ?
    WHERE item = ?
""", (100, "たまご"))

# データの削除
cursor.execute("""
    DELETE FROM oden
    WHERE id = ?
""", (2,))

# データの取得
cursor.execute("SELECT * FROM oden")
rows = cursor.fetchall()

# データの表示
for row in rows:
    print(f"{row[0]}: {row[1]}（{row[2]}）{row[3]}円")

# 変更を保存（練習中はコメントアウトでもOK）
conn.commit()

# 接続を閉じる
conn.close()

このコードでは INSERT 文を 3 回実行していますが、Cursor.executemany() メソッドを用いることで以下のようにデータを一括で挿入することもできます。

oden_items = [("ちくわ", "練り物", 120), ("だいこん", "野菜", 100), ("たまご", "たまご", 90)]
cursor.executemany("INSERT INTO oden (item, category, price) VALUES (?, ?, ?)", oden_items)

また、Connection.commit() メソッドは変更を保存する重要な処理ですが、INSERT 文の練習中は実行するたびにデータが増えていってしまうため、必要に応じてコメントアウトすることを検討してください。

演習¶

以下の演習では、おでん屋さんのメニュー管理・注文管理を題材に、SQLite によるデータベース操作を練習します。演習 12-1 で作成するデータベース（oden.db）を、以降の演習でも引き続き使用します。

演習 12-1（初級）：おでん屋のメニュー登録

おでん屋さんのメニューをデータベースに登録します。id（整数型）、item（文字列型）、category（文字列型）、price（整数型）の列を持つ oden テーブルを作成し、以下の 6 つの具材を登録して、全データを表示してください。

item	category	price
ちくわ	練り物	120
だいこん	野菜	100
たまご	たまご	90
はんぺん	練り物	110
つみれ	練り物	130
かまぼこ	練り物	140

oden テーブルを作成する（id は PRIMARY KEY）
executemany() で 6 つの具材を一括で挿入する
SELECT 文で全データを取得して表示する

テンプレートコード

import sqlite3

conn = sqlite3.connect("oden.db")
cursor = conn.cursor()

# 再実行してもデータが増えないように、テーブルを作り直す
cursor.execute("DROP TABLE IF EXISTS oden")

# 1. oden テーブルを作成する

# 2. executemany() で 6 つの具材を一括で挿入する

# 3. SELECT 文で全データを取得して表示する

conn.commit()
conn.close()

演習 12-2（初級）：メニューの絞り込みと並べ替え

お客さんに見せる「安い順のメニュー表」と「練り物だけのメニュー表」を作ります。演習 12-1 で作成した oden.db に接続し、以下の 2 つの結果を表示してください。

全メニューを価格の安い順（昇順）に表示する（ORDER BY 句）
カテゴリが「練り物」の具材だけを表示する（WHERE 句とプレースホルダ ? を使用）

テンプレートコード

import sqlite3

conn = sqlite3.connect("oden.db")
cursor = conn.cursor()

# 1. 全メニューを価格の安い順に取得して表示する

# 2. カテゴリが「練り物」の具材だけを取得して表示する

conn.close()

演習 12-3（中級）：値上げと売り切れの反映

原材料の高騰と売り切れをメニューに反映させます。演習 12-1 で作成した oden.db に対して、以下の操作を行ってください。

「練り物」カテゴリの具材をすべて 10 円値上げする（UPDATE 文）
売り切れた「はんぺん」をメニューから削除する（DELETE 文）
全データを表示して、正しく反映されたことを確認する

テンプレートコード

import sqlite3

conn = sqlite3.connect("oden.db")
cursor = conn.cursor()

# 1. 練り物カテゴリの具材をすべて 10 円値上げする

# 2. はんぺんをメニューから削除する

# 3. 全データを表示して確認する

conn.commit()
conn.close()

演習 12-4（上級）：おでんの注文会計プログラム

お客さんの注文を受けて、会計（レシート）を表示するプログラムを作成してください。注文リスト orders には（具材名, 個数）のタプルが入っています。演習 12-3 で更新した oden.db のメニューから各具材の価格を調べて、以下のようなレシートを表示してください。

注文リストの各具材について、SELECT 文（プレースホルダ使用）と fetchone() で価格を取得する
メニューにある具材は「価格 × 個数」の小計を表示し、合計金額に加算する
メニューにない具材（売り切れの「はんぺん」など）は「在庫がありません」と表示する
最後に合計金額を表示する

Output

ちくわ 130円 × 2 = 260円
だいこん 100円 × 3 = 300円
はんぺん は在庫がありません
たまご 90円 × 1 = 90円
合計: 650円

テンプレートコード

import sqlite3

conn = sqlite3.connect("oden.db")
cursor = conn.cursor()

# 注文リスト（具材名, 個数）
orders = [("ちくわ", 2), ("だいこん", 3), ("はんぺん", 1), ("たまご", 1)]

total = 0

# 1〜3. 注文リストの各具材について価格を調べ、小計を表示・合計に加算する

# 4. 合計金額を表示する

conn.close()

12. SQL の基礎¶

12.1 SQL とは¶

12.1.1 SQL の主な機能¶

12.1.2 SQLite¶

12.2 Python によるデータベース操作¶

12.2.1 SQLite のインポート¶

12.2.2 データベースへの接続¶

12.2.3 カーソルオブジェクトの作成¶

sqlite3.Cursor オブジェクトの主なメソッド¶

sqlite3.Connection オブジェクトの主なメソッド¶

12.2.4 テーブルの作成（CREATE TABLE）¶

データ型の概要¶

12.2.5 データの挿入（INSERT 文）¶

12.2.6 データの取得（SELECT 文）¶

すべての列を取得¶

特定の列を取得¶

取得条件の指定¶

データのソート¶

Python での SELECT 文の実行¶

12.2.7 データの更新（UPDATE 文）¶

12.2.8 データの削除（DELETE 文）¶

12.2.9 テーブルの削除（DROP TABLE）¶

12.2.10 Python によるデータベース操作のまとめ¶

演習¶

`sqlite3.Cursor` オブジェクトの主なメソッド¶

`sqlite3.Connection` オブジェクトの主なメソッド¶

12.2.4 テーブルの作成（`CREATE TABLE`）¶

12.2.5 データの挿入（`INSERT` 文）¶

12.2.6 データの取得（`SELECT` 文）¶

Python での `SELECT` 文の実行¶

12.2.7 データの更新（`UPDATE` 文）¶

12.2.8 データの削除（`DELETE` 文）¶

12.2.9 テーブルの削除（`DROP TABLE`）¶