Week 7 SQL

ようこそ！

• これまでの数週間で、C言語で学んだものと同じ構成要素を利用する高レベルプログラミング言語であるPythonを紹介しました。しかし、この新しい言語を導入したのは、単に「別の言語」を学ぶためではありません。あるツールは特定の仕事には適していますが、他の仕事にはあまり適していないことがあるからです。

• 今週は、Pythonに関連するさらなる構文について進めていきます。

• さらに、この知識をデータと統合していきます。

• 最後に、データとのやり取りやデータの変更を行うためのドメイン固有の方法である、SQL（Structured Query Language、構造化クエリ言語）について説明します。

• 全体として、このコースの目標の1つは、単にこのコースで説明されている言語でプログラミングする方法だけでなく、プログラミング全般を学ぶことです。

フラットファイルデータベース

• すでにご存知のように、データは列（column）と行（row）のパターンで記述されることがよくあります。

• Microsoft ExcelやGoogleスプレッドシートで作成されたようなスプレッドシートは、csv（comma-separated values、カンマ区切り値）ファイルとして出力できます。

• csvファイルを見ると、すべてのデータがテキストファイルで表される単一のテーブルに保存されているという意味で、ファイルが平坦（フラット）であることに気づくでしょう。このようなデータの形式をフラットファイルデータベースと呼びます。

• すべてのデータは行ごとに保存されます。各列はカンマまたは別の値で区切られます。

• Pythonにはcsvファイルのネイティブサポートが備わっています。

• まず、favorites.csvをダウンロードし、cs50.dev内のファイルエクスプローラーにアップロードしてください。次に、このデータを確認すると、最初の行が各列を定義しているという点で特別であることに気づくでしょう。その後、各レコードが行ごとに保存されています。

ターミナルウィンドウでcode favorites.pyと入力し、次のようにコードを記述します。

# Prints all favorites in CSV using csv.reader

import csv

# Open CSV file
with open("favorites.csv", "r") as file:

    # Create reader
    reader = csv.reader(file)

    # Skip header row
    next(reader)

    # Iterate over CSV file, printing each favorite
    for row in reader:
        print(row[1])

csvライブラリがインポートされていることに注目してください。さらに、csv.reader(file)の結果を保持するreaderを作成しました。csv.reader関数はファイルから各行を読み取り、コードではその結果をreaderに保存します。したがって、print(row[1])はfavorites.csvファイルからプログラミング言語を出力します。

コードを次のように改善できます。

# Stores favorite in a variable

import csv

# Open CSV file
with open("favorites.csv", "r") as file:

    # Create reader
    reader = csv.reader(file)

    # Skip header row
    next(reader)

    # Iterate over CSV file, printing each favorite
    for row in reader:
        favorite = row[1]
        print(favorite)

favoriteが保存され、その後出力されることに注目してください。また、next関数を使用してリーダーの次の行にスキップしていることにも注目してください。

• 上記のアプローチの欠点の1つは、row[1]が常に「お気に入り（favorite）」であると信頼していることです。しかし、列が入れ替わっていたらどうなるでしょうか。

この潜在的な問題を修正できます。Pythonでは、リストのキー（見出し）でインデックスを付けることもできます。コードを次のように修正します。

# Prints all favorites in CSV using csv.DictReader

import csv

# Open CSV file
with open("favorites.csv", "r") as file:

    # Create DictReader
    reader = csv.DictReader(file)

    # Iterate over CSV file, printing each favorite
    for row in reader:
        favorite = row["language"]
        print(favorite)

この例では、print文でlanguageキーを直接利用していることに注目してください。favoriteは、row["language"]というreaderの辞書形式にインデックスを付けています。

これはさらに次のように簡略化できます。

# Prints all favorites in CSV using csv.DictReader

import csv

# Open CSV file
with open("favorites.csv", "r") as file:

    # Create DictReader
    reader = csv.DictReader(file)

    # Iterate over CSV file, printing each favorite
    for row in reader:
        print(row["language"])

csvファイルで表明されたお気に入りの言語の数を数えるには、次のように行います。

# Counts favorites using variables

import csv

