早いものでもう第7週目に突入。前回は人気言語Pythonの講義だったが、今週はSQLというデータベース言語を学ぶ。SQLはデータベースを管理するソフトウェアを操作・制御することが目的なので、プログラミング言語ではないのでシステムを開発することはできない。代わりにSQLの構文を他の言語に組み込むことが可能で、そうすることでデータベースの操作が容易にかつ素早くできるようになる。どのサービスを今後展開していくにしても、データベースは必須なので必要な講義と言えそうだ。

　

Data processing

講義は、Google formを使って生徒からお気に入りのテレビ番組とそのジャンルを入力を求め、その結果がどうなるかを詳細に説明していく。ある人は、同じテレビ番組に対して一つのジャンルを選択しているのに対して、複数を選択している人がいたり、データ構造がめちゃくちゃな様子を解説する。

Google Sheets、Apple’s Numbers、Microsoft Excel等のスプレッドシートアプリは、データを並び替えたり、データを行と列に保存し、列にschema（スキーマ）またはフォーマットを指定することによってデータを整理することができる。この基本的なデータを保存するものをデータベースと呼ぶ。

flat-file database（フラットファイルデータベース）とは、単純なデータベースの表現を保存したもので、通常、マークアップされていないテキストファイルで構成されており、リレーショナルデータをカンマなどの区切り文字で区切って表現します。CSVファイル形式は、まさにその一つである。

　

Cleaning

Pythonを使って、ファイルを開き、データを一行ずつ読み込んでいく。

import csv
with open("Favorite TV Shows - Form Responses 1.csv", "r") as file:
    reader = csv.reader(file)
    next(reader)
    for row in reader:
        print(row[1])

with を使ってCSVファイルを開くと、作業が終わると勝手にフィアルを閉じてくれる。open は読み取り専用モードでファイルを開くが、明示的に指定するために r を付け加える。CSV ライブラリーには、reader 関数があり、簡単にデータを読み込むことができる。まず、next を使ってデータのヘッダーが入っている一行目を飛ばし、その後にループを使って全ての行を読み込む。

このプログラムを改善するためには、DictReader を使う。DictReader を使えば、データの辞書を作るため、列データに名前でアクセスできるようになるのと、1行目のヘッダーは自動的にスキップされる。

import csv
with open("Favorite TV Shows - Form Responses 1.csv", "r") as file:
    reader = csv.DictReader(file)
    for row in reader:
        print(row["title"])

次に、重複したテレビ番組名をまとめてデータを整理する。

import csv
titles = set()
with open("Favorite TV Shows - Form Responses 1.csv", "r") as file:
    reader = csv.DictReader(file)
    for row in reader:
        titles.add(row["title"])
for title in titles:
    print(title)

titles という set 型 を宣言し、重複データを除きながらデータを保存していく。

テレビ番組名順にデータを並び替えるためには、Python の sorted 関数 を使って、for title in sorted(titles) と指定する。結果は、綺麗になっているように見えるが、大文字小文字の違いなので、まだユニークとは言えない。よって、データを全て大文字に統一するために、titles.add(row["title"].upper()) と加え、また不要なスペースがある場合はそれを取り除く（ strip() ）ために titles.add(row["title"].strip().upper()) とする。

　 　

Counting

データがある程度整理されたところで、一番人気のテレビ番組を分析する。ここで、set() は重複データを取り除くことには適しているが、票はカウントされないことになってしまう。よって、Dict 型を使う。

そして、重複データ（同一のテレビ番組名）を見つけた度に、カウンター titles[title] += 1 で集計すれば良い。ここで注意点は、変数を使うためにはまず値を代入する必要があるため、カウンターを使いするだけではエラーとなってしまう。よって、以下のように もしまだカウンターにデータが追加されていない場合は、初期化するというプログラミングを if title not in titles: titles[title] = 0 とする。これでエラーを回避できる。

最後に、print 関数の第2引数に、titles[title] を追加して結果を確認できる。