# Open CSV file
with open("favorites.csv", "r") as file:

    # Create DictReader
    reader = csv.DictReader(file)

    # Counts
    scratch, c, python = 0, 0, 0

    # Iterate over CSV file, counting favorites
    for row in reader:
        favorite = row["language"]
        if favorite == "Scratch":
            scratch += 1
        elif favorite == "C":
            c += 1
        elif favorite == "Python":
            python += 1

# Print counts
print(f"Scratch: {scratch}")
print(f"C: {c}")
print(f"Python: {python}")

各言語がif文を使用してカウントされていることに注目してください。さらに、それらのif文内の二重等号==にも注目してください。

Pythonでは、辞書を使用して各言語のcountsを数えることができます。コードの改善案を考えてみましょう。

# Counts favorites using dictionary

import csv

# Open CSV file
with open("favorites.csv", "r") as file:

    # Create DictReader
    reader = csv.DictReader(file)

    # Counts
    counts = {}

    # Iterate over CSV file, counting favorites
    for row in reader:
        favorite = row["language"]
        if favorite in counts:
            counts[favorite] += 1
        else:
            counts[favorite] = 1

# Print counts
for favorite in counts:
    print(f"{favorite}: {counts[favorite]}")

すでに存在する場合、counts内のキーfavoriteの値がインクリメントされることに注目してください。存在しない場合は、counts[favorite]を定義して1に設定します。さらに、フォーマット済み文字列は、counts[favorite]を表示するように改善されています。

Pythonでは、countsをソート（並べ替え）することもできます。コードを次のように改善します。

# Sorts favorites by key

import csv

# Open CSV file
with open("favorites.csv", "r") as file:

    # Create DictReader
    reader = csv.DictReader(file)

    # Counts
    counts = {}

    # Iterate over CSV file, counting favorites
    for row in reader:
        favorite = row["language"]
        if favorite in counts:
            counts[favorite] += 1
        else:
            counts[favorite] = 1

# Print counts
for favorite in sorted(counts):
    print(f"{favorite}: {counts[favorite]}")

コードの下部にあるsorted(counts)に注目してください。

Pythonドキュメントでsorted関数のパラメータを調べると、多くの組み込みパラメータがあることがわかります。次のように、これらの組み込みパラメータの一部を活用できます。

# Sorts favorites by value using .get

import csv

# Open CSV file
with open("favorites.csv", "r") as file:

    # Create DictReader
    reader = csv.DictReader(file)

    # Counts
    counts = {}

    # Iterate over CSV file, counting favorites
    for row in reader:
        favorite = row["language"]
        if favorite in counts:
            counts[favorite] += 1
        else:
            counts[favorite] = 1

# Print counts
for favorite in sorted(counts, key=counts.get, reverse=True):
    print(f"{favorite}: {counts[favorite]}")

sortedに渡される引数に注目してください。key引数を使用すると、項目のソートに使用したいメソッドをPythonに伝えることができます。この場合、値でソートするためにcounts.getが使用されています。reverse=Trueは、大きいものから小さい順にソートするようにsortedに伝えます。

Pythonには、コードで利用できる多数のライブラリがあります。これらのライブラリの1つにcollectionsがあり、そこからCounterをインポートできます。Counterを使用すると、これまでのコードで見られたような煩わしいif文をすべて使わずに、各言語のカウントにアクセスできます。次のように実装できます。

# Sorts favorites by value using .get

import csv

from collections import Counter

# Open CSV file
with open("favorites.csv", "r") as file:

    # Create DictReader
    reader = csv.DictReader(file)

    # Counts
    counts = Counter()

    # Iterate over CSV file, counting favorites
    for row in reader:
        favorite = row["language"]
        counts[favorite] += 1

# Print counts
for favorite, count in counts.most_common():
    print(f"{favorite}: {count}")

counts = Counter()によって、collectionsからインポートされたこのCounterクラスを使用できるようになったことに注目してください。

• Pythonドキュメントのsortedについて詳しく学ぶことができます。

リレーショナルデータベース

• Google、X、Metaはすべて、大規模な情報を保存するためにリレーショナルデータベースを使用しています。