import csv
titles = {}
with open("Favorite TV Shows - Form Responses 1.csv", "r") as file:
    reader = csv.DictReader(file)
    for row in reader:
        title = row["title"].strip().upper()
        if title not in titles:
            titles[title] = 0
        titles[title] += 1
for title in sorted(titles):
    print(title, titles[title])

　

が、集計したかったのは人気順なので、カウンター順でソートするために、仮にf 関数と名付けた関数を作り、番組名を渡すと、集計結果を返す関数を作り、sorted() の第2引数に、key=f を指定することで、集計結果を渡すことができる。

..
def f(title):
    return titles[title]
for title in sorted(titles, key=f, reverse=True):
...

関数は、通常プログラムの中で何度も使うために書くもので、上の例のように一度しか呼ばないときは、Python では lambda 関数を呼ぶことができ、以下のように書き換えられる。

for title in sorted(titles, key=lambda title: titles[title], reverse=True):

　

Searching

今度は、自動的にファイルを開いて人気順に並び替えるだけでなく、特定の番組を検索するプログラムの紹介。

import csv
title = input("Title: ").strip().upper()
with open("Favorite TV Shows - Form Responses 1.csv", "r") as file:
    reader = csv.DictReader(file)
    counter = 0
    for row in reader:
        if row["title"].strip().upper() == title:
            counter += 1
print(counter)

今度は、ある特定の番組（一つだけ）の票数を数えるプログラムなので、int 型 の counter を使う。また、ユーザーが人気番組を入力する際の大文字小文字スペースを統一するために、input("Title: ").strip().upper() と指定する必要があることに注意。ただ、このプログラムは検索スピードが遅い。よってリレーショナルデータベースを使うことによって検索スピードを上げることができる。

リレーショナルデータベース（Relational databases）

リレーショナルデータベースは、Flat-data fileと同様に行と列からなる表を使うが、リレーショナルデータベースでは、より多くのデータ構造が用意されており、検索、更新、挿入などの操作が効率的に実行できる。

SQL（シークゥオ）Structured Query Language は、データベースを操る言語で、Oracle Database、MySQL、PostgreSQL、Microsoft Access、などがあるが、この講義ではSQLiteを使う。

データベースを操作するためには、コマンドラインプロウイルス（sqlite3）を使う。CSVファイルをデータベースに入れる場合は、以下のコマンドライン（.mode csv .import）で操作する。

~/ $ sqlite3
SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
sqlite> .mode csv
sqlite> .import 'Favorite TV Shows (Responses) - Form Responses 1.csv' shows

リレーショナルデータベースを操作するためには、次の4つのコマンドを覚えておく必要がある。

  • CREATE
  • READ
  • UPDATE
  • DELETE

　

SQL

SQLでは、コマンドは次の5つ。

  • CREATE, INSERT
    • CREATE TABLE table (column type, ...); を使えば、テーブルを作ることができる。
  • SELECT
    • SELECT column FROM table;
  • UPDATE
  • DELETE

ここで、.schema でスキーマを確認できる。スキーマとは、データベースの構造のことで、データベース管理システムでサポートされている形式言語で記述される。

sqlite> .schema
CREATE TABLE shows(
  "Timestamp" TEXT,
  "title" TEXT,
  "genres" TEXT
);

コラムを選択することもできる。以下では、shows というテーブルから、title のコラムのデータを選択している。

sqlite> SELECT title FROM shows;
title
...
"Madam Secretary"
"Game of Thrones"
"Still Game"

複数のコラムを選択することも可能で、例えば SELECT Timestamp, title FROM shows; のように指定してあげれば良い。ワイルドカード（SELECT * FROM shows;）を使うことも可能。

これ以外にも、多くの関数が最初から実装されている。

  • AVG
  • COUNT
  • DISTINCT 重複データを排除できる
  • LOWER
  • MAX
  • MIN
  • UPPER

DISTINCT を使えば、人気番組名の重複を省いてテーブルにデータを入れることができる。

sqlite> SELECT DISTINCT(UPPER(title)) FROM shows;
title
...
"GREY'S ANATOMY"
"SCOOBY DOO"
"MADAM SECRETARY"

次のような条件を追加することもできる。

  • WHERE, matching results on a strict condition
  • LIKE, matching results on a less strict condition
  • ORDER BY, ordering results in some way
  • LIMIT, limiting the number of results
  • GROUP BY, grouping results in some way
  • …

番組名が “The Office” のデータだけを選択、表示することもできる。

sqlite> SELECT title FROM shows WHERE title = "The Office";
title
...
"The Office"
"The Office"
"The Office"

ただ、大文字小文字スペースなどを考慮するために、LIKE % を使って検索することも可能。% は、Placeholderを意味し、そこに文字が入ることを意味する（入らなくても良い）。

sqlite> SELECT title FROM shows WHERE title LIKE "%Office%";
title
...
office
"The Office"
"the office "
"The Office"

並び替えることも可能。

sqlite> SELECT DISTINCT(UPPER(title)) FROM shows ORDER BY UPPER(title);
...
X-FILES
"ZETA GUNDAM"
"ZONDAG MET LUBACH"

同じ番組名毎にまとめ、いくつあるか表示することも可能。GROUP BY

sqlite> SELECT UPPER(title), COUNT(title) FROM shows GROUP BY UPPER(title);
...
"THE OFFICE",23
...
"TOP GEAR",1
...
"TWIN PEAKS",4
...

数順で並びかることも可能。ORDER BY

sqlite> SELECT UPPER(title), COUNT(title) FROM shows GROUP BY UPPER(title) ORDER BY COUNT(title);
...
"THE OFFICE",23
FRIENDS,26
"GAME OF THRONES",33

DESC を使えば、降順に変更できる。また、LIMIT 10 と指定すれば、表示数を10までに制限できる。

sqlite> SELECT UPPER(title), COUNT(title) FROM shows GROUP BY UPPER(title) ORDER BY COUNT(title) DESC LIMIT 10;
UPPER(title),COUNT(title)
"GAME OF THRONES",33
FRIENDS,26
"THE OFFICE",23
...

番組名に入っている余分なスペースを除く TRIM

sqlite> SELECT UPPER(TRIM(title)), COUNT(title) FROM shows GROUP BY UPPER(TRIM(title)) ORDER BY COUNT(title) DESC LIMIT 10;
UPPER(title),COUNT(title)
"GAME OF THRONES",33
FRIENDS,26
"THE OFFICE",23
...

この検索結果を .save shows.db で保存することも可能。

このようなプログラムを書くためには、Pythonでも何行かのコードが必要だったが、SQLを使えば非常に簡単にデータを操作できることが分かった。

　

Tables

ジャンルで、Comedyだけでなく、複数のジャンルを選択した人のデータも数に入れるために、LIKE "%Comedy%" と検索しなければならい。

sqlite> SELECT title FROM shows WHERE genres LIKE "%Comedy%";
...

ただ、これにも問題がある。ジャンルが Music のものを選択したい場合は、%MUSIC% となるが、これでは、別のジャンルの Musical も含まれてしまう。

さて、これをどう解決するか？

解は、データをテーブルに入れる際に、データを綺麗にする必要がある。INSERT INTO table (column, ...) VALUES(value, ...);

sqlite> INSERT INTO shows (Timestamp, title, genres) VALUES("now", "The Muppet Show", "Comedy, Musical");

行のデータを更新する場合は、UPDATE table SET column = value WHERE condition;

sqlite> UPDATE shows SET genres = "Comedy, Drama, Musical" WHERE title = "The Muppet Show";

データを削除するためには、DELETE FROM table WHERE condition;

sqlite> DELETE FROM shows WHERE title LIKE "Friends";