• リレーショナルデータベースは、テーブルと呼ばれる構造の中に、行と列の形式でデータを保存します。

SQLでは、4種類のコマンドを使用できます。

  Create
  Read
  Update
  Delete

• これら4つの操作は、親しみを込めてCRUDと呼ばれます。

• SQLの構文CREATE TABLE table (column type, ...);を使用してデータベースを作成できます。しかし、このコマンドはどこで実行するのでしょうか。

sqlite3は、このコースで必要な主要機能を備えたSQLデータベースの一種です。

• ターミナルでsqlite3 favorites.dbと入力することで、SQLデータベースを作成できます。プロンプトが表示されたら、yを押してfavorites.dbを作成することに同意します。

• 現在はsqliteというプログラムを使用しているため、プロンプトが異なっていることに気づくでしょう。

• .mode csvと入力することで、sqliteをcsvモードに切り替えることができます。次に、.import favorites.csv favoritesと入力することで、csvファイルからデータをインポートできます。何も起こっていないように見えます！

• .schemaと入力すると、データベースの構造を確認できます。

• SELECT columns FROM tableという構文を使用して、テーブルから項目を読み取ることができます。

• 例えば、SELECT * FROM favorites;と入力すると、favoritesのすべての行が出力されます。

• SELECT language FROM favorites;というコマンドを使用して、データのサブセットを取得できます。

SQLは、次のようなデータにアクセスするための多くのコマンドをサポートしています。

  AVG
  COUNT
  DISTINCT
  LOWER
  MAX
  MIN
  UPPER

• 例えば、SELECT COUNT(*) FROM favorites;と入力できます。さらに、SELECT DISTINCT language FROM favorites;と入力すると、データベース内の個々の言語のリストを取得できます。SELECT COUNT(DISTINCT language) FROM favorites;と入力して、それらのカウントを取得することもできます。

SQLには、クエリで利用できる追加のコマンドがあります。

  WHERE       -- データをフィルタリングするためのブール式を追加する
  LIKE        -- より緩やかにレスポンスをフィルタリングする
  ORDER BY    -- レスポンスを並べ替える
  LIMIT       -- レスポンスの数を制限する
  GROUP BY    -- レスポンスをグループ化する

SQLでコメントを書くには--を使用することに注目してください。

SELECT

• 例えば、SELECT COUNT(*) FROM favorites WHERE language = 'C';を実行できます。カウントが表示されます。

• さらに、SELECT COUNT(*) FROM favorites WHERE language = 'C' AND problem = 'Hello, World';と入力することもできます。結果を絞り込むためにANDがどのように利用されているかに注目してください。

• 同様に、SELECT language, COUNT(*) FROM favorites GROUP BY language;を実行することもできます。これにより、言語とカウントを示す一時的なテーブルが表示されます。

• SELECT language, COUNT(*) FROM favorites GROUP BY language ORDER BY COUNT(*);と入力することで、これを改善できます。これにより、結果のテーブルがcount順に並べ替えられます。

• 同様に、SELECT COUNT(*) FROM favorites WHERE language = 'C' AND (problem = 'Hello, World' OR problem = 'Hello, It''s Me');を実行できます。SQLを混乱させない方法でシングルクォートを使用できるように、''という2つのマークがあることに注意してください。

• さらに、SELECT COUNT(*) FROM favorites WHERE language = 'C' AND problem LIKE 'Hello, %';を実行して、Hello, （スペースを含む）で始まる問題を検索できます。

• SELECT language, COUNT(*) FROM favorites GROUP BY language;を実行することで、各言語の値をグループ化することもできます。

• 次のように出力を並べ替えることができます：SELECT language, COUNT(*) FROM favorites GROUP BY language ORDER BY COUNT(*) DESC;。

• クエリ内で変数のようにな別名（エイリアス）を作成することもできます：SELECT language, COUNT(*) AS n FROM favorites GROUP BY language ORDER BY n DESC;。

• 最後に、出力を1つ以上の値に制限できます：SELECT language, COUNT(*) AS n FROM favorites GROUP BY language ORDER BY n DESC LIMIT 1;。

INSERT

• INSERT INTO table (column...) VALUES(value, ...);という形式を利用して、SQLデータベースにINSERT（挿入）することもできます。

• INSERT INTO favorites (language, problem) VALUES ('SQL', 'Fiftyville');を実行できます。

• SELECT * FROM favorites;を実行することで、このお気に入りの追加を確認できます。

DELETE

DELETEを使用すると、データの一部を削除できます。例えば、DELETE FROM favorites WHERE Timestamp IS NULL;を実行できます。これにより、TimestampがNULLであるすべてのレコードが削除されます。

UPDATE

• データの更新にはUPDATEコマンドを利用することもできます。

• 例えば、UPDATE favorites SET language = 'SQL', problem = 'Fiftyville';を実行できます。これにより、CやScratchがお気に入りのプログラミング言語であった以前の記述がすべて上書きされます。

• これらのクエリには絶大な力があることに注意してください。したがって、現実の世界では、誰が特定のコマンドを実行する権限を持っているか、バックアップが利用可能かどうかを考慮する必要があります。

IMDb

• テレビ番組をカタログ化するために作成したいデータベースを想像してみましょう。title、star、star、star、star、そしてさらに多くのスターといった列を持つスプレッドシートを作成することもできます。このアプローチの問題は、無駄なスペースがたくさんあることです。スターが1人の番組もあれば、数十人の番組もあるでしょう。

• データベースを複数のシートに分けることもできます。showsシート、starsシート、peopleシートを持つことができます。peopleシートでは、各人が一意のidを持つことができます。showsシートでも、各番組が一意のidを持つことができます。starsと呼ばれる3番目のシートでは、show_idとperson_idを持つことで、各番組にどのような人物が出演しているかを関連付けることができます。これは改善されていますが、理想的なデータベースではありません。

IMDbは、人物、番組、脚本家、スター、ジャンル、評価のデータベースを提供しています。これらの各テーブルは、次のように相互に関連付けられています。

• shows.dbをダウンロードした後、ターミナルウィンドウでsqlite3 shows.dbを実行できます。

データベース内のshowsとratingsという2つのテーブルの関係に注目してみましょう。これら2つのテーブルの関係は次のように説明できます。

• これらのテーブル間の関係を説明するために、次のコマンドを実行できます：SELECT * FROM ratings LIMIT 10;。出力を確認し、SELECT * FROM shows LIMIT 10;を実行します。

• showsとratingsを確認すると、これらが1対1の関係にあることがわかります。つまり、1つの番組には1つの評価があります。

• データベースを理解するために、.schemaを実行すると、各テーブルだけでなく、それぞれのテーブル内の個々のフィールドも見つかります。

• 具体的には、shows内のフィールドを理解するために.schema showsを実行できます。また、ratings内のフィールドを確認するために.schema ratingsを実行することもできます。

• ご覧のとおり、show_idはすべてのテーブルに存在します。showsテーブルでは、単にidと呼ばれています。すべてのフィールド間で共通するこのフィールドは、キー（鍵）と呼ばれます。主キー（Primary keys）は、テーブル内の一意のレコードを識別するために使用されます。外部キー（Foreign keys）は、別のテーブルの主キーを指すことで、テーブル間の関係を構築するために使用されます。ratingsのスキーマを見ると、show_idがshowsのidを参照する外部キーであることがわかります。

• 上記のようにリレーショナルデータベースにデータを保存することで、データをより効率的に保存できます。

sqliteには、次の5つのデータ型があります。

  BLOB       -- 1と0のグループであるバイナリラージオブジェクト
  INTEGER    -- 整数
  NUMERIC    -- 日付のように特別にフォーマットされた数値用
  REAL       -- 浮動小数点数（float）のようなもの
  TEXT       -- 文字列など

さらに、列に特別な制約を設定することもできます。

  NOT NULL
  UNIQUE

• これらの関係を理解するために、さらにこのデータを操作してみましょう。SELECT * FROM ratings;を実行します。たくさんの評価があります！

• SELECT show_id FROM ratings WHERE rating >= 6.0 LIMIT 10;を実行することで、このデータをさらに絞り込むことができます。このクエリから、10の番組が表示されていることがわかります。しかし、それぞれのshow_idがどの番組を表しているかはわかりません。