手作業で一つ一つデータを入力しているのでは、膨大な時間がかかってしまう。ということで…

ここから徐々にSQLの本当の価値が見えてくる。PythonのプログラムにSQLを組み込むことができる。CSVファイルをSQLにデータを入れるプログラムを書いてみる。

まずは、テーブルを整理する。一つのテーブルにすべてをまとめるのではなく、2つのテーブルに分けるところから始まる。1つ目はは、番組名。2つ目は、ジャンルをまとめたもの。こうすることによって、ジャンルのテーブルでは、一つの番組に対して複数のジャンルがある場合は、複数の行で表示されるが、上で紹介したような問題を解決できる。

SQLにも、データを入力する際に最適化するコマンドがある。

  • BLOB, for “binary large object”, raw binary data that might represent files
  • INTEGER
  • NUMERIC, number-like but not quite a number, like a date or time
  • REAL, for floating-point values
  • TEXT, like strings

他の属性も指定できる。

  • NOT NULL （非NULL制約）, 必ず値が必要
  • UNIQUE （ユニーク制約）, ユニークでなければならい（重複データは受け付けない）
  • PRIMARY KEY （主キー）, テーブル内でデータを特定するために作るデータで、通常は数字が順番に0から並んでいる。上の図の例では、 id がその例。 
  • FOREIGN KEY （外部キー）, show_id のように他のテーブルを参照しているキーのこと

import csv
from cs50 import SQL
open("shows.db", "w").close()
db = SQL("sqlite:///shows.db")
db.execute("CREATE TABLE shows (id INTEGER, title TEXT, PRIMARY KEY(id))")
db.execute("CREATE TABLE genres (show_id INTEGER, genre TEXT, FOREIGN KEY(show_id) REFERENCES shows(id))")
with open("Favorite TV Shows - Form Responses 1.csv", "r") as file:
    reader = csv.DictReader(file)
    for row in reader:
        title = row["title"].strip().upper()
        id = db.execute("INSERT INTO shows (title) VALUES(?)", title)
        for genre in row["genres"].split(", "):
            db.execute("INSERT INTO genres (show_id, genre) VALUES(?, ?)", id, genre)

まず、shows.db でファイルを開閉してファイルを作成する。次に、データベースを保存するための変数 db を指定。次に、db.execute を使えばSQLコマンドを書くことができる。これで自動化できるわけだ。

テーブルが作られたところで、今度はデータを入力していく。INSERT コマンドを使ってデータを入力していく。SQLでは、? がPlaceholderとなる。id は自動的に作られるので指定する必要はない。よって、VALUE(?) と記述する。これで、番組名をテーブルに代入できる。

次に、ジャンルのテーブルを作る。ここで、ジャンルのデータは、コンマでデータを分けてから代入する必要があるので、for 文 の行でこれを指定している。また、今度は、show_id を使って番組名を参照する必要があるので、id を指定する。id は 自動的にシステムが判断して接続してくれる。

shows テーブルはユニークに、genres テーブルは、複数のジャンルが指定されていたら複数行代入される。以下の図では、512 GAME OF THRONESが、Action、Adventure、History、Thriller、Warが選択されていたことが分かる。

sqlite> SELECT * FROM shows;
...
511 | MADAM SECRETARY
512 | GAME OF THRONES
513 | STILL GAME
sqlite> SELECT * FROM genres;
...
511 | Drama
512 | Action
512 | Adventure
512 | History
512 | Thriller
512 | War
513 | Comedy

ジャンルが Musical のものを確認したい場合は、

sqlite> SELECT show_id FROM genres WHERE genre = "Musical";
...
422
435
468

ジャンルが Musical の番組名を表示するためには、（IN（）を使ってネストすることができる）

sqlite> SELECT title FROM shows WHERE id IN (SELECT show_id FROM genres WHERE genre = "Musical");
title
BREAKING BAD
...
THE LAWYER
MY BROTHER, MY BROTHER, AND ME