• SELECT * FROM shows WHERE id = 626124;を実行することで、これらがどのような番組であるかを発見できます。

次のように実行することで、クエリをさらに効率的にできます。

SELECT title
FROM shows
WHERE id IN (
    SELECT show_id
    FROM ratings
    WHERE rating >= 6.0
    LIMIT 10
)

このクエリが2つのクエリを入れ子（ネスト）にしていることに注目してください。内部クエリが外部クエリによって使用されています。

JOIN

• showsとratingsからデータを取得しています。showsとratingsの両方が共通のidを持っていることに注目してください。

• テーブルを一時的に結合するにはどうすればよいでしょうか。テーブルはJOINコマンドを使用して結合できます。

次のコマンドを実行します。

SELECT * FROM shows
  JOIN ratings on shows.id = ratings.show_id
  WHERE rating >= 6.0
  LIMIT 10;

これにより、これまで見てきたものよりも幅の広いテーブルが表示されることに注目してください。

これまでのクエリがこれらのキー間の1対1の関係を示していたのに対し、いくつかの1対多の関係を調べてみましょう。genresテーブルに注目して、以下を実行します。

SELECT * FROM genres
LIMIT 10;

これにより、生データの感覚がつかめることに注目してください。1つの番組が3つの値を持っていることに気づくかもしれません。これは1対多の関係です。

• .schema genresと入力することで、genresテーブルについて詳しく知ることができます。

データベース内のさまざまなコメディについて知るために、次のコマンドを実行します。

SELECT title FROM shows
WHERE id IN (
  SELECT show_id FROM genres
  WHERE genre = 'Comedy'
  LIMIT 10
);

これにより、Catweazleを含むコメディのリストが生成されることに注目してください。

join（結合）を通じてさまざまなテーブルを結合することで、Catweazleについて詳しく知ることができます。

SELECT * FROM shows
JOIN genres
ON shows.id = genres.show_id
WHERE id = 63881;

これにより一時的なテーブルが作成されることに注目してください。重複するテーブルがあっても問題ありません。

• 1対1や1対多の関係とは対照的に、多対多の関係がある場合もあります。

次のコマンドを実行することで、番組『ジ・オフィス（The Office）』とその番組の出演者について詳しく知ることができます。

SELECT name FROM people WHERE id IN
    (SELECT person_id FROM stars WHERE show_id =
        (SELECT id FROM shows WHERE title = 'The Office' AND year = 2005));

これにより、入れ子になったクエリを通じてさまざまなスターの名前を含むテーブルが表示されることに注目してください。

スティーヴ・カレル（Steve Carell）が出演したすべての番組を見つけます。

SELECT title FROM shows WHERE id IN
    (SELECT show_id FROM stars WHERE person_id =
        (SELECT id FROM people WHERE name = 'Steve Carell'));

これにより、スティーヴ・カレルが出演した番組のタイトルのリストが表示されます。

これは次のように表現することもできます。

SELECT title FROM shows, stars, people
WHERE shows.id = stars.show_id
AND people.id = stars.person_id
AND name = 'Steve Carell';

• ワイルドカードの%演算子を使用して、名前がSteve Cで始まるすべての人を見つけることができます。SELECT * FROM people WHERE name LIKE 'Steve C%';という構文を使用します。

インデックス

• リレーショナルデータベースはCSVファイルを利用するよりも高速で堅牢ですが、テーブル内のデータはインデックス（索引）を使用して最適化できます。

• インデックスを利用してクエリを高速化できます。

• sqlite3で.timer onを実行することで、クエリの速度を追跡できます。

• インデックスがどのようにクエリを高速化できるかを理解するために、次を実行してください：SELECT * FROM shows WHERE title = 'The Office'; クエリ実行後に表示される時間に注目してください。

• 次に、CREATE INDEX title_index ON shows (title);という構文でインデックスを作成できます。これは、sqlite3に対してインデックスを作成し、このtitle列に関連する特別な内部最適化を実行するように伝えます。

これにより、二分木に似たデータ構造であるB木（B Tree）が作成されます。ただし、二分木とは異なり、3つ以上の子ノードを持つことができます。

さらに、次のようにインデックスを作成できます。

CREATE INDEX name_index ON people (name);
CREATE INDEX person_index ON stars (person_id);

クエリを実行すると、クエリが非常に速く実行されることに気づくでしょう！

SELECT title FROM shows WHERE id IN
    (SELECT show_id FROM stars WHERE person_id =
        (SELECT id FROM people WHERE name = 'Steve Carell'));

• 残念ながら、すべての列にインデックスを付けると、より多くのストレージ容量を消費することになります。したがって、速度の向上とのトレードオフがあります。

PythonでSQLを使用する

このコースでSQLを扱うのを支援するために、コード内で次のようにCS50ライブラリを利用できます。

from cs50 import SQL

• CS50ライブラリの以前の使用と同様に、このライブラリはPythonコード内でSQLを利用する複雑なステップを支援します。

• ドキュメントでCS50ライブラリのSQL機能について詳しく読むことができます。

• SQLの新しい知識を使用して、Pythonと並行して活用できるようになりました。

favorites.pyのコードを次のように修正します。

# Searches database popularity of a problem

from cs50 import SQL

# Open database
db = SQL("sqlite:///favorites.db")

# Prompt user for favorite
favorite = input("Favorite: ")

# Search for title
rows = db.execute("SELECT COUNT(*) AS n FROM favorites WHERE language = ?", favorite)

# Get first (and only) row
row = rows[0]

# Print popularity
print(row["n"])

db = SQL("sqlite:///favorites.db")がPythonにデータベースファイルの場所を教えていることに注目してください。次に、rowsで始まる行がdb.executeを利用してSQLコマンドを実行します。実際、このコマンドは引用符内の構文をdb.execute関数に渡します。この構文を使用して、任意のSQLコマンドを発行できます。さらに、rowsが辞書のリストとして返されることに注目してください。この場合、結果は1つだけ（1行）なので、rowsリストに辞書として1つの行が返されます。

レースコンディション

• SQLの利用により、時として問題が発生することがあります。

• 複数のユーザーが同じデータベースにアクセスし、同時にコマンドを実行しているケースを想像してみてください。

• これにより、コードが他の人のアクションによって中断されるといった不具合が生じる可能性があります。その結果、データが失われる可能性があります。

• BEGIN TRANSACTION、COMMIT、ROLLBACKといった組み込みのSQL機能は、これらのレースコンディション（競合状態）の問題の一部を回避するのに役立ちます。

SQLインジェクション攻撃

• さて、上記のコードをまだ考慮していると、上記の?（疑問符）が何をしているのか不思議に思うかもしれません。SQLの現実世界のアプリケーションで発生する可能性のある問題の1つは、インジェクション攻撃と呼ばれるものです。インジェクション攻撃とは、悪意のある攻撃者が悪意のあるSQLコードを入力することです。

例えば、次のようなログイン画面を考えてみましょう。

自身のコードに適切な保護がない場合、悪意のあるアクターが悪意のあるコードを実行する可能性があります。次を考えてみましょう。

rows = db.execute("SELECT COUNT(*) FROM users WHERE username = ? AND password = ?", username, password)

?が配置されているため、クエリによって盲目的に受け入れられる前に、favoriteに対して検証（バリデーション）を実行できることに注目してください。

• クエリで上記のようなフォーマット済み文字列を使用したり、ユーザーの入力を盲目的に信頼したりしてはいけません。

• CS50ライブラリを利用すると、ライブラリは潜在的に悪意のある文字をサニタイズ（無害化）して削除します。

まとめ

このレッスンでは、Pythonに関連するさらなる構文を学びました。さらに、この知識をフラットファイルやリレーショナルデータベースの形式でデータと統合する方法を学びました。最後に、SQLについて学びました。具体的には、以下について議論しました。

• フラットファイルデータベース

• リレーショナルデータベース

• SELECT、CREATE、INSERT、DELETE、UPDATEなどのSQLコマンド

• 主キーと外部キー

• JOIN

• インデックス

• PythonでのSQLの使用

• レースコンディション

• SQLインジェクション攻撃

また次回お会いしましょう！