逆に、番組名が THE OFFICE に指定されたジャンルを表示するためには、

sqlite> SELECT DISTINCT(genre) FROM genres WHERE show_id IN (SELECT id FROM shows WHERE title = "THE OFFICE") ORDER BY genre;
genre
...
Comedy
Documentary
...

このデザインは更に改善できる。genres テーブルの中に、Comedy、Comedy、Comedyと何度も繰り返し表示されてしまっているが、これを3つ目のテーブルを使うことで更に良いデザインにすることができる。これを、join tableと呼び、2つのテーブルを”_”（アンダースコア）で接続したテーブル shows_genres が作られる。このテーブルには foreign key が入っている。

これで、Comedyは繰り返されることがない。

さらに、データ型を指定することができる。

  • INTEGER
    • smallint, with fewer bits
    • integer
    • bigint, with more bits
  • NUMERIC
    • boolean
    • date
    • datetime
    • numeric(scale,precision), with a fixed number of digits
    • time
    • timestamp
  • REAL
    • real
    • double precision, with twice as many bits
  • TEXT
    • char(n), a fixed number of characters
    • varchar(n), a variable number of characters, up to some limit n
    • text, a string with no limit

　

IMDb

Internet Movie Database（IMDb）（映画のデータベース）を使って実際のデータを操る。IMDbは、TSV（tab-separated values）ファイルなので今までの例で使われたコンマとは違うことに注意。

genres テーブルは重複データが存在するが、stars と writers テーブルは、people と showsテーブルを結合している。

SELECT COUNT(*) FROM shows; コマンドを実行すると、150,000以上の映画が存在することが分かる。これを検索していては、時間がかかり過ぎてしまうので、INDEX を使って、データ構造を変えることができる。Indexとは、B-trees という特殊なデータ構造で、C言語でいうところの binary treesのようなもの。もっと多くのメモリを使うが、検索結果は早くなる。

sqlite> CREATE INDEX title_index ON shows (title);

最後に、JOIN の紹介。JOIN は一時的にテーブルを結合できる。

sqlite3> SELECT title FROM people
    ...> JOIN stars ON people.id = stars.person_id
    ...> JOIN shows ON stars.show_id = shows.id
    ...> WHERE name = "Steve Carell";
...
The Morning Show
LA Times: the Envelope

　

Problem

最後に、SQL injection attack と呼ばれる良く知られたハッキングのリスクについて。仮に、次のようなプログラムがあった場合、

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

ログイン画面でIDとしてEmailを入力する場合、malan@harvard.edu’–と指定すれば、それ以降のSQLがコメントとみなされ、書き換えられてしまう。

f"SELECT * FROM users WHERE username = '{username}' AND password = '{password}'"

malan@harvard.edu’–と指定すると、AND 以降、特にパスワードの部分が入力無効となるため、パスワードの入力が必要なくなくことを意味する。

 f"SELECT * FROM users WHERE username = 'malan@harvard.edu'--' AND password = '{password}'"

もう一つの問題は、race conditionと呼ばれるもの。インスタグラムのようなアプリを想定した場合、同時に複数の人が like を押した場合、以下のプログラムが同時に処理されることになり、混線してしまう。よって、誰かがデータベースを操作しているときは、他の人が操作できないようにする処理が必要。

rows = db.execute("SELECT likes FROM posts WHERE id = ?", id);
likes = rows[0]["likes"]
db.execute("UPDATE posts SET likes = ? WHERE id = ?", likes + 1, id);

これを防ぐために、次のコマンドが用意されている。

  • BEGIN TRANSACTION
  • COMMIT
  • ROLLBACK
db.execute("BEGIN TRANSACTION")
rows = db.execute("SELECT likes FROM posts WHERE id = ?", id);
likes = rows[0]["likes"]
db.execute("UPDATE posts SET likes = ? WHERE id = ?", likes + 1, id);
db.execute("COMMIT")

以上！