CS50 2020 Week7 SQL

いよいよ現実世界でTwitterやInstagram、Facebookに使われているデータベースの構造に迫る。
難しいけど、今まで学んできたパーツが合わさって出来てる感じで、ちょっと感動。
最後に先生とブライアンのコントが観られるのでお楽しみに。

////////////////////////////////////////////////////////////////

今日はデータに焦点を当てます。データを収集するプロセス、保存するプロセス、検索するプロセス、その他多くのことです。

先週はまず、比較的小さなデータセットを使って遊んでみました。
みんなにホグワーツでの希望の家を聞いてみました。
そして、pythonを使ってそのデータを少しずつ分析し、グリフィンドールやスリザリンなどを希望する人の数を数えてみました。
最終的にはGoogleのフォームを使って集計してみました。
全てのデータをGoogleスプレッドシートに保存し、CSVファイルとしてエクスポートしました。
今週はもう少しデータを集めて、気になるデータをスプレッドシートやCSVファイルだけで保存すると、どんな問題が起きるのかを見てみましょう。
実際、このURLにアクセスしてみると、別のGoogleフォームが表示されます。
皆さんにはお気に入りのテレビ番組があるでしょう。
そこで今回は皆さんに好きなテレビ番組とそのテレビ番組が属するジャンルをフォームに入力して頂きたいと思います。
(オーディエンスが入力)
それでは送られてきたデータを見てみましょう。
これはGoogleformsが作成したGoogleスプレッドシートです。
Googleフォームにはデフォルトで3つの列があることに気づくでしょう。
Timestamp title genres
1つ目はタイムスタンプで、みんなが何日何時に回答を送ってきたかを基にGoogleが自動的に表示してくれます。
そして、その先のヘッダ行にはタイトルとジャンルが表示されています。
目立たせるためにあらかじめ手動で太字にしてあります。
しかし、このタイトルとジャンルという見出しが、Googleフォームで質問した内容と完全に一致していることに気付くでしょう。
これにより、皆さんの回答と私たちの質問を一致させることができます。
ここでは、punisher、The office、Breaking Bad、New Girl、Archer、The Office…などが入力されているのがわかります。
そして3列目のジャンルでは不思議なことが起こっています。
いくつかのセル、つまりテキストの小さなボックスには「コメディ」や「ドラマ」といった単一の単語が入っていますが、いくつかのセルにはカンマで区切られたリストが入っていることに気づくでしょう。
Crime,Drama,Thriller
このカンマで区切られたリストは、Breaking Badが犯罪ジャンルのドラマであり、スリラーでもあることを示すために、複数のチェックボックスにチェックを入れた人がいることを示しています。
Googleフォームでは、これらの値を全てカンマ区切りのリストにして、スプレッドシートの中にドロップするという、ちょっと怠惰な方法で処理しています。
これは最終的にCSVファイルとしてダウンロードする場合、カンマの間にカンマが入ってしまうため、問題になる可能性があります。
幸いなことにこれには解決策があるので、後ほどご紹介します。
さて、ここにはかなりの量のデータがあります。
実際、下にスクロールしていくと、数百件の回答が表示されています。
このデータを何らかの方法で分析して、最も人気のあるテレビ番組を把握したり、ジャンル別に好きな新番組を検索したりできたらいいですよね。
このように、このデータセットを使っていくつかの質問に答えることができるでしょう。
しかし、このようなデータをただのスプレッドシートに置いておくことの限界をまず考えてみましょう。
私たちは皆、GoogleスプレッドシートやApple Numbers、Microsoft Excelなどのツールを使うことがあるでしょう。
そこで、スプレッドシートの得意なこと、不得意なことを考えてみましょう。
誰か、最初の質問に答えてくれませんか？
スプレッドシートの得意なことは？
オーディエンス(アンドリュー)「非常に素早く分類して検索を行うことができます。」
↑素早く分類できる。良いことです。
例えばタイトルの列の一番上をクリックすると、すぐに全てのタイトルをアルファベット順に並べ替えることができます。

スプレッドシートを使うほかの理由はないでしょうか？
オーディエンス(ピーター)「大量のデータを保存して、後で分析することができます。」
↑いわば、大量のデータを保存するのに適したモデルと言えます。
ただ、実際には限界があります。
実はその昔、私はこの限界を知りました。
大学院時代、研究データの分析に表計算ソフトを使っていました。
あるとき、エクセルがサポートする行数を超えるデータがありました。
具体的には65,536行あったのですが、当時のエクセルでは多すぎました。
なぜなら、Googleスプレッドシートのような表計算ソフトでは、全ての行に1から順に番号が振られているからです。
残念ながら、当時のMicrosoft社は、16ビット(または2バイト)の整数を使って、それぞれの数字を表現していました。
そして、2の16乗は約65,000です。
ですから、この時点で私は行数の上限に達してしまったのです。
ピーターの指摘によると、近年その数は大きくなっています。
そして、実際もっと多くのデータを保存できるようになっています。
スプレッドシートは確かにその点では優れています。
しかし、必ずしも全てに優れているわけではありません。
というのも、ある時点で、MacやPCで処理できないほどのデータがスプレッドシートに保存される可能性があるからです。
実際、twitterやInstagram、Facebookなどのアプリケーションを構築しようとしている場合、これらの企業はデータをスプレッドシートに保存していません。
なぜなら、データ量が多すぎて使いこなせないからです。
また、文字通り誰も自分のコンピュータでデータを開くことができません。
なので、規模の問題に対する解決策が必要になります。
しかし、スプレッドシートの優れた点を捨てる必要はないと思います。
確かに多くのデータを行形式で保存することができます。
しかし、列形式でも多くのデータを保存することができるように思われます。
私はA,B,Cの列しか示していませんが、もちろんD,E,Fなどの列を追加したスプレッドシートを使ったことがあるでしょう。
では、スプレッドシートの行と列をどう考えるか、正しいメンタルモデルは何でしょうか？
概念的に多少異なる使い方をしているのではないかと感じています。
考え方が少し違うのかもしれません。
スプレッドシートの行と列の違いは何ですか？
オーディエンス(ソフィア)「データ等のエントリを追加することは、行に関することですが、データの実際の属性や特徴は列にあるべきです。」
↑その通りです。スプレッドシートにデータを追加するときは、実際には一番下に追加して、どんどん行を増やしていくべきなのです。
もちろん、それは人間の感覚に過ぎませんが、これらは垂直方向に成長します。
上から下に向かってどんどん行を増やしていくのです。
しかし、ソフィアの指摘によると、列は属性やフィールドなどと呼ばれるものを表し、保存するデータの種類を表します。
今回のフォームの場合、タイムスタンプが1列目、タイトルは2列目、ジャンルは3列目です。
これらの列はフィールドや属性、データのプロパティと考えることができます。
これらのプロパティは、最初にフォームを作成するときに、あるいは別のケースではスプレッドシートを手動で作成するときに事前に決めておくべきものです。
スプレッドシートでは、データの種類が増えない限り、左から右へとデータを追加し、列をどんどん増やしていく習慣はありません。
誰かが好きなテレビ番組を追加したからといって、それを左から右へと新しい列に追加すべきではないのです。
上から下へと追加していくべきなのです。
しかし、実際に全員からより多くの情報を収集することにしたとしましょう。
もしかしたらフォームでは名前やメールアドレス、その他の質問をしていたかもしれません。
そのようなプロパティや属性、フィールドは新しい列として属することになります。
このように、データのレイアウトやスキーマ(構造)は事前に決めておくのが一般的です。
気が変わって特定のデータのスキーマを変更する必要がない限り、そこから先は列ではなく行を追加していくのです。
ピーターの言うように、大規模なデータセット、あるいはそれなりの大きなデータセットを収集する場合には、スプレッドシートは実に便利だということがわかりました。
もちろん、先週に引き続き、これらのデータセットをCSVファイルとしてエクスポートすることもできます。
スプレッドシートからASCIIやUnicodeで保存されたシンプルなテキストファイルに変換できます。(一般的にはハードディスクやクラウドのどこかに保存されます。)
このCSVファイルはフラットファイルデータベースと呼ばれています。
データベースとは、一般的に言えば、データを保存するファイルのことです。
あるいは、データを保存してくれるプログラムのことです。
私たちの誰もが、何らかの形でデータベースについて考えたり、使ったりしたことがあるでしょう。
Googleやtwitter、Facebookなどの大手サイトでは、データの保存にデータベースが使われていることはよく知られています。
そのデータベースとは、たくさんのデータが入った巨大なファイルか、データを保存する特別なプログラムのことです。
フラットファイルとは、非常にシンプルな設計であることを表しています。
実際、何十年も前に人間は単純なテキストファイルにデータを保存する際に、異なるタイプのデータを保存するなら、つまりソフィアが指摘したように、異なるプロパティや属性を保存するなら、シンプルにしておこうと考えました。
フラットファイルデータベース、通称CSVでは、列をカンマで区切るだけにしておきましょう。
区切りには他のものを使うこともできます。
TSV(Tab-Separated Values)と呼ばれるものもあります。
率直に言って、何を使っても良いのです。
しかし、困ったことがあります。
すでにそのプレビューを見ました。
実際のデータ自体にカンマが含まれているとしたら？
お気に入りのテレビ番組のタイトルにカンマが入っていたら？
Googleがジャンルをカンマで区切られたリストとして保存しているとしたら？
フラットファイルデータベースとしてCSVを使用すると、悪いことが起こりえます。
しかし、それに対する解決策はあります。
実は世の中では、CSVファイルの中にカンマがある場合、文字列全体の左端と右端を""二重引用符で囲んでしまうのが一般的なのです。
二重引用符の中にあるものは、ファイル内の他のカンマのように列を区切るものとはみなされません。
以上がフラットファイルデータベースの意味するところです。
Googleスプレッドシートやエクセル、Numbersなどのプログラムでは、ファイルをCSV形式で保存することができるので、CSVはおそらく最も一般的なフォーマットの一つです。
さて、話が長くなりましたが、組み込み式関数や数式など、スプレッドシートのファンシーな機能を使ったことがある人もいると思います。それらはGoogleスプレッドシートやエクセル、Numbersに組み込まれており、独自のものです。
CSVファイルやTSVファイル、あるいはフラットファイルデータベースでは一般的に数式を使うことはできません。
保存できるのは静的な値、つまり不変の値だけです。
つまり、データをエクスポートすると、目に見えるものがそのままデータになってしまうのです。
だからこそ、エクセルやNumbers、Googleスプレッドシートのような洗練されたプログラムが使われるのです。
しかし、データをエクスポートしても実際には生のテキストデータしか得られません。
でも、それで良いと思います。
ところでブライアン、このスプレッドシートをCSVファイルとしてダウンロードしてもいいですか？
ブライアン「はい、どうぞ。」
Googleスプレッドシートの「ファイル」から「ダウンロード」を選択します。
すると、PDF、Webページ、CSVなど、様々なオプションが表示されます。
そこで、スプレッドシートのドロップダウンからCSVを選んでみましょう。
これでファイルがダウンロードされました。
それではCS50 IDEに入ってみましょう。
先週、IDEにファイルをアップロードすることができたことを思い出してください。
今週も同じようにしてみましょう。
私のコンピュータのダウンロードフォルダにあるファイルを取り出します。
そして、これをIDEにドラッグ＆ドロップして、いわば私のホームディレクトリに配置します。
これで「Favorite TV Shows Forms」というファイルができました。
実際、IDEの中でこれをダブルクリックすると、見慣れたデータが表示されます。
Timestamp,Title,Genresはこのファイルのプロパティや属性の名前を含むヘッダ行です。
そして、それぞれのリストが表示されています。
ここで、Googleがカンマを含む値を二重引用符で囲んでくれていることに注目してください。
"Crime,Drama,Thriller"
このように、比較的シンプルなファイルフォーマットです。
誰がThe officeが好きか、誰ばBreaking Badが好きか、あるいは他の番組が好きかを把握するために、このファイルにざっと目を通すことができます。
しかし、先週に引き続き、pythonという便利なプログラミング言語を手に入れたことで、このデータをより簡単に操作・分析できるようになりました。
先週の「仕事に適したツールを使う」という話ですが、CS50の今までの週でも、今からやることは絶対にできました。
もちろんCを使うこともできるでしょう。
しかしお分かりのように、C言語での作業は文字列の操作、変更、分析など、ある種の処理に関しては苦痛を伴う傾向があります。
スペルチェッカのように、CSVファイルをメモリにロードすることはできません。
mallocやreallocを使いまくることになります。
テキストファイルを分析するだけでも大変な作業が必要です。
pythonはデータを分析したり開いたりするために自由に使える関数を増やすことで、そのような作業を全て代行してくれるのです。
先に進んで、新たにfavorites.pyというファイルを作成し、このデータセットを使って質問に答えられるかどうかを調べてみます。
率直に言って私自身、始めてプログラミングを学んでから20年以上経った今でも、新しいプログラムを書くときにはシンプルに始める癖がついていて、いきなり最終的に解決したい問題を解決するのではなく、コンセプトの証明のようなもっとシンプルなものを作って、適切であるかどうかを確認します。
それはつまり、次のようなことです。
簡単なプログラムを書いてみましょう。このCSVファイルを開き、上から下へと繰り返し、各タイトルを出力します。
これは、自分が何をしているのか、そこにあるデータにアクセスできるのかを確認するための簡単な整合性チェックです。

それでは先に進んでCSVをインポートしてみましょう。
import csv
もうお分かりでしょうが、openコマンドとwithキーワードを使ってファイルを開くと、最終的には自動的に閉じてくれます。
そしてFavorite TV Shows - Form Responses 1.csvというファイルを読み取りモードで開き、fileという変数名を与えます。
with open("Favorite TV Shows - Form Responses 1.csv","r")　as file:
厳密に言えば、rは必須ではありません。
ネット上ではrを入れない例を見かけることがあります。
それはreadがデフォルトだからです。
しかし、Cやfopenと同等にするために、私は明示的にrを書くつもりです。
つまり、この行は「CSVファイルを読み取り専用で開き、fileという変数を作って、それを参照できるようにする」効果があります。

さて、これからCSVの機能を使ってみましょう。
私たちがreaderと呼んでいるものを与えようと思います。名称はxyzでも何でも構いません。
reader = csv.reader(file)
そしてそれは、そのファイルでcsv.readerを呼び出したときの戻り値になります。
先週に引き続き、CSVライブラリには多くの機能が内蔵されています。
そして、入力として必要なのは、すでに開かれているテキストファイルです。
そして、そのファイルをいわばラップして列や行を一度に読めるようにするなど、より便利な機能を満載しています。
さて、これから先に進みますが、今のところ、最初の行はスキップします。
1行目にはタイムスタンプ、タイトル、ジャンルという見出しがあるので、1行目はスキップします。
next(reader)
列の内容はわかっているので、今はこの行を無視します。
では、次のようにしてみましょう。
for row in reader:
print(row[1])
行のタイトルだけが欲しいのですが、左から右の3列であれば、0、1、2の順なのでrow[1]、つまり2列目になります。
ターミナルウィンドウに移動して、python favorites.pyを実行してみましょう。
(全ての行のタイトルが表示される)
ほら。あっという間でした。
確かにこれは皆さんが入力したテレビ番組の全てのようですね。
上にスクロールしていくと確かに数百件あります。
ということで、私のプログラムは機能しているようです。
しかし、もう少し改良してみましょう。
csv.readerの使用はpythonでは必ずしもベストなアプローチではありません。
多くの人がすでにDictReader、つまり辞書readerを発見していると思いますが、これは素晴らしいことです。
なぜなら、データが何番目のどの列にあるかを調べたり、二重に確認する必要がないからです。
その代わりに、タイトルやジャンルなど、ヘッダ自体で参照することができます。
なぜなら、あなたや同僚がスプレッドシートをいじっていて、列を左右にドラッグして並べ替えてしまった場合、あなたがコードで使っている0、1、2などの数字は突然間違った列を参照し始めてしまうからです。
辞書readerを使えば、単なる数字ではなくタイトルを使用するので少しは堅牢になるでしょう。
しかし、最初の行で誰かが値を変更し、タイトルやジャンルの名前を変えてしまうとやはり不具合が生じます。
そうなると破綻してしまいます。
その時はコードとデータの関係を把握していなかったあなた自身を責めるしかありません。
しかし、どの道を選んだとしても、依然としてリスクはあります。
そこで、ここでは辞書reader、つまりDictReaderを変更することにします。
reader = csv.DictReader(file)
残りのコードはほとんど同じですが、このnext(reader)というハックは必要ありません。
最初から次の行にスキップする必要はありません。
なぜなら、最初の行を読む処理は辞書readerにおまかせできるからです。
それ以外は、この最後の行を除いて、同じままです。
というのも、ここではrowをリストではなく辞書として使っていて、各行の中からtitleだけを出力したいからです。
print(f"{row["title"]}")
それではもう一度プログラムを実行してみましょう。
ほら。同じ結果が得られたようです。
しかし、実際に両者を並べて比較してもやっていることは同じとわかるでしょう。

さて、新しい機能を追加する前に、先ほど書いたpythonスクリプトでファイルを開き、readerやDictreaderでラップして、一度に一行ずつ反復してtitleを表示することについて、質問や不明な点はありませんか？
構文についての質問でも結構です。
私たちはまだpythonを知って1週間しか経っていません。
慣れていなくても問題ありません。
ブライアン「ここで使っている構文で、ファイルを閉じる必要がないのはなぜですか？」
↑非常に良い質問ですね。
先週、私はより慎重にopenを単独で使用しました。
そしてその後、開いたばかりのファイルに関連付けられたclose関数を使いました。
今回は、よりPythonicな方法として、Cには存在しなかったキーワードwithを使っています。
pythonでは便利な機能で、with open ...と言えば、ファイルを開いてくれます。
そして、コードがキーワードブロックの中でインデントされている限り、ファイルは開かれたままになります。
そして、プログラムが終了すると同時に自動的にファイルが閉じられます。
これは、ある意味でpythonが私たちを私たち自身から守ろうとしている機能の一つです。
私を含めて人間は開いたファイルを閉じるのを忘れることがよくあります。
これは、あるものを永久に保存する際に問題となります。
C言語でお馴染みのメモリリークが発生するのです。
ですから、withキーワードは私がうっかりファイルを閉じ忘れたりしないようにしています。
pythonは自動的にそれをやってくれます。

ブライアン、他にありますか？
ブライアン「DictReaderは、titleが辞書内のkeyの名前であることをどうやって知るのですか？」
↑これも良い質問ですね。
DictReaderはpython言語の作者によって設計されており、ファイルの最初の行を見て、カンマで分割し、最初のカンマの前の最初の単語またはフレーズが1列目の名前で、最初のカンマの後の2番目の単語またはフレーズが2列目の名前であるというように仮定しています。
つまり、DictReaderはCSVの慣例として、1行目に列を参照するための見出しが含まれていることを前提としています。
もし、CSVにそのような見出しがなく、1行目から実際のデータにジャンプしてしまうような場合には、少なくとも手動で設定しない限り、DictReaderを正しく使用することはできません。

さて、それでは先に進みましょう。
データを見る限り、全体的にごちゃごちゃしているように感じます。
かなり人気のある番組もあり、見ていると重複しているものもありますね。
The Officeが好きな人はたくさんいます。
ブレイキング・バッドやゲーム・オブ・スローンズなど、他にもたくさんの番組がありますよね。
ですから、データを見る範囲を狭めて、ユニークな値だけを見るようにした方が良いと思います。
ファイルを隅から隅まで繰り返して、次々とタイトルをプリントアウトするのではなく、このデータを何らかのデータ構造に蓄積して、重複した値を捨て、蓄積したユニークなタイトルだけをプリントアウトするようにしてはどうでしょうか。
これにはいくつかの方法があると思います。
先週の辞書のデモではsetと呼ばれるものを使っていたことを思い出してください。

↓先週のsetへの言及
最後に今日の目的のためにsetと呼ばれるものがあります。
数学を思い出すと、setとはa、b、cや1、2、3のような、重複しない値の集まりです。
しかし、pythonはそれを管理してくれます。
setに要素を加えることも、setから要素を取り除くこともできます。
pythonは重複がないようにしてくれますし、メモリもすべて管理してくれます。

titlesという変数を作り、それにset()というものを代入してみます。。
titles = set()
setとは単なる値の集まりのことです。リストのようなものですね。
しかし、私のために重複を排除してくれます。
これはまさに私がこのプログラムに求める特性です。
さて、タイトルごとに出力するのではなく、まず重複したものを除外したい場合は、次のようにしてみます。
これからadd関数を使ってtitlesに現在の行のタイトルを追加していきます。
import csv

titles = set()

with open("Favorite TV Shows - Form Responses 1.csv","r")　as file:
for row in reader:
titles.add(row["title"])
繰り返しますが、今はまだ出力しません。
代わりに、その特定のタイトルをtitlesに追加します。
もし、それがすでに追加されたものだったら、pythonのsetデータ構造は重複したものを捨ててくれます。
そしてユニークな値だけを残してくれます。
さて、ファイルの一番下ではもう少し作業をする必要があります。
ユニークなタイトルだけをプリントアウトするために、setを繰り返し処理しなければなりません。
for title in titles:
print(title)
ここからがpythonが本当にユーザーフレンドリーになるところです。
int i = 0とか、i<nとか、そんなことを気にする必要はありません。
titlesに含まれる全てのタイトルをプリントアウトしたいならfor title in titlesとすれば良いのです。
titlesという変数が反復処理(イテレイト)できるタイプのデータ構造であれば、それがリストであれsetであれ、あるいは先週pythonで見た別のデータ構造である辞書であっても、pythonのforループは何をすべきかを知っています。
それではこのプログラムを実行してみましょう。
(タイトルが表示される)
これを見ると、確かにリストは先ほどとは何か違うように見えます。
上にスクロールしていくと、結果のタイトル数が少なくなっているようです。
しかし、正直なところ、これでも依然としてごちゃごちゃしています。
先に進んで、これをソートしてみましょう。
C言語ではソートするのはちょっと面倒だったと思います。
バブルソートや選択ソート、あるいは最悪の場合はマージソートなどの擬似コードを作成して、自分たちで一から実装しなければなりませんでした。
しかし、pythonには実に多くの関数が用意されています。
つまり、このsetをソートしたいなら、「ソートしたい」と言えば良いのです。
for title in sorted(titles):
print(title)
pythonにはsortedという関数があって、マージソートのような優れたアルゴリズムを使うことができます。
もしかしたらクイックソートと呼ばれるものかもしれませんし、また別のものかもしれませんが、少なくともO(n^2)のソートを行ったりはしません。
恐らくpython開発者の誰かが時間をかけてより良いソートを実装してくれているのでしょう。
ともかく、私のためにソートしてくれる関数です。
それではこれでもう一度プログラムを実行してみましょう。
(タイトルがアルファベット順にソートされて表示される)
OKです。
これで面白い番組がソートされた状態で揃ったので、頭の中で整理しやすくなりました。
上にスクロールしていくと、誰かがいたずらで入力した数字やピリオドで始まる番組も含めて、アルファベット順で全て表示されます。
これで少しは理解しやすくなったと思います。
しかし、何かがおかしい。
Avatar:The Last Airbenderが好きな人が多いように感じます。
私は実に4回もそのタイトルを見ているのです。
でも、set構造を使うことで、重複は排除していると思っていました。
では、何が起こっているのでしょうか？
実際、このままスクロールしていくと、もっと重複しているものがあるような気がします。
ブレイキング・バッドやブルックリン99、friends…(微妙に大文字小文字の入れ方などが異なる)なども重複しています。
オーディエンス(カダナ)「今回のソートでは、大文字と小文字を区別しています。つまり、誰かがavatarのどこかを大文字のAで綴ったとすると、それぞれ異なる結果として抽出されるということです。」
↑ええ。その通りです。
皆さんの中には、大文字を使うことにあまり熱心でない人もいました。
カダナが指摘するように、実際には大文字と小文字の違いがあるのです。
これについては以前も取り上げました。
実際、皆さんがスペルチェッカを実装した時には、任意のテキストをスペルチェックする際に、すでにこの問題に対処しなければなりませんでした。
大文字で始まる単語もあれば、ある単語は全て小文字で、ある単語は全て大文字です。
大文字と小文字の違いを許容する必要がありました。
そこで、全てを大文字にするか、または小文字にするかして、大文字小文字を区別せずに処理することで解決したはずです。
では、これからこのフォームにちょっとした変更を加えてみましょう。
全てを強制的に大文字か小文字にしてみましょう。
どちらでも構いませんが、いわば、何らかの方法で物事を正規化する必要があります。
正規化とは、全てのデータを何らかの標準的な方法でフォーマットすることです。
カダナの指摘に従って、大文字と小文字を標準化してみましょう。
単にタイトルをrow["title"]で追加するのではなく、統一性のために今回はタイトルを大文字にしてみます。
for row in reader:
titles.add(row["title"].upper())
そして、ここで何が起こったかを確認してみましょう。
上記の部分以外は変更しません。
もう一度実行してみましょう。
(大文字に変換されたタイトルがアルファベット順に表示される)
ほら。少し読みにくいですが。
重複はもう見つかり…ちょっと待ってください。
まだTHE OFFICEが2回表示されています。
そのままスクロールしていくと、今度はSTRANGER THINGSとSTRAINGER THINGSが出てきます。これはただのタイプミスのようですね。
SHERLOCKが2つあります。これは少し怪しいですね。
カダナと私はまだ問題を解決できていないようです。
そして、これはもう少し微妙です。
重複したデータを確実に削除するためには、私のデータに何をすればいいのでしょうか？
オーディエンス(オリビアと)「端を切り取るとか？」
↑端を切り取る？w響きは気に入りましたが、どういうことでしょうか？
「誰かが単語の前後に余分なスペースを入れてしまった時のために、そのスペースを切り落とすことですね。」
↑ええ。その通りです。
人間は意図的であれ偶然であれ、スペースバーを押すべきではないところでスペースを押してしまうことがよくあります。
実際、私が推測するに、皆さんの中にはSHERLOCKの後にスペースを誤って入力したまま「これで終わりだ」と判断した人がいるのではないでしょうか。「他には何も入力していない」と。
しかし、目には見えなくてもスペースは存在します。
そして、我々が文字列の比較を行うとき、あるいはsetデータ構造がそれを行うときに、気づかれることになります。
そのため、同じものとは見なされません。
いくつかの異なる方法でこれを解決することができます。
しかし、pythonでは関数を連結できることがわかりました。これはもう一つのファンシーな特徴です。
titles.add(row["title"].strip().upper())
ここで私が行っていることを見てください。
まずtitlesにアクセスしています。
そこにadd関数で次のような値を追加しています。
row["title"]を追加しているのですが、そのままではありません。
これはpythonで言うところの文字列、strです。
ここで、それにstrip関数を適用しています。
つまり、この関数のドキュメントによると、オリビアが言ったように、左側の全ての空白、右側の全ての空白、それがスペースバーであろうとEnterキーであろうと、Tabキーであろうと、その他いくつかのものを取り除いたり、トリミングしたりします。
先頭と末尾の空白を取り除くのです。
そして残ったものはカダナの提案に基づいて全てを強制的に大文字にします。
このように、2つの優れたアイデアを組み合わせてデータをより綺麗なフォーマットに整形しています。
これは現実的な話です。
人間は、あなたも私も、本来の方法でデータを入力することができません。
たとえAmazonでチェックアウトするときに有効な住所を入力しようとしても、ちょっとした怠慢やソーシャルメディア的な理由で、すべて小文字になってしまうことがあります。
大文字ばかりの場合もあります。私の周りにはCaps Lockをまだ理解していない人が何人かいるからです。
week0で強調したように、正確さが要求されるコンピュータシステムにとって、これは良くないことです。
データの整形とは、データを綺麗にすることであり、データの意味を変えずに正規化、標準化することで、りんごとオレンジではなく、りんごとりんごを比較することです。
さて、先に進んで、もう一度プログラムを実行してみましょう。
(タイトルが重複なく表示される)
いい感じになってきましたね。
今度はTHE OFFICEが一つしか見つかりません。
更に上へスクロールしていくと、まだタイポが見られますが、ホワイトスペースに関するものは何もありません。
この時点で、タイトルのユニークなリストがより綺麗になったと思います。
もちろん、誤字脱字のようなものまで検出しようと思えば、もっと巧妙にならざるを得ません。
F.R.I.などと熱心に書いていたのに、最後に飽きてピリオドを忘れてしまった人がいるようですねw
しかし、ユーザーから入力を受け付けている以上はそういうこともあるでしょう。
CS50にももちろん、様々なバージョンがあります。
これを綺麗にするのは大変です。なぜなら、もしCS50の様々なバリエーションをCS50として正規化したいと思ったら、if条件やelifをたくさん追加して、これら全てをクリーンアップしなければならないからです。
つまり、これは非常に滑りやすい坂道なのです。
私もあなたも、この問題を解決するために、膨大な量のデータを書き始めるかもしれません。
しかし、これが実世界のデータを扱うときの現実なのです。
先に進んで、プログラムを更に改良して、もっとファンシーなことをします。
なぜなら、私のデータは数件のタイプミスやCS50の変種などを除いて、すでにおおまかに正規化されていると思われるからです。
ここで、皆さんに最も人気のあるテレビ番組を調べてみましょう。
すでにビルディングブロックの大半は揃っていると思われるので、今までと同じように、現在のコードから始めようと思います。
ここでは、コードを少し整理します。
もう少し整然と物事を考えられるように、titleという別の変数を用意します。

でも、もうこのsetには何も追加しないつもりです。
定義上、setは重複を排除するので、実際のところ、テレビ番組の人気を追跡するのにsetでは十分ではないと思います。
今の目的はその逆です。
どの番組が重複しているのかを知ることで、The Officeを好きな人が何人いるのかを知ることができるのです。
The Officeが好きな人が何人、Breaking Badを好きな人が何人…といった具合です。
そのためには、pythonのツールキットのうち、どのようなツールを使って情報を蓄積したり、把握したりすればいいでしょうか？
番組とその人気を把握したい場合、どのようなデータ構造が役立つと思いますか？
人気というのは、CSVファイルの中での頻度のことです。
オーディエンス(サンティアゴ)「辞書を使うのも一つの方法だと思います。The Office:20票、Breaking Bad:13票、というように、辞書を使うことで視覚化することができます。」
↑そう。完璧な直感ですね。
辞書とは、結局のところ、どんなに高度に実装されていたとしても、例えばスペルチェッカのように、単なるキーと値のペアの集まりに過ぎないのです。
あるデータを別のデータに関連付けるこの機能は、非常に汎用的な問題解決策であるため、あらゆる言語の中で最も有用なデータ構造の１つと言えるでしょう。
サンティアゴの提案に従って、番組の人気度を把握するという問題では、番組のタイトルをキーに、その頻度、いわば投票数をキーの値にしましょう。
タイトル:投票数、タイトル:投票数…といった具合に対応させていきます。
辞書とはまさにそういうものです。
では、先に進んで上にスクロールしてみましょう。
ここを変えてみます。
setの代わりにdictとすることで、空の辞書を作ることができます。
titles = set()
↓
titles = dict()
実際には、もう少し一般的な略記法があります。
titles = {}
空の中括弧{}を使うのです。
最初は空の辞書を作ってください。
setの方はこのように洒落た略記法はありません。set()と文字通り入力する必要があります。
しかし、辞書の方は非常に一般的で人気があり、強力なので、{}という小さな構文上のショートカットがあるのです。
それでは次のことをやってみましょう。
forループの中でタイトルを表示するのではなく、setに追加するのでもなく、辞書に追加したいと思います。
辞書がtitlesと呼ばれるものであれば、基本的にはtitles[title] += 1のようにすることができると思います。
辞書を、0から始まって1を足し、2を足し、3を足すような、カウントや数字のちょっとしたカンニングペーパーのように使うことができるかもしれません。
だから、The Officeを見るたびに+=1をするのです。
しかし、これはtitlesという辞書に入り、特定のタイトルにマッチするキーを探し、そこにある値を1ずつ増やしていくように見えます。
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()
titles[title] += 1
これをまずは素朴に実行してみましょう。
おっと、9行目ですでに壊れてしまいました。
そもそもPunisherでKey Errorが発生しているというのは、番組の選択としては適切なのかもしれません。Punisherは悪い奴ですからね。
Key errorとは、辞書の無効なキーにアクセスしようとしたことを意味します。
つまり、このコードtitles[title] += 1ではtitlesが辞書であり、titleの値がPunisherであるにも関わらず、そのタイトルがまだ存在していないため、Key Errorが発生しているのです。
この問題を解決するためのpythonの構文がわからないとしても、直感的な解決策は何でしょうか？
辞書にPunisherが入っていないので、Punisherの投票数を増やすことができません。
オーディエンス(グレッグ)「まずforループを作って辞書に登録されているすべての項目に値を割り当てる必要があると思います。例えば、まず0という値を割り当てて、その後に1を加えるとか」
↑ええ。良い直感ですね。
ここで、別の比喩を使ってみましょう。
ここでは「鶏が先か、卵が先か」という問題が生じています。なぜなら、コードの先頭で辞書の全ての値を0に初期化するループを追加することはできないと思うからです。その時点で全ての番組名をすでに取得している必要があります。
グレッグの指摘通り、CSVファイルを開いて、上から下へと繰り返し、タイトルが表示されるたびに辞書の値を0に初期化し、別のforループでファイルを再度開いて、同じことをするとします。
確かにそうすれば動作します。
しかし、これは間違いなくあまり効率的ではありません。
漸近的な計算量の観点からは効率的ですが、2倍の仕事をしているように見えます。
全てを0に初期化するためにファイルを1回反復し、カウントを増やすために2回目の反復を行うのです。
私はもう少し効率的に物事を進めることができると思います。
正しさだけでなく、より良いデザインを実現することができると思います。
全体を2回繰り返すことなく、この問題を解決する方法について何か考えはありませんか？
オーディエンス(セモウィット)「if分を追加して、そのキーが辞書にあるかどうかをチェックすることができると思います。そして、もし辞書になければそれを追加し、その後、値を増加させていきます。」
↑良いですね。まさにそれができるのです。
ではその直感を応用してみましょう。
まだ存在していないキーにアクセスしようとしていることが問題なら、もう少し賢くなりましょう。
そして、セモウィットの提案に従って、キーが存在するかどうかをチェックしましょう。
そして、存在していればインクリメントします。
もし存在していなければ、そのときだけ、グレッグのアドバイスに従って、それを0と初期化します。
ではそうしてみましょう。
if title in titles:としてみましょう。
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 in titles:
これは、このような質問をするときの非常にPythonicで美しい方法で、C言語よりもずっと綺麗です。
次に進んで、以前の通りインクリメントします。
titles[title] += 1
しかし、もしそのタイトルがまだtitlesという辞書に入っていないなら、それもOKです。
else:
titles[title] = 0
つまり、ここでの違いは、存在しないキーを使って、その時点で値を与えようと思えば、確かに辞書にインデックスを付けることができるということです。
先週からずっとそうしています。
しかし、先に進んで、そこにある値を増やそうと思ったら、この別の行でそれを行うことになります。
しかし、ここでバグが発生しました。
論理的にはもう一歩踏み込む必要があると思います。
私はこれを0に初期化したいとは思いません。
誰か私の論理に微妙なバグがあると思いませんか？
タイトルがすでに辞書に入っている場合は1増やし、そうでない場合には0に初期化しようとしていますが、何か微妙な引っ掛かりはありませんか？
オーディエンス(オリビア)「最初のインスタンスなので、1に初期化すべきだと思います。」
↑その通りだと思います。
そうしないと、この特定のタイトルを見落とすことになり、数え落としが生じます。
ですから、このように修正することができます。
else:
titles[title] = 1
あるいは、率直に言って、技術的にはif elseを使う必要はありません。
代わりに次のようにすればifだけで済みます。
if title not in titles:
titles[title] = 0
ttitles[title] += 1
では、どちらがいいのでしょうか？
コードを1行節約できるという点では、2番目の方が少し良いのかもしれませんね。
しかし、セモウィットのアドバイスに従って、タイトルが確実に入っていることを確認するまでtitlesにインデックスを作成しないというif条件を確保しています。
それでは先に進んで実行してみましょう。

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)

(タイトルだけが表示され、投票数は表示されない)
クラッシュはしませんでした。
しかし、まだ有益な情報は得られていません。
しかし、もう少しアクセスできるようになりました。
このプログラムの一番下のこのループを見てみましょう。
for title in sorted(titles):
print(title)
タイトルだけでなく辞書のキーの値も表示してみましょう。
for title in sorted(titles):
print(title,titles[title])
この構文を見たことがないかもしれません。
しかし、printには複数の引数を渡すことができます。
デフォルトでは、printはそれらをスペースで区切って表示します。
実行してみましょう。
(タイトルの横に投票数も表示される)
ほら。できました。
あちこちにありますね。
The Officeは26票の超人気作です。
なかなか良い結果ですが、どの番組が一番人気なのかを知るには、まだまだ時間がかかりそうですね。
スプレッドシートの場合、Microsoft ExcelやGoogle Spreadsheets、Apple Numbersでは列の見出しをクリックするだけで、すぐにソートされますよね。
今では、コードでやらない限り、その機能は失われてしまったようです。
そこで、私がそれをやってみましょう。
コードに戻ってみましょう。
sortedは辞書で動作するとはいえ、実際には値ではなくキーでソートしているようです。従って、私たちのpythonプログラミング技術はもう少し洗練される必要があります。
そして、この問題を具体的に、しかしかなり一般的な方法で解決するpythonの別の機能を紹介したいと思います。
sortedのドキュメントを読んでみると、sorted関数は確かにsetをその中の「値」でソートします。リストをその中の「値」でソートします。辞書はその中の「キー」でソートします。
辞書は全ての要素に2つの情報があるからです。
辞書にはキーと値があり、値だけではありません。
ですから、デフォルトでは、sortedはキーでソートします。
そこで、なんとかこの動作をオーバーライドする必要があります。
では、どうすればいいのでしょうか？
つまり、sorted関数はkeyというオプションの引数を取るのです。
そして、key引数は関数の名前を値として受け取ります。
ここからがややこしいですが、非常に面白いところです。
pythonでは関数の名前を引数として渡すことができます。
技術的にはCでもできますが、構文的にはもっと複雑です。
しかしpythonでは一般的に行われており、JavaScriptでは非常に一般的です。
多くの言語では、関数を第一級のオブジェクトとして考えるのが一般的で、これは変数のように関数を渡すことができるということの洒落た言い方です。
私たちはそれを呼び出しません。しかし、その名前で渡すことができます。
これは何を意味しているのでしょうか？
さて、私は今、辞書をその値でソートするための関数を必要としています。
そして、恐らく私だけがその方法を知っています。
そこで、今のところは一般的な関数の名前をつけてみましょう。f-functionのfです。数学のようなものです。というのも、いずれは取り除くつもりだからです。
先に進み、一時的にfという関数を定義して、入力としてタイトルを受け取ります。
そして、そのキーに対応する値を返すようにします。
つまり、titles[title]を返すようにしてみます。
def f(title):
return titles[title]

for title in sorted(titles, key=f):
print(title,titles[title])
↑ここにある関数の目的はとてもシンプルです。
タイトルを与えると、それをグローバル辞書で調べ、その値、つまり頻度、投票数を教えてくれます。
非常にシンプルですが、それが唯一の目的です。
しかし、sortedのドキュメントによると、sortedが今やろうとしていることは、keyという第2引数を渡しているので、キーでアルファベット順にソートするのではなく、代わりに、辞書のすべての要素に対してfという関数を呼び出すことになります。
そして、あなたの答えに応じて、f関数で与えられた戻り値が、実際の順序を決定するために代わりに使用されます。
つまり、デフォルトではsortedはキーしか見ません。
このf関数で効果的に行っていることは、代わりにすべてのキーに対応する値を返すことです。
構文が少し変わっていますが、論理的な意味合いとしては、このtitlesという辞書は、キーではなく値でソートされることになります。
なぜなら、デフォルトではキーでソートされているからです。
しかし、自分でキー関数を定義して、対応する値を返すように動作をオーバーライドすれば、実際に値、数字、カウントを使用してソートすることが可能なのです。
では、実際にそれが正しいのか試してみましょう。
(キーでなく値でソートされてタイトルと値が表示される)
しかし、もちろん、このリストはある意味後ろ向きです。
つまり、画面の下の方から見る方が便利です。
でも本当は、リストを作るのであれば一番上にあるべきです。
ではどうすればこの動作を無効にできるのでしょうか？
ドキュメントを読むと、sorted関数にはreverseというオプションのパラメータがあることがわかります。
reverseをTrueに設定すると、同じソートの逆順になります。
for title in sorted(titles, key =f, reverse=True):
もう一度実行してみましょう。
(上から順に投票数が多いタイトルが表示される)
なかなか良い感じですね。
繰り返しになりますが、少なくともpythonの新機能は実際に関数を関数に渡して、後者が前者を呼び出すようにすることができることです。

辞書の使い方や、値による逆順のソートの仕方について、質問や不明点はありませんか？
特に無し。
ここで、よくある間違いを指摘しておきましょう。
fが関数であるにも関わらず、ここで関数を呼び出していないことに注目してください。
for title in sorted(titles, key =f, reverse=True):
↑f()でなく、fになっている
なぜかというと、関数fをsorted関数に渡して、sorted関数が勝手にfを何度も何度も呼び出してくれるようにしたいからです。
()括弧を使って一度だけ呼び出したいわけではありません。
pythonに付属しているsorted関数が代わりにそれをやってくれるように、名前で渡したいのです。
オーディエンス(サンティアゴ)「なぜf()にtitleを入れてf(title)としなかったのでしょうか？その質問をしようと思っていました。」
↑実はf(title)とすると、関数を一度しか呼び出さないことになるからです。
私たちはソートして何度も呼び出せるようにしたいのです。
さて、これまで見てきたことを基に、正しい解決策の例を示します。
これは私が意図した通りに動作しており、上から下へ人気順に並べられたタイトルのリストになっています。
しかし、ちょっとお粗末な設計になっています。
というのも、私はこの関数fを定義しているのですが、その名前がそもそもダサいのです。
しかし、私が関数を定義しているのは、ある場所でそれを使うためだけです。
しかも、この関数はとても小さいので、それを渡すためだけに新しい関数を定義するのはキーストロークの無駄のように感じられます。
そこでpythonでは、局所的な問題を一度だけ解決することを目的とした非常に短い関数があって、折り返してスタイルが悪くならないよう1行のコードに収めることができると分かっている場合、代わりにこうすることができます。
このように、関数の定義のために考えていたコードをコピーすることができます。
そして、実際にfを関数名として定義する代わりに、pythonでは lambdaという特別なキーワードを使うことができます。
先ほどと同じように、関数の引数の名前を指定します。
そして、戻り値を指定するだけで、その後、関数自体を削除することができます。
for title in sorted(titles,key=lambda title: titles[title], reverse=True):
→関数fの定義は削除
はっきりさせておきたいのは、keyは依然としてsorted関数の引数であるということです。
その値として、通常は関数の名前が想定されています。
しかし、関数を定義してその名前を渡すのは労力の無駄だと判断した場合は、関数そのものを渡してしまえば良いのです。
lambda(ラムダ)関数とは匿名関数のことです。
lambdaは文字通り、「pythonよ、関数をくれ」と行っているのです。
その名前は気にしません。ですから、名前を決める必要はありません。
しかし、引数と戻り値は気になります。
ですから、0個以上の引数と戻り値を提供するかどうかは、あなた次第です。
そして、私がそれをしたことに注意してください。
キーワードlambdaに続いて、この匿名の関数が受け取るべき引数の名前を指定しています。そして、次に戻り値を指定しています。
lambda 引数: 戻り値
lambda title: titles[title]
lambda関数では、returnを指定する必要はありません。
:コロンの後に書いたものは文字通り自動的に戻り値として返ってきます。
繰り返しになりますが、これは非常にPythonicなことなのです。
初めて見る人には少しわかりにくいかもしれませんが、非常に賢いワンライナーです。
しかし、考えていることを簡潔な文章に凝縮して仕事を終わらせることができるので、自分や他の人が追跡しなければならないような関数をどんどん定義する必要はありません。

いいでしょう。
この件について何か質問はありますか？
今日のpythonコードはこれくらい複雑で洗練されたものになると思いますよ。
オーディエンス(ソフィア)「他のキーワードではなく、なぜlambdaが特別に使われているのかが疑問です。」
↑ええ。これには長い歴史があります。
実際、関数型プログラミングのコース(ハーバード大学ではCS51と呼ばれている)を受講すると、このようなキーワードの背後にある全体的な語源を知ることができます。
その話はまた別の機会にしましょう。
しかし、pythonに限らず、他の言語でも、このようなものはlambda関数と呼ばれるようになりました。
つまり、他の言語でも実際によく使われているのです。
で、pythonがその言葉を採用しただけなんです。
数学的には、lambdaは関数の記号としてよく使われます。
それで、プログラミングの世界でも同じ考え方を借りた、というわけです。

他に質問がなければ、pythonを使って関連する問題を解決してみましょう。
しかし、データをCSVファイルで保存するとなると、効率性の限界に挑戦することになります。
このファイル、favorites.pyで新たに始めてみましょう。
とはいえ、ここまで書いたコードはすべてコースのウェブサイトに事前に掲載されているので、少しずつ改善されているのがわかります。
やはり一番上でcsvをインポートします。
そして今度は単に自動的にCSVを開いて番組の人気度を計算して分析するだけではないプログラムを書いてみましょう。
CSVの中から特定の番組を検索して、その人気度を出力してみましょう。
これには様々な方法があります。
しかし、ここではできるだけ簡潔に説明したいと思います。
まず、ユーザーにタイトルを入力してもらいます。
title = input("Title: ")
CS50のget_string関数も使えますが、それはpythonのinput関数とほとんど同じであることを思い出してください。
そこで、今日はpythonのinput関数を使います。
そして先ほどと同じようにFavorite TV Shows - Form Responses 1.csvというCSVファイルをfileという変数として読み取り専用モードで開きます。
with open("Favorite TV Shows - Form Responses 1.csv","r") as file:
リーダーにはDictReaderを使うので、どの列に入っているかを気にする必要はありません。
reader = csv.DictReader(file)
気になるタイトルが１つだけなら、このプログラムはもっとシンプルにできます。
すべての番組の人気度を把握する必要はありません。
人間が入力した一つの番組のタイトルの人気度を調べれば良いのです。
そこで、counterという非常にシンプルなintを用意して、それを0に設定します。
counter = 0
辞書全体は必要なく、一つの変数だけで十分です。
そして、先ほどと同様にreaderの中で行に関して反復します。
for row in reader:
そして、if row["title"] == title:
counter += 1
とします。
counterは7行目で初期化しているので問題ありません。
そして、このプログラムの最後でシンプルにcounterの値をプリントアウトします。
このプログラムの目的は、ユーザーに番組のタイトルを入力させ、ファイル内のその番組のインスタンス数をカウントすることで、その人気度を報告することです。
それでは実行してみましょう。

▼favorite2.py
import csv

title = input("Title: ")

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"] == title:
        counter += 1

print(counter)

The Officeと入力すると、19という値が返ってきます。
この数字を正確には覚えていません。
でもThe Officeはこれよりも人気があったことは覚えています。
19でなかったことは確かです。
このプログラムがバグだらけなのか、そう思われる理由について何か直感的な意見はありますか？
ブライアン「チャットで何人かの人が文字の大きさと空白の処理を忘れていると言っています。」
↑ええ。以前に学んだ同じ教訓を実践する必要があります。
ユーザーが入力した情報とCSVから送られてきた情報の両方を正規化する必要があります。
最も簡単な方法は、私が誤ってスペースを入力した場合のために、ここでまず先頭と末尾の空白を削除することです。そして、強制的に大文字にしてしまいましょう。
大文字でも小文字でも構いませんが、少なくともこの方法で物事を標準化しましょう。
title = input("Title: ").strip().upper()
row["title"]についても同じ処理をする必要があります。
if row["title"].strip().upper() == title:
それではもう一度実行してみましょう。
(正しい投票数が表示される)
ほら。先ほどと同じ数字になりました。
実際のところ、ユーザーである私は少しずさんなこともできます。
小文字でタイピングしても、the officeの後に余計なスペースを入れても同じように動作します。
確かに、ここではホワイトスペースを削るなど、衒学的なことに凝っているように見えますが、考えてみてください。
比較的少数のオーディエンスの中で、どれだけの人が誤ってスペースを押してしまったり、大文字と小文字を間違えたりしたでしょうか？
このようなことは規模が大きくなればなるほど起こります。
ソーシャルメディアのアカウントで友人をタグ付けしているときにこれが重要になることは想像に難くありません。
例えば@Brianのようにね。
ユーザーが@や大文字のB、小文字のrianなどを入力する必要はありません。
このように、バラバラで面倒なユーザーの入力を許容することは、私たちが使っている今日のアプリを含め、解決すべき共通の問題なのです。

では、このプログラムについて何か質問はありますか？
では、私からも質問させてください。
このプログラムはどのような意味で貧弱なのでしょうか？
このプログラムはどのような意味でデザインが悪いのでしょうか？
これはもっと微妙ですね。
しかし、このプログラムの実行時間を計算量の観点から考えてみましょう。
CSVファイルにn個の異なる番組が入っていたり、n個の異なる投稿があったりした場合、このプログラムの実行時間はどうなりますか？
つまり、nがここで問題となる変数です。
実行時間はどのくらいですか、アンドリュー？
オーディエンス(アンドリュー)「O(n)です。」
↑ええ。O(n)です。なぜなら文字通りforループによる線形探索を使っているからです。
pythonのforループは、C言語の場合と同じように最初から最後まで続く可能性があります。
つまり、私は暗黙のうちに線形探索を使っています。
なぜなら派手なデータ構造やset、辞書を使っていないからです。
上から下へとループしているだけです。
想像してみてください。もし私たちがこのクラスの学生だけでなく、キャンパスの全員、あるいは世界の全員を対象に調査しているとしたら…あるいは対象はインターネット・ムービー・データベース、IMDbかもしれません。
膨大な数の投票があり、膨大な数の番組があるかもしれません。
プログラムを書くとき、それがターミナルウィンドウであろうと、モバイル・デバイスであろうと、ラップトップやデスクトップのウェブページであろうと、ひとつの質問に答えるためにデータベース内のすべての番組について上から下へと常にループさせるのは、恐らく最良のデザインではありません。
log n時間や一定時間で処理する方がずっと良いでしょう。
ありがたちことに、この数週間、C言語とpythonの両方で、これを行うためのよりスマートな方法を見てきました。
しかし、私はここで説いたことを実践していません。
そして実際、ある時点で、このフラットファイルデータベースという概念は、私たちにとってあまりにも原始的なものになり始めます。
CSVファイルのようなフラットファイルデータベースは、何かを素早く行いたいときや、Googleのような第三者から標準的でポータブルな方法でデータをダウンロードしたいときにとても便利です。
ポータブルとは、様々な人、様々なシステムで使用できることを意味します。
CSVは非常にシンプルで、Microsoft WordやApple Numbersなどの特定の製品を所有する必要がありません。
ただのテキストファイルですから、テキスト編集ソフトでもプログラミング言語でもアクセスできます。
しかし、フラットファイルデータベースは効率的な検索ができないため、大規模なデータセットには必ずしも適した構造ではありません。
CSCファイルの場合、上から下、左から右へと検索するのが精一杯です。
しかし、データを保存するファイルではなく、データを保存するプログラムであるリレーショナルデータベースと呼ばれる優れたデータベースが存在します。
公平に見て、これらのプログラムはデータを実際に保存するために多くのRAM、メモリを使用します。
そして、それらは確かにデータを保存します。
データをファイルに保存することで、データを長期的に保存します。
しかし、あなたとあなたのデータとの間には、この実行中のプログラムがあります。
Oracle、MySQL、PostgreSQL、SQL Server、Microsoft Accessなど、商用、フリー、オープンソースを問わず人気のある製品をご存知かもしれませんが、これらのリレーショナルデータベースは、スプレッドシートと精神的に似ています。
しかし、これらはソフトウェアで実装されています。
そして、どんどん機能が増えていきます。
そして、より多くのデータ構造を使用しているので、CSVファイルを使用するよりもはるかに効率的に、データの検索、挿入、削除、更新を行うことができます。
それではここで5分間の休憩としましょう。
戻ってきたら、リレーショナルデータベースと、その中のSQL(シークル)と呼ばれる言語について見てみましょう。

戻ってきました。
目下の目標は、かなり単純化されたフラットファイルデータベースから、より適切なリレーショナルデータベースに移行することです。
リレーショナルデータベースは、今日のモバイルアプリケーションやWebアプリケーションなどの多くを支えています。
そして今、私たちは実世界のソフトウェア、実世界の言語へと移行し始めています。
ここでは、SQLite(シークライト)と呼ばれるものを紹介します。
リレーショナルデータベースとは、すべてのデータを行と列に分けて保存するデータベースのことです。
しかし、表計算ソフトやシートを使って保存するのではありません。
代わりにテーブルと呼ばれるものを使って保存します。
つまり、考え方はほぼ同じです。
しかし、テーブルを使うことで、いくつかの追加機能を得ることができます。
テーブルにはデータの検索、更新、削除、新規挿入などの機能があります。
これらは表計算ソフトでも絶対にできることです。
しかし、スプレッドシートの世界では、何かを検索しようと思ったら人間であるあなたが手動でクリックしたりスクロールしたりして行うのが普通です。
データを挿入するには新しい行を追加した後、人間が手で入力しなければなりません。
何かを削除したい場合は、右クリックやCtrlキーを押しながら行全体を削除したり、個々のセルを更新したりすることになります。
SQL(Structured Query Language)は、他のプログラミング言語と組み合わせて使用されることが多い新しいプログラミング言語です。
今日、私たちはSQLを単独で使用します。
また、pythonのプログラムの中でもSQLを使います。
pythonのような言語は、SQLを使ってpythonだけではできないような強力なことができるのです。
そう考えると、SQLiteはSQLのライトバージョンのようなものです。
よりユーザーフレンドリーで移植性にも優れています。
MacでもPCでも携帯でも、ラップトップでもデスクトップでもサーバーでも使うことができます。
しかし、これは非常に一般的なものです。
実際、あなたが使っているiPhoneやAndroid端末では、今あなたが使っているアプリケーションの多くがSQLiteを利用しています。
ですから、SQLiteは決しておもちゃの言語ではありません。
一般にSQLと呼ばれている言語を比較的シンプルに実装したものです。
話が長くなりましたが、世の中には他にもリレーショナルデータベースの実装があります。そして、そのうちのいくつかについてはすでに説明しました。
OracleやMySQL、PostgreSQLなどですね。
それらはすべて、SQLの微妙に異なるフレーバーや方言を持っています。
つまり、SQLはデータベースを操作するためのかなり標準的な言語なのです。
しかし、様々な企業やコミュニティが、自分たちの好みの機能を追加したり、削除したりしています。
そのため、使用する構文はすべてのプラットフォームで共通しています。
しかし、このコースではSQLiteを標準とすることにします。
実際、最近のモバイルアプリケーションの世界では、SQLiteを使うのが一般的です。
これは非常に重要なことです。
SQLiteを使えば、データを照会したり、更新したり、削除したりすることができます。
しかし、そのためにはデータベースと対話するためのプログラムが必要になります。
SQLiteの仕組みは、すべてのデータを１つのファイルに保存する、というものです。
しかし、それはバイナリファイルです。
つまり、0と1を含むファイルです。
この0と1はテキストを表しているかもしれません。
数字を表すかもしれません。
しかし、ASCIIやUnicodeを使った単なるCSVファイルよりもコンパクトで効率的な表現になっています。
これが最初の違いです。
SQLiteは、1つのファイル、つまりバイナリファイルを使って、全てのデータを保存し、そのファイルの中で0と1、あるいは前に言及したテーブル(スプレッドシートに相当します)を使ってデータを表現します。
すべてのデータが保存されているバイナリファイルを操作するためには、何らかのユーザーフレンドリーなプログラムが必要です。
使えるツールはたくさんあります。
SQLiteに付属する標準的なものはsqlite3と呼ばれるもので、基本的にはこのツールのバージョン3です。
これはこれまでのターミナルウィンドウで実行してきたコマンドと同じような精神のコマンドラインツールで、バイナリファイルを開いて全てのテーブルを操作することができます。
さて、ここでまた鶏と卵の問題が発生します。
データベースを使用したいが、まだデータベースを持っていない、しかしデータベースからデータを選択したい場合、実際にはどのようにしてデータを読み込むのでしょうか？
SQLiteデータベースにデータを読み込むには、少なくとも2つの方法があります。
1つ目は、これからやりますが、既存のフラットファイルデータベース(CSVなど)をインポートする方法です。
MacやPCのCS50 IDEにCSVを保存しておきます。
sqlite3で特別なコマンドを実行します。
するとCSVをメモリに読み込みます。
カンマがどこにあるかを把握します。
そして、そのバイナリファイルの中に、対応する行と列を適切な0と1を使って構築し、すべての情報を保存します。
つまり、自動的にインポートしてくれるのです。
2つ目の方法は、pythonなどの言語で実際にコードを書き、すべてのデータを手動でデータベースに挿入する方法です。
それもやってみましょう。
しかし、まずは簡単な方から始めましょう。
例えば、sqlite3を実行してみましょう。
これはCS50 IDEにプリインストールされていますが、MacやPCでも起動するのはそれほど難しくありません。
ターミナルウィンドウでsqlite3を実行してみます。
(sqlite3とターミナルウィンドウに入力すると以下のようなメッセージが表示される)
~/ $ sqlite3
SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
sqlite>
ほら。
非常にシンプルな出力が表示されています。
使い方のヒントを見たければ「.help」と入力します。
しかし、私はほとんどのコマンドを知っていますし、私たちは、皆さんが必要とするであろう全てのコマンドを提供できると思います。
実際、「.mode」や「.import」などのコマンドがあります。
通常、これらのコマンドを頻繁に使うことはありません。
既存のCSVファイルから初めてデータベースを作成するときに使うくらいでしょうか。
そして、それが今の私の目標です。
皆さんの好きなテレビ番組が入ったCSVファイルを、適切なリレーショナルデータベースであるSQLiteにロードして、データの検索やその他の作業を、例えばO(n)よりも上手く行えるようにしましょう。
そのためには2つのコマンドを実行する必要があります。
まず、SQLiteをCSVモードにする必要があります。
.mode csv
これは、タブ区切りのTSVなどの他のフラットファイルデータベースと区別するためです。
そして「.import」を実行します。
インポートするファイルの名前を指定します。
そして、そのテーブルをshowsと呼ぶことにします。
.import 'Favorite TV Shows - Form Responses 1.csv' shows
.importは2つの引数を取ります。インポートしたいファイルの名前と、そのファイルから作成したいテーブルの名前です。
.import FILE TABLE
繰り返しになりますが、テーブルには行と列があります。
ファイル内のカンマはそれらの列がどこから始まり、どこで終わるかを示すものです。
それではEnterキーを押してみましょう。
(特に何も表示されない)
あっという間に終わったようです。
何も起きていないように見えます。
でも大丈夫です。これからは、実際にデータを操作できるようになります。
でも、どうやってデータを操作するのでしょうか？
新しい言語が必要です。
SQL(Structured Query Language)はSQLite、Oracle、MySQL、PostgreSQL、その他多くの製品で使われている言語で、当面はその名前を知ったり覚えたりする必要はありません。
しかし、SQLは、データベースに情報を照会し、それを使って何かをするために使用する言語です。
一般的に言って、リレーショナルデータベースとリレーショナルデータベースと対話するための言語であるSQLは、4つの基本的な操作をサポートしています。
この4つの基本操作は、リレーショナルデータベースの基本操作を思い出すのに便利なようにそれぞれの頭文字を取ってCRUDと呼ばれます。
CRUD
Create(作成)
Read(読み取り)
Update(更新)
Delete(削除)
作成とは、新しいデータの作成や追加を意味します。
読み取りとは新しいデータにアクセスしてメモリに読み込むことです。
読み取りはファイルを開くときにも使われました。
更新と削除はデータセット内のデータを操作する場合に文字通りの意味を持ちます。
これらはあらゆるリレーショナルデータベースに共通の操作です。
この4つのプロパティはリレーショナルデータベースで一般的にサポートされているものです。
SQLの世界では、これらの4つの機能を実装する非常に特殊なコマンドや関数があります。
それがcreateとinsertです。一般的にはcreateと同じことを実現します。
データベースからデータを読み込むにはselectというキーワードが使われます。
updateとdeleteも同じです。
create,insert
select
update
delete
これはCRUDとの厄介な不整合ですね。
しかし、SQLの世界では、言語の作者はこれら4つのアイデアをSQL言語の5つのキーワード、関数、コマンドによって実装することにしました。
ここではSQLという新しい言語で、実際にデータベースに何かをするために使える5つのキーワードを紹介します。
さて、これはどういうことでしょうか？
例えば、あなたが初めて手動でデータベースを作成するとします。
どうしますか？
スプレッドシートの世界では、それはとても簡単なことです。
Googleスプレッドシートを開きます。
「ファイル」「新規作成」などを選択します。
そうすると、ほら、新しいスプレッドシートができて、そこに行や列などを作り始めることができます。
Microsoft ExcelでもApple Numbersでも同じように「ファイル」メニューから「新規スプレッドシート」などを選択すると、新しいスプレッドシートが出来上がります。
SQLの世界では、SQLデータベースは一般的にコードで操作することを前提としています。
しかし、GUI(グラフィカル・ユーザー・インターフェース)を使ってデータベースを操作することもできます。
しかし、今日はコードを使って、コマンドラインでプログラムを実行してみましょう。
このようなコマンドで実行すれば、プログラムでテーブルを作成できます。
CREATE TABLEのような構文を文字通り入力し、テーブルの名前をここでは小文字で示し、次に括弧を付け、作成したい列の名前とその列のタイプをC言語のように入力し、更にいくつかの列を入力します。これは一般的にSQLと呼ばれる言語で新しいテーブルを作成するときに使用する構文です。
CREATE TABLE table (column type, ...);
これは抽象的な表現です。
繰り返しますが、小文字のtableは実際のテーブルに付ける名前を表しています。
小文字のcolumnは自分の列に付ける名前を表しています。
タイトルかもしれないし、ジャンルかもしれません。
そして、...はそれ以上の列を持つことができるということです。
sqlite3プログラムを実行した後、ターミナルウィンドウにこのようなコマンドを入力すれば、すぐに1つ以上のテーブルを作成することができます。
実際、私の場合はすでにそうなっています。
この.importコマンドはSQLの一部ではありませんが、ExcelやGoogleスプレッドシートのメニューオプションに相当します。
.importコマンドは、特定のプロセスを自動化します。
私の場合はこうでした。
今、.schema(スキーマ)と入力すると、これもまたSQLite特有のコマンドですが、(.で始まるものは全て、このターミナルウィンドウプログラムであるsqlite3にのみ特有のものです。)出力は次のようになります。
sqlite> .schema
CREATE TABLE IF NOT EXISTS "shows"(
"Timestamp" TEXT,
"title" TEXT,
"genres" TEXT
);
.importを実行すると、データベースの中に自動的にshowsというテーブルが作られました。
そして、そこには3つの列、Timestamp、title、genresが与えられました。
この列名はどこから来たのでしょうか？
それはCSVの最初の行からです。
そして、それらは全てテキストのように見えたので、これらの値のタイプは単にTEXT、TEXT、TEXTと仮定されました。
これを手動で入力してshowsという新しいテーブルにこの3つの列を作ることも出来ました。
しかし、繰り返しになりますが、.importコマンドはCSVを基にそれを自動的に行いました。
SQLは、ここで見られるようなCREATE TABLE showsなどのコマンドです。
つまり、このデータベースにはshowsというファイルというか、テーブルがあって、その中にCSVからのデータが全て入っています。
実際にそのデータを取得するにはどうすればいいのでしょうか？
その場合にはCREATEだけでなく別なコマンドであるSELECTが呼ばれます。
SELECT columns FROM table;
SELECTはreadに相当し、データベースからデータを取得します。
このコマンドは非常に強力です。
多くのデータサイエンティストや統計学者がSQLのような言語を好んで使う理由は、データの取得、フィルタリング、分析が比較的簡単に出来るからです。
SQLのSELECTコマンドはテーブルから1つ、または複数の列を、指定された名前で選択します。
では、すぐにこの例を見てみましょう。
これを行うにはどうすればいいでしょうか？
それでは、ウィンドウをクリアしてスッキリさせてから、プロンプトで試してみましょう。
例えば、SELECT title FROM shows;としてみます。
なぜこのようなことをするのでしょうか？
繰り返しになりますが、SELECTコマンドの一般的な書式は、
SELECT、1つまたは複数の列の名前、前置詞FROM、そしてデータを選択したいテーブルの名前の順です。
つまり、テーブルの名前がshowsで、列の名前がtitleであれば、SELECT title FROM shows;で欲しいデータが得られます。
さて、厳密には必須ではありませんが、優れたスタイルであるとされているものがいくつかあります。
一般的には、SELECTやFROMなどのSQLキーワードは全て大文字にして、列の名前やテーブルの名前は小文字にします。ここではこれらの列やテーブルを小文字で作成するものとします。
世の中には様々な慣習があります。
大文字にする人もいます。
キャメルケースやスネークケースなどと呼ばれるものを使う人もいます。
しかし、一般的に言えば、SQL構文は全て大文字にして、列やテーブルの名前は小文字にすることをお勧めします。
Enterキーを押してみます。
(csvファイルの全てのtitleが表示される)
ほら。早速、データベースから出力された値のリストを見てみましょう。
CSVファイルがデータベースのテーブルに上から下へと読み込まれているので、実際には以前と同じ順序になっていることに気づくかもしれません。
ですから、実際に今見ているのは、重複や大文字小文字の違い、誤った空白の挿入など、全て同じデータなのです。
しかし、CSVからの全てのデータを見たいとします。
その場合には複数の列を選択することもできます。
Timestampを追加してみます。
sqlite> SELECT Timestamp, title FROM shows;
そこで、列名をカンマで区切ったリストを使うと、どんなことができるか見てみましょう。
(Timestampとタイトルが表示される)
色々なことが起こっているので、人間にとっては少し見づらいかもしれません。
左側の""二重引用符の中には、皆さんがお気に入りの番組を投稿した時間を表すTimestampが入っていることに注目してください。
カンマの右側には、もう１つの引用符で囲まれた文字列があります。これは、皆さんが気に入った番組のタイトルですが、SQLiteは、Friendsのように単一の単語の場合は、慣習的に引用符を省略します。
全ての列を取得したい場合には、そのための短縮構文があります。
*は、いわゆるワイルドカード演算子です。
sqlite> SELECT * FROM shows;
これは、テーブルの左から右まで、全ての列を取得します。
sqlite> SELECT * FROM shows;
Timestamp,title,genres
"10/19/2020 13:34:57","The Office",Comedy
"10/19/2020 13:35:03","The Office",Comedy
…
(CSVの全ての項目・列の情報が表示される)
これで全てのデータが表示され、ジャンルも全て表示されました。
つまり、3つの列が一度に出力されていることになります。
でも、これは今のところあまり役に立っていません。
実際にはCSVの内容を出力しているだけです。
しかし、SQLが強力なのは、GoogleスプレッドシートやExcelに搭載されている関数と似たような機能がそのまま搭載されているからです。
それらを最終的には自分のコードの中で使うことができます。
AVG
COUNT
DISTINCT
LOWER
MAX
MIN
UPPER
↑上記のような関数がSQLに組み込まれており、クエリの一部として使用することで、データベースから戻ってきたデータをその時々の必要に応じたフォーマットに変更することができます。
例えば、以前の私の目標の１つは、個別のユニークなタイトルだけを受け取ることでした。
そのためには、setを使って面倒なコードを書き、setに何かを追加して、またそれをループさせる必要がありました。
大した量のコードではありませんでしたが、少なくともその仕事を終わらせるのに5～10分はかかっていたと思います。
SQLでは、このような作業を一度に行うことができます。
では、早速やってみましょう。
単にタイトルを選択するのではなく、SELECT DISTINCT(title) FROM shows;とします。
SELECT DISTINCT(title) FROM shows;
DISTINCTはSQLで利用可能な関数で、名前通り(明確な、ほかのものとは別の…という意味)の機能を持ちます。
つまり、全てのタイトルをフィルタリングして、個別のタイトルだけを返してくれます。
ここでEnterを押すと、同じようにごちゃごちゃしたリストが表示されますが、その中にはテレビを見ない人の「わからない」を含む、ソートされていないタイトルのリストが含まれています。
sqlite> SELECT DISTINCT(title) FROM shows;
title
"The Office"
"the office"
"The Office "
Friends
"the office "
"The office"
FRIENDS
"Friends etc."
"friends "
office
friends
"Sherlocks; Friends "

そこで、先ほどと同じように、このリストを整理することにしましょう。
では、DISTINCTを適用するだけでなく、全て大文字にしてみましょう。
UPPER()を追加してみます。
sqlite> SELECT DISTINCT(UPPER(title)) FROM shows;
入れ子にしているだけであることに注意してください。
今まで多くの言語で見てきたように、ある関数の出力は別の関数の入力になります。
これでEnterを押してみましょう。
(大文字になったタイトルのリストが表示される)
sqlite> SELECT DISTINCT(UPPER(title)) FROM shows;
(UPPER(title))
"THE OFFICE"
"THE OFFICE "
FRIENDS
"FRIENDS ETC."
"FRIENDS "
OFFICE
"SHERLOCKS; FRIENDS "

これで少しは正規化されました。全てに大文字が使われています。
しかし、まだソートされていないようです。
入力したのと同じ順番になっていますが、今回は重複していません。

SQLには、クエリ(問合せ)をより正確に、より強力にするために使える他の構文があります。
表示されるデータや戻ってくるデータを変更するための関数に加えて、SQLクエリでは以下のような句や構文を使用することができます。
WHERE
LIKE
ORDER BY
LIMIT
GROUP BY

条件分岐に相当するWHEREという句があります。
「このデータの中で、あるものについて真(または偽)であるものを全て選択してください。」と言うことができます。
LIKEを使えば、「正確にはこれではないが、これに似ているデータをくれ」ということができます。
ORDER BYで、ある列の内容をもとに、データを並べることができます。
LIMITでは、戻ってくる行の数を制限することもできます。
GROUPでは、同じ値を持つものを何らかの方法でグループ化することもできます。

では、いくつかの例を見てみましょう。
The Officeで遊んでみましょうか。
SELECT title FROM shows WHERE title = "The Office";
WHERE title = "The Office"という、いわばWHERE述語を追加しています。
(タイトル列からThe Officeが全て抽出される)
"The Office"
"The Office"
"The Office"
"The Office"
"The Office"
…
SQLは素晴らしいですね。
より正確であるにも関わらず、pythonの精神に似ていて、全てが英語の文章のように読めるという点で、C言語よりユーザーフレンドリーです。そして、より簡潔です。
これだけの人がThe Officeと入力しているわけですが、これで全部ではないですよね？
まだ何人か足りないですね。
文字通り、"The Office"、大文字のT、大文字のOを入力した人の分だけ返ってきたようです。
もし、もう少し柔軟性が欲しいと思ったら？
では、Officeと入力された行を探してみましょう。
冠詞のTheを省略しているかもしれません。
そこで、title = "Office"ではなく、title LIKE "Office"としてみます。
しかし、ただのOfficeにはしたくありません。
最初と最後に何かを入れることができるようにしたいのです。
ちょっと矛盾しているように見えますが、LIKEを使う文脈では、もう一つワイルドカード文字があります。
title LIKE "%Office%";
左側の%記号は、左に0文字いじょうの文字があることを表します。
そして、右側の%記号は、右に0文字以上の文字があることを表します。
つまり、Officeを含むすべてのタイトルを検索してくれる、包括的なキーワードのようなものです。
また、LIKEは大文字小文字を区別しないので、LIKEを使う場合は大文字小文字を気にする必要がありません。
では、Enterを押してみましょう。
sqlite> SELECT title FROM shows WHERE title LIKE "%office%";
title
"The Office"
"The Office"
"the office"
…
(大文字小文字区別なく、前後のスペースやTheの有無にも関わらずThe Officeに近いタイトルが出力される)
ほら。これで、より多くの答えが返ってきました。
色んな入力をされているのがよくわかりますね。
ここで一人が小文字を使っていることに注意してください。
これは、素早く入力するときによくあることです。
ある人は、ここで小文字にして、最後に余計な空白を入れています。
ある人は単にOfficeとだけ入力しました。
the officeと入力した後、最後にスペースを入れた人がもう一人います。
このように、様々なバリエーションがあります。
だからこそ、全てを強制的に大文字にして、端を切り詰めることによって、多くの冗長性を取り除くことができたのです。
さて、実際に今から実行してみましょう。
個別の大文字のタイトルを選択するところまで戻って、
SELECT DISTINCT(UPPER(title)) FROM shows ORDER BY UPPER(title);
としましょう。
ここでは、新しい句であるORDER BYを使って、タイトルの大文字バージョンを指定しています。
さて、ここでいくつかのことが起こっていることに注意してください。
しかし、私は最初の頃と同じように、より複雑なクエリを構築しているだけで、問題に対してパズルのピースをどんどん投げているだけです。
showsから、大文字のタイトルを全て選択しています。
今回はタイトルの大文字バージョンを利用して、結果を順に並べます。
つまり、全てが大文字になり、アルファベット順にソートされます。
Enterを押すと少し意味がわかりやすくなります。

sqlite> SELECT DISTINCT(UPPER(title)) FROM shows ORDER BY UPPER(title);
(UPPER(title))
FRIENDS
"FRIENDS "
"FRIENDS ETC."
OFFICE
"SHERLOCKS; FRIENDS "
"THE OFFICE"
"THE OFFICE "

""引用符は、タイトルに複数の単語が含まれる場合にのみ使われます。
それ以外の場合、sqlite3はわざわざ表示しません。
ここにはTHEで始まる全てのタイトルが並んでいます。
更に上にスクロールしていくと、P、N、M、L…などなど
ORDER BYを使ったおかげで、確かにアルファベット順になっています。
いいでしょう。
では、もっと似たような問題を、先ほどpythonでやったときよりもずっと少ないコードで解いてみましょう。
例えば、最も人気のある番組の票数を確認したいとします。
それぞれの番組について表を加え合わせて、全ての番組の票数を把握したいのです。
では、試してみましょう。
もう一度、タイトルの大文字バージョンをSELECTしてみましょう。
しかし、今回はDISTINCTを使うつもりはありません。
なぜなら、少し違った方法で行いたいからです。
タイトルの大文字バージョンとともに、それらのタイトルのCOUNT(COUNTは出現回数をカウントするための新しいキーワードです。)を選択します。
SELECT UPPER(title), COUNT(title) FROM shows
でも、その回数をどうやって知ることができるのでしょうか？
このテーブルにたくさんのタイトルがあることを考えると、同一のタイトルをグループ化して、グループ化したタイトルの数を実際に数えられたら良いですね。
そのための構文は、文字通りGROUP BY UPPER(title);となります。
SELECT UPPER(title), COUNT(title) FROM shows GROUP BY UPPER(title);
これはSQLに対して、大文字のタイトルを全てグループ化し、複数の行を1つにまとめるように指示しています。
それでは、Enterを押してみましょう。
UPPER(title),COUNT(title)
FRIENDS,26
"FRIENDS ",1
"FRIENDS ETC.",2
OFFICE,1
"SHERLOCKS; FRIENDS ",1
"THE OFFICE",23
"THE OFFICE ",3

以前に書いたpythonのプログラムと非常によく似ていますが、左の全てのタイトルの後にカンマが続き、その後にカウントが表示されます。
The Officeを好きな人は23人いるようですが、ここではまだ端を切り捨てていません。
ですから、必要であれば、空白を削って数え直すこともできます。
しかし、今はこのような数になっています。
さて、どうやってこれを以前と同じような順に並べればいいのでしょうか？
ここで、ORDER BY COUNT(title)を追加してみましょう。

sqlite> SELECT UPPER(title), COUNT(title) FROM shows GROUP BY UPPER(title) ORDER BY COUNT(title);
UPPER(title),COUNT(title)
"FRIENDS ",1
OFFICE,1
"SHERLOCKS; FRIENDS ",1
"FRIENDS ETC.",2
"THE OFFICE ",3
"THE OFFICE",23
FRIENDS,26

pythonの場合と同じように、最初は小さいものから順に並んでいて、ここではFRIENDSが一番下になっています。
これを修正するにはどうすればいいでしょうか？
そのためには並べ方を降順にするよう指定します。
つまり、デフォルトの並べ方であるASCの代わりにDESCを指定します。
sqlite> SELECT UPPER(title), COUNT(title) FROM shows GROUP BY UPPER(title) ORDER BY COUNT(title) DESC;
この場合は、どこで行が始まるかを見るために、上の方までスクロールしなければなりません。

sqlite> SELECT UPPER(title), COUNT(title) FROM shows GROUP BY UPPER(title) ORDER BY COUNT(title) DESC;
UPPER(title),COUNT(title)
FRIENDS,26
"THE OFFICE",23
"THE OFFICE ",3
"FRIENDS ETC.",2
"SHERLOCKS; FRIENDS ",1
OFFICE,1
"FRIENDS ",1

それでは、上位3個にLIMITして見てみましょう。
sqlite> SELECT UPPER(title), COUNT(title) FROM shows GROUP BY UPPER(title) ORDER BY COUNT(title) DESC LIMIT 3;
UPPER(title),COUNT(title)
FRIENDS,26
"THE OFFICE",23
"THE OFFICE ",3

思った通りの順で出力できましたが、まだ何かが足りないですね。
ブライアン、先頭と末尾のホワイトスペースをトリミングするSQL関数を覚えていますか？
ブライアン「TRIMで良かったと思います。」
↑TRIMですね。OKです。
私も覚えていませんでした。
迷ったときはググるか、ブライアンに聞いてみてください。
では、先にこれを修正してみましょう。
UPPER(TRIM(title))、GROUP BY UPPER(TRIM(title))とします。
これで出来上がりです。
sqlite> SELECT UPPER(TRIM(title)), COUNT(title) FROM shows GROUP BY UPPER(TRIM(title)) ORDER BY COUNT(title) DESC LIMIT 10;
UPPER(TRIM(title)),COUNT(title)
FRIENDS,27
"THE OFFICE",26
"FRIENDS ETC.",2
"SHERLOCKS; FRIENDS",1
OFFICE,1
これでThe Officeはようやく26票になりました。
SQLでここまで話を進めるのに少し時間がかかりました。
しかし、私たちがしたことに注目してください。
私たちは、pythonでは数分の時間と十数行のコードを要したプログラムを取り上げました。
それを、新しい言語ではありますが、単なるワンライナーのようなものに集約したのです。
SQLのような言語を使いこなせるようになると、特にあなたがコンピュータサイエンティストではなく、データサイエンティストや金融情報や医療情報、あるいは行や列に読み込むことができるあらゆるデータセットを一日中見ているようなアナリストであれば、かなり強力なクエリを比較的簡潔に表現できるようになります。
sqlite3のようなコマンドラインプログラムを使えば、非常に単純なテキストではありますが、結果をすぐに見ることができます。
しかし、前述したように、SQLをサポートするフリーまたは商用のグラフィカルなプログラムもあり、そこでこれらのコマンドを入力することもできます。
その場合は、WindowsやMacOSのように、よりユーザーフレンドリーな方法で表示されます。

それでは、SELECT文の構文や機能について、何か質問はありますか？
ブライアン「１つ質問がありました。このデータのファイルは実際にはどこに保存されているのでしょうか？」
↑良い質問ですね。
ファイルは実際にはどこに保存されているのでしょうか？
終了する前に、このファイルを任意の名前で保存することができます。
ファイルの拡張子は、通常.dbです。
ところでブライアン、ちょっと確認してもらえませんか？
.dbを使って手動でファイル名を指定する場合の構文はどうなっていますか？
.helpの下にあると思うのですが。
ブライアン「.saveの後にファイル名を入力すると思います。」
.save shows.dbとしてEnterします。
次に、別のターミナルウィンドウを開いて、お馴染みのlsコマンドを入力すると、shows.dbというファイルがあることがわかります。
これはCSVから動的に読み込んだテーブルを含むバイナリファイルです。

SELECT文について、他に何か質問はありますか？
ブライアン「何人かの人が、このクエリの実行時間について質問しています。」
↑ええ。本当に良い質問ですね。
ランタイムはどのくらいなのでしょうか？
その質問には、もう少し後に戻ってきますが、よろしいでしょうか？
今のところ、確かにO(n)です。
CSVファイルやpythonコードでやったこと以上のことは実際にはまだできていません。
今のところ、デフォルトではまだO(n)です。
しかし、もっと対数的なものにするための良い答えが出てくることでしょう。
その機能を有効にするときには、またお話します。

しかし、そのためのいくつかのステップを始めましょう。
というのも、データを読み込む際にCSV形式の大きなファイルを1つだけ用意して、それを読み込んで仕事をするというような贅沢なことはできないからです。
どのようにデータを保存するのか、どのようなデータを保存するのか、1つのテーブルではなく複数のテーブルにどのような関係を持たせるのかを事前に決めておく必要があります。
では、ここでもう一つのコマンドを実行してみましょう。
このコマンドは、実際には最初の問題を引き起こします。
ターミナルウィンドウに戻って、次のコードを書いてみましょう。
sqlite> SELECT title FROM shows WHERE genres = "Comedy";
title
"The Office"
"The Office"
"the office"
"The Office "
Friends
…
たくさんの結果が返ってきました。
しかし、何か見落としているのではないでしょうか？
私はこれをかなり速く読み飛ばしています。
しかし、私は何か見落としています。「genres = "Comedy"」とした場合、何を省略しているでしょうか？
複数のボックスにチェックを入れた人は、コメディとドラマ、コメディとロマンスなど、いくつかのジャンルを組み合わせているかもしれませんね。
ここで「genres = "Comedy"」とすると、「私の好きなテレビ番組はコメディです」と答えた人の番組だけが表示されることになります。
しかし、代わりにLIKE "%Comedy%"のようにしたいとしたらどうでしょう？
Comedyという言葉が入ってさえいればよいのであれば、さらに多くの結果が得られるはずです。
sqlite> SELECT title FROM shows WHERE genres LIKE "%Comedy%";
(更に多くのタイトルが表示される)
確かに、検索結果のリストは長くなりましたね。
少なくともComedyのボックスにチェックを入れた人が入力したタイトルは全て出てきました。
しかし、残念なことに、これは少しずさんになり始めています。
なぜなら、genres列がどのように見えるかを思い出してください。
SELECT genres FROM showsとしてみましょう。
(ジャンルが表示される)
sqlite> SELECT genres FROM shows;
genres
Comedy
"Comedy, Documentary, Reality-TV"
Comedy
"Comedy, Reality-TV"
"Comedy, Documentary"
Comedy
CSVファイルからこのテーブルに読み込んだジャンルは、すべてカンマで区切られたジャンルのリストであることに注意してください。
これは、Google Formsがそうしていただけです。
そして、それはCSVの目的のためには良いことです。
SQLの目的のためにも良いことのようですが、実際にはこれはちょっと面倒なことです。
一般的に言って、カンマで区切られた値のリストをSQLデータベースに保存すべきではありません。
SQLデータベースを使う意味は、カンマやCSVから離れて、もっときれいに保存することにあります。
というのも、実際に、ある問題を提案してみましょう。
例えば、Comedyだけでなく、musicも検索したいとします。
そして、musicという単語をカンマで区切られたリストから検索するとします。
SELECT title FROM shows WHERE genres LIKE "%Music%";
ここに微妙なバグがあります。
最初の段階に遡って、あなたが取り出したフォームを思い出す必要があるかもしれません。
ここで全部を見せることはできませんが、アクション、アドベンチャー、アニメーション、バイオグラフィーから始まって、Musicが含まれていました。
その中にはMusicalも含まれていました。
ミュージックビデオとミュージカルは2つの異なるタイプのジャンルです。
しかし、今の私の質問に注意してください。
SELECT title FROM shows WHERE genres LIKE "%Music%";
これのどこが問題なのでしょうか？
今のところ、このクエリではMusicだけでなくMusicalも選択されてしまう、というバグがあるようです。
ここからが問題なのです。
さて、どうでしょう？
これをきれいにすることができます。
Musicの後に,カンマを入れて、Music何とか、でなく、Music,何とかでなければならないようにしましょう。
SELECT title FROM shows WHERE genres LIKE "%Music,%";
でも、もしあなたが最後に入力したのがmusicだとしたら、どうでしょう？
その場合は「,Music」になり、後にカンマがありません。
そうすると、今度はORでつなぐ必要があります。
SELECT title FROM shows WHERE genres LIKE "%Music,%" OR genres LIKE "%Music";
しかし、正直なところ、これはただ面倒なだけです。
これはお粗末な設計です。
もしあなたがデータをカンマで区切られた値のリストとして列に格納していて、それを理解するためにこのようなハックに頼らなければならないとしたら、そして構文のすべての順列を考えなければならないとしたら、それは間違った方法です。
SQLデータベースの機能を最大限活用できていないということです。
では、どのように設計すれば、このCSVをもう少しきれいにデータベースに読み込むことが出来るのでしょうか？
要するに、ジャンルの列にある馬鹿げたカンマを取り除き、代わりにComedyやMusic、Musicalなどの単語をそれぞれのセルに入れるにはどうしたら良いのでしょうか？
2つでも3つでもなく、いくつかのジャンルを捨てずに1つだけ。
では、そのための構成要素をいくつか紹介しましょう。
自分でテーブルを作り、自分でデータベースにデータを読み込む場合、SELECT以外にも必要なものがあります。
もちろん、SELECTは読むだけのものです。
しかし、これをもっと上手くやって、単に組み込みの.importコマンドとしてsqlite3を使うのではなく、すべてのデータを2つのテーブル(1つはタイトル用、もう1つはジャンル用)にロードするコードを書こうとするなら、SQLに関してもう少し表現力が必要になるでしょう。
そのためには自分でテーブルを作成CREATEする機能が必要になります。
CREATE TABLE table(column type, ...);
これは以前にも少しだけ見ました。
また、挿入INSERTという別の構文も必要になります。
INSERTは、SQLデータベース上で実行できる別のコマンドで、実際にデータベースにデータを追加することができます。
これは素晴らしい機能です。
なぜなら、CSVを反復処理しながら、自分で全ての行をデータベースに手動で追加したいというような場合、挿入が必要になるからです。
そのための構文は以下の通りです。
INSERT INTO table(column, ...) VALUES(value, ...);
このように、一般的な形で見ると、少し抽象的ですね。
しかし、もう少し具体的に見てみましょう。
では、データベースに何かを挿入することにします。まずこれを試してみましょう。
仮にThe Muppet Showであるとします。
私はマペット・ショーが大好きでした。
70年代に放送されたものです。
リストにはなかったと思いますが、確かめてみましょう。
SELECT * FROM shows WHERE title LIKE…
とりあえずmuppetsをワイルドカード付きで検索してみましょう。
SELECT * FROM shows WHERE title LIKE "%Muppets%";
誰も入れていないと思います。
(何も表示されない)
入っていなかったようですね。
どうやら入力するチャンスを逃してしまったようです。
戻ってフォームに記入してCSVを再インポートすることもできますが、ここでは手動でやってみましょう。
では、INSERT INTOで何の列を表示させるかというと、titleとgenres、それに念のためにTimestampも入れてみましょう。
INSERT INTO shows (Timestamp, title, genres) VALUES(
さて、値はどのように入れましょう？
その値は、まあ、わかりませんが、今はどんな時間であっても構いません。
"now"だから、日付や時間を調べるのではなく、そこはごまかすつもりです。
titleは"The Muppet show"です。
genresは、コメディのようでもあり、ミュージカルのようでもありました。
INSERT INTO shows (Timestamp, title, genres) VALUES("now", "The Muppet show", "Comedy, Musical");
繰り返しになりますが、これは挿入したいテーブル、挿入したい列、それらの列に入れたい値を指定する標準的な構文に従っています。
INSERT INTO table(column, ...) VALUES(value, ...)
Enterを押してみましょう。
何も起きていないように見えます。
しかし、先ほどと同じクエリを選択すると…
SELECT * FROM shows WHERE title LIKE "%Muppets%";
(何も起きない)
wwああ、わかった、まだ何も起きない、ミスをしていました。
私が検索したいのは複数形のMuppetsではなくMuppetです。
sqlite> SELECT * FROM shows WHERE title LIKE "%Muppet%";
Timestamp,title,genres
now,"The Muppet show","Comedy, Musical"
ほら、これで、データベースに私の行ができました。
このように、INSERTはデータベースに新しい行を挿入する機能を提供します。
例えば、何かを更新したいとします。
マペット・ショーの中には、実際にはかなりドラマチックなものもありました。
では、どうやって更新するのでしょうか？
例えば、
UPDATE shows SET genres = "Comedy,Drama,Musical" WHERE title = "The Muppet Show";
といった具合です。
これは簡単な例に過ぎませんが、非常に簡単に更新することができます。
この構文に慣れるのには少し時間がかかるかもしれませんが、文字通りのことができます。
実行し、もう一度マペット・ショーの行をSELECTしてみます。
sqlite> SELECT * FROM shows WHERE title LIKE "%Muppet%";
Timestamp,title,genres
now,"The Muppet show","Comedy,Drama,Musical"
ほら。
マペット・ショーはコメディでもあり、ドラマでもあり、ミュージカルでもあることがわかりました。
しかし、私が問題にしているのはリストにあった人気番組の１つです。
多くの人が好きなのは例えばFRIENDSですが、私はあまり好きではありません。
ここで、
SELECT title FROM shows WHERE title = "Friends";
を実行してみましょう。
(タイトルFriendsが全て表示される)
でも、もうちょっと厳密にやるべきかもしれません。
大文字と小文字が違う場合に備えて、title LIKE "Friends"と言うべきでしょう。
(大文字小文字関わらず、タイトルFriendsが全て表示される)
多くの人がFriendsが好きでした。
実際のところ、何人くらいでしょうか？
さて、こんなことができることを思い出してください。
COUNTと言えば、SQLにカウントを任せられます。
SELECT COUNT(title) FROM shows WHERE title LIKE "Friends";
COUNT(title)
26
この26人に対して、私は強く反対します。
これを私が問題視しているとします。
さて、
DELETE FROM shows WHERE title LIKE "Friends";
として、全部取り除きましょう。
sqlite> SELECT COUNT(title) FROM shows WHERE title LIKE "Friends";
COUNT(title)
0
(Friendsのタイトルのカウントが0になった)
このように、CRUD(Create Read Update Delete)の基本的なコマンドは、CREATEやINSERTを使っても、SELECTを使っても、UPDATEを文字通りに使っても、DELETEを文字通りに使っても、実行することができます。
以上、簡単に説明しました。
あっという間に終わってしまいましたが、SQLの基本的な操作はこの4つだけで、それに加えてCOUNTなどの付加的な関数やWHEREなどのキーワードがあります。

では、もっと良い方法を提案しましょう。
データを選択したり、テーブルを作成したり、データを挿入する機能があるのであれば、自分でpythonスクリプトを書いて、SQLをループのように使って、CSVファイルを読み込んで、各行を手動で挿入してみましょう。
正直なところ、新しいデータベースに全ての行をインポートするために何百ものSQLクエリを手動で入力するのは、ひどく時間がかかることでしょう。
私はそれを行う代わりにプログラムを書きたいのです。
そして、次のように設計することを提案したいと思います。
今回は2つのテーブルを用意して、この図のように表します。
shows genres
id → show_id
title genre

1つはshowsと呼ばれます。
もう１つはgenresと呼びます。
これはリレーショナルデータベースを設計する際の基本的な原則で、データ間の関係を把握し、データを正規化するためのものです。
データを正規化するということは、冗長性を排除するということです。
データを正規化するということは、同じ言葉が何度も出てこないようにして、いわばデータの真実の情報源を1つにするということです。
では、どうすればいいのでしょうか？
私が提案するのは、showsというシンプルなテーブルを作り、そこには2つの列だけを設けることです。
1つはidと呼ばれるもので、これは新しいものです。
もう１つは以前と同じくtitleと呼ばれるものにします。
正直なところ私はタイムスタンプを気にしないので、その値は捨ててしまいます。これは、自分でプログラムを書く事のもう１つの利点です。
好きなデータを追加したり、削除したりすることができます。
idにはユニークな識別子を導入します。
文字通り単純な整数で、1、2、3…10億～20億まで、お気に入りの番組の数に合わせて自由に設定できます。
このidを自動的に増やしていくことにします。
なぜでしょうか？
すべてのジャンルを別のテーブルに移すことを提案します。
1つ、2つ、3つ、または5つのジャンルを1つの列にカンマで区切って並べるのではなく…それって面倒ですよね？
つまり、ここにカンマがあるか、あそこにカンマがあるか、をチェックするような馬鹿げたコマンドを実行しなければならないのです。
いわば、非常にハック的なもので、悪いデザインです。
そんなことをする代わりに、別のテーブルを作って2つの列を持たせます。
1つはshow_id、もう１つはgenreと呼びます。
ここでのgenreはただのシングルワードになります。
この列にはComedyやMusic、Musicalなどのジャンルを表す単語が入ります。
このshow_idを使って、これらのジャンルをGoogleフォームに投稿されたオリジナルの番組showと関連付けることにします。
これは特に何を意味するのでしょうか？
1、2、3、4、5、6…というユニークな識別子を、最初のテーブルであるshowに追加することで、非常に効率的な方法で、単純な数字を使って同じ番組を参照することができます。
たった１つの正規化された番号で参照することができ、それは4バイト、または32ビットにしかなりません。非常に効率的です。
しかし、その番組を1つのジャンル、2つ、3つ、あるいはそれ以上と関連付けたり、あるいは関連付けなかったりすることもできます。
このようにして、現在のテーブルのすべての行は、新しい２つのテーブルの1つ以上の行になります。
ジャンルを分解しているので、すべての番組に複数の行を追加することができ、それらのジャンルを元の番組自体にリマップすることもできます。
さて、ここで何が話題になっているのでしょうか？
慣れておくべき言葉は何でしょうか？
さて、ここではどのような種類の型があるのかを知る必要があります。
そのために、こうしましょう。
ここにリストを作成しました。
SQLiteでは、5つの主要なデータ型があります。
BLOB
INTEGER
NUMERIC
REAL
TEXT
ちょっと単純化しすぎですが、5つの主要なデータ型があり、そのうちのいくつかは見慣れたものですが、いくつかは目新しいものです。
INTEGERは整数で、32ビットまたは4バイトの値で、1、2、3、4のような正負の値を表します。
REALはfloatと同じく小数点以下の値を持つ浮動小数点数で、おそらくデフォルトでは32ビットです。
しかし、これらの型のサイズはC言語で技術的にそうであったようにシステムによって異なります。
一般的に言えば、これらはあくまで経験則です。
TEXTはまさに文字通りのものです。ある程度の長さの文字列に相当します。
しかし、SQLiteにはこれまで見たことのない2つのデータ型、NUMERICとBLOBがあります。
これらについて、もう少し詳しく説明します。

BLOBとはBinary Large OBjectの略です。
つまり、0と1のデータをデータベースに保存できるということです。

NUMERICは、数字のようだが、それ自体は数字ではないものです。
年や時間のように、数字を含んではいるが、単なる整数ではないものを指します。
また、SQLiteでは自分でSQLコードを実行して手動で列を作成する際に、列をNULLにできないよう指定することができます。
NOT　NULL
今まではこれを無視してきました。
しかし、中には黙秘権を行使して、番組のタイトルやジャンルを教えてくれなかった人もいるかもしれません。
あなたの答えは空欄になっているかもしれません。
ウェブサイトに登録する際に、住んでいる場所や電話番号などの情報を提供したくない人もいるでしょう。
このように、一般的なデータベースでは、NULL値をサポートしたい場合があります。
しかし、NULLではいけないと言いたいこともあるでしょう。
ウェブサイトではメールアドレスやパスワード、その他いくつかのフィールドが必要になるでしょうが、全てではありません。
また、SQLにはUNIQUEというキーワードがありますが、これを使うと、この列に入る値は全て一意でなければならないと追加で指定することができます。
UNIQUE
ウェブサイトでもこれを使うことができます。
同じメールアドレスが何度もウェブサイトに登録できないようにするには、email列が一意になるように指定します。
そうすれば、同じメールアドレスで複数の人を登録することはできません。
要するに、これはSQLツールキットの中のツールの１つに過ぎないのです。
そのうちのいくつかは、これから間接的に使います。

そして、自分でテーブルを設計する前に必要な最後の専門用語はこれです。
SQLには主キーと外部キーという概念があります。
これはスプレッドシートでは見たことがないものです。
現実世界で何年も働いていて、アナリストや財務担当者のように目の前にかなり豪華なスプレッドシートがある人以外は、やはりキーや一意的な識別子を見たことはないでしょう。
しかし、これらは比較的シンプルなものです。
実際、先ほどの図に戻りますが、このような2つのテーブルがあり、一方のテーブルの全ての行を一意に識別するために単純な整数を使用したい場合、それを技術的にはIDと呼びます。
慣習的にそう呼ぶことにします。
何とでも呼べますが、IDは単にユニークな識別子という意味です。
しかし、意味的には、このIDは主(プライマリー)キーと呼ばれるものです。
主(プライマリー)キーとはテーブルの中で、全ての行を一意に識別する列のことです。
つまり、タイトル欄に複数のバージョンのThe Officeを入れることができるということです。
しかし、それらの行はそれぞれ独自の番号を持つことになるでしょう。
つまり、主キーは各行を一意に識別します。
ジャンルのような別のテーブルでは、その一意の識別子を使って元のテーブルを参照することができます。
この文脈では、そのID(genresのshow_id)は外部キーと呼ばれます。
ここではshow_idと読んでいますが、これは多くのSQLデータベースにおける慣習で、技術的にはshowまたはshows(この場合は複数形)と呼ばれるテーブルのIDと呼ばれる列であることを意味しています。
showsに1という数字があり、仮にThe Officeが1というユニークなIDを持っているとすると、このテーブルにはidが1、titleがThe Officeという行があることになります。
The OfficeはComedy、Drama、Romanceなど、複数のカテゴリに属しているかもしれません。
従って、genresテーブルでは、3つの行を出力し、それぞれの行に1、1、1という数字を入れて、それぞれの行にComedy、Drama、Romanceという言葉を入れたいのです。
shows genres
1,The Office 1,Comedy
1,Drama
1,Romance
繰り返しになりますが、ここでの目的はデータベースをより良く設計することであり、1つの列の中にカンマで区切られた値のリストを入れるような馬鹿げたことはしません。
それをそれぞれの行の中に展開したいのです。
複数の列を使えばいいのではないかと思うかもしれません。
しかし、表計算ソフトの原則に従えば、ジャンル、ジャンル、ジャンルのように、データがすべて同じであるときに、列をどんどん増やしていく習慣をつけるべきではありませんよね。
スプレッドシートの世界では、ジャンル1と呼ばれる列に加え、ジャンル2、ジャンル3、ジャンル4と呼ばれる列を用意するのは愚かなやり方です。
これがどれだけ馬鹿げていて非効率的かは想像に難くありません。
ジャンル数が少ない番組では、これらの列の多くが空になってしまいます。
この時点でちょっとした混乱が生じてしまいます。
そこで、リレーショナルデータベースの世界では、2番目のテーブルのようなものを用意し、概念的には外部キーと呼ばれるものを使って、主キーに何らかの形でリンクする複数の行を用意するのが良いでしょう。

さて、それでは早速、このコードを書いてみましょう。
IDEに戻ります。
SQLiteを終了してみましょう。
先ほど作成したshows.dbも削除してしまいます。
以下のことを行う最終バージョンのpythonファイルを実装してみましょう。

まず、showsとgenresの2つのテーブルを作成し、
次にforループでCSVを反復処理し、
showsにデータを挿入し、
genresにもデータを挿入します。

これをプログラムで行うにはどうしたらいいでしょうか？
さて、パズルの最後のピースが必要になります。
pythonとSQLの世界を橋渡しする方法が必要なのです。
ここではライブラリが必要になります。なぜなら、ライブラリ無しで行うのはあまりにも苦痛だからです。
それはCS50です。
CS50は、これから説明するように、これをとてもシンプルにしてくれます。
他にもサードパーティー製の商用、オープンソースのライブラリがありますが、これらも同じように使用できます。
しかし、それらはあまり使いやすくないので、まずCS50ライブラリを使います。python向けにはget_string、get_int、get_floatなどの関数がありますが、今日はSQL機能もサポートしてくれます。

それではfavorite.pyファイルに戻ってみましょう。
そして、CSVだけでなく、CS50のライブラリからSQLと呼ばれる機能をインポートします。
from cs50 import SQL
CS50ライブラリの中にはSQLという変数があります。
これはSQLiteデータベースをメモリにロードします。
では、どうすればいいのでしょうか？
新しいコードを2行ほど追加してみましょう。
shows.dbというファイルを開きますが、今回は書き込みモードにします。
そして、今のところは、すぐに閉じてしまいます。
open("shows.db","w").close()
これは空のファイルを作成するPythonicな方法です。
見た目は少し馬鹿げていますが、show.dbというファイルを書き込みモードで開き、すぐに閉じることで、ファイルを作成し、ファイルを閉じるという効果が得られます。
つまり私は今、対話するための空のファイルを持っているのです。
余談ですが、次のようにすることもできます。
(ターミナルウィンドウに)touch shows.db
ちょっと変わったコマンドですが、ターミナルウィンドウでは、ファイルが存在しない場合にファイルを作成するという意味です。
ですから、代わりにこれを実行することもできます。
しかし、それはpythonとは関係ありません。
さて、ファイルを作成したら、それをSQLiteデータベースとして開いてみましょう。
データベースを表すdbという変数を宣言します。
CS50ライブラリからSQL関数を使います。
そして、やや不可解な文字列ですが、sqlite:///shows.dbを開きます。
db = SQL("sqlite:///shows.db")
さて、これはURL(http://)のように見えますが、代わりにSQLiteになっています。
通常の2つの//スラッシュでなく、3つの///スラッシュがあります。
しかし、このコードの6行目では、まだ何も入っていない空のファイルをCS50ライブラリを使ったSQLiteデータベースとして開いています。
なぜそんなことをしたのでしょうか？
それは最初のテーブルを作りたいからです。
先に進んで、db.executeを実行してみましょう。
CS50ライブラリの中にexecuteという関数があります。先に進んでこれを実行してみます。
db.execute("CREATE TABLE shows (id INTEGER, title TEXT, PRIMARY KEY(id)")
ちょっとわかりにくいですね。
しかし、何が起こっているのか見てみましょう。
8行目ではpythonとSQLを組み合わせているように見えます。
そして、ここからが、プログラミングが本当にパワフルに、派手に、クールに、難しくなるところです。
ある言語を別の言語の中で使うことができるのです。
どうやって？
SQLは単なるテキストコマンドの集まりです。
今まではsqlite3というプログラムで手入力していました。
しかし、同じコマンドをpythonの文字列に格納して、コードを使ってデータベースに渡すことを妨げるものは何もありません。
私が使っているコードはexecuteと呼ばれる関数です。
そして、その目的は、CS50のスタッフが書いたように、pythonコードの引数をデータベースに渡して実行することです。
つまり、数分前にSQLiteのプロンプトに手動で入力したものを、プログラムで実行するようなものです。
これでshowsというテーブルが作成され、そこにユニークなIDとタイトルが格納されます。
そして、次のようにします。
db.execute("CREATE TABLE genres (show_id INTEGER, genre TEXT, FOREIGN KEY(show_id) REFERENCES shows(id)")
さて、これは長いですね。
左から右へ要約してみましょう。
db.executeは私のpython関数で、好きなSQLを実行します。
CREATE TABLE genresは、genresというテーブルを作成します。
そのテーブルの列はshow_idと呼ばれるもので、これは整数です。genreはテキストフィールドになります。
しかし、複数のジャンルではなく、一度に１つのジャンルになります。
そして、ここでは外部(FOREIGN)キーとしてshow_idを指定していますが、これはたまたまshowsテーブルのid列を参照しています。
少しわかりにくいですが、これはこの図と同等のことを実現しているのです。
点滅しているプロンプトに、この2つのSQLコマンドを手動で入力することもできました。
しかし、そうではなく、pythonでテーブルを作成し、更にデータをデータベースにロードするプログラムを書きたいのです。
それでは今からその作業に入ってみましょう。
すべてをインポートしたいので、タイトルを選ぶつもりはありません。
カウントなども行いません。
▼favorite_SQL.py
import csv

from cs50 import SQL

open("shows.db","w")
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)

先ほどと同じようにループの中に入ってみましょう。
そして今回は、readerの行に対して、いつものように現在のタイトルを取得してみましょう。
いつものように正規化するために空白を取り除き、大文字にしてみましょう。
for row in reader:
title = row["title"].strip().upper()
そして、
db.execute("INSERT INTO shows(title) VALUES(?)"...)
とします。
私たちが使っているようなSQLライブラリでは、プレースホルダとして(クエスチョンマーク?を使った)最後の形の構文をサポートしています。
Cでは%sを使います。
Pythonでは、中括弧{}を使って、単語をそのまま入れます。
SQLでは、同じ問題に対する第3のアプローチがあります。構文的には違いますが、概念的には同じです。
プレースホルダを置きたい場所にクエスチョンマーク?を置くのです。
そして、この文字列の外側に、クエスチョンマーク?に挿入したい値を実際に入力していきます。
db.execute("INSERT INTO shows(title) VALUES(?)",title)
これはweek1のprintfとよく似ていますね。
しかし、%sの代わりにクエスチョンマーク?、そしてプレースホルダに挿入したい引数をカンマで区切ったリストにしています。
さて、この16行目のコードは、これらの値をすべてデータベースに挿入しました。
それでは早速実行してみましょう。

▼favorite_SQL.py
import csv

from cs50 import SQL

open("shows.db","w")
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()

    db.execute("INSERT INTO shows(title) VALUES(?)",title)

それではpython favorite_SQL.pyを実行してみましょう。
(しばらく何も起こらない)
時間がかかっているようです。
それは、かなりのサイズのファイルがあるからです。
あるいは、私が失敗したのかもしれません。
これは時間がかかりすぎです。。。
ああ、そうか。もう少し我慢すれば良かった。
いいでしょう。
私の接続の速度が少し遅かったようです。
思ったとおり、全てが100%正しく、問題なく動作していました。
では、実際に何をしたのか見てみましょう。
lsを入力すると、shows.dbというファイルがあることに気づきます。
これは今回pythonプログラムが作成したもので、全く新しいものです。
sqlite3 show.dbを実行して、中身を確認してみましょう。
~/ $ sqlite3 shows.db
SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.

どのようなテーブルが存在するかを確認するために、.schemaコマンドを実行します。
sqlite> .schema
CREATE TABLE shows (id INTEGER, title TEXT, PRIMARY KEY(id));
CREATE TABLE genres (show_id INTEGER, genre TEXT, FOREIGN KEY(show_id) REFERENCES shows(id));

確かに、pythonのコードで作成した2つのテーブルは存在しているようです。
SELECT * FROM shows;を実行すると、すべてのデータを見ることができます。
sqlite> SELECT * FROM shows;
id | title
1 | THE OFFICE
2 | THE OFFICE
3 | THE OFFICE
4 | THE OFFICE
…
(すべてのタイトルが表示される)
ほら。プログラムで作成されたテーブルがあります。
このテーブルにはタイムスタンプもジャンルもありません。
しかし、左にはIDがあり、右にはタイトルがあります。
そして驚くべきことに、すべてのIDは1から57まで単調に増加しています。
なぜでしょうか？
SQLデータベースの機能の1つとして、SQLiteで、ある列を主キーとして定義すると、自動的にインクリメントしていきます。
私のコードのどこにも、1、2、3…と整数を入力する行がなかったことを思い出してください。
絶対にそうすることはできたはずです。
counter = 1といった具合です。
そして、ここでid、titleと2つのプレースホルダを指定して、毎回カウンタを渡すことができました。
これを自分で実装して、反復するたびにcounter += 1とすることもできたのです。
しかし、SQLデータベースには、これまで見てきたように、より多くの機能が組み込まれています。
IDを主キーとして宣言しておけば、SQLiteが代わりに挿入し、インクリメントもしてくれるので、上記のようなことをする必要はありません。

さて。
SQLiteに戻ると、IDとタイトルがあることに気づきます。
しかし、SELECT * FROM genresを実行すると、もちろんまだ何もありません。
(何も表示されない)
では、どうやってそれぞれの番組のすべてのジャンルを取り込むのでしょうか？
私のスクリプトを完成させる必要があります。

この同じループの中で、現在の行はタイトルだけでなく、ジャンルも持っています。
with open("Favorite TV Shows - Form Responses 1.csv","r") as file:
reader = csv.DictReader(file)
for row in reader:
しかし、ジャンルはカンマで区切られています。
CSVでは、各タイトルの横に、カンマで区切られたジャンルのリストがあったことを思い出してください。
各ジャンルを個別に見るにはどうすればいいのでしょうか？
for genre in row["genres"]:と言えば良いはずです。
しかし、これでは上手くいきません。
なぜなら、カンマで区切られないからです。
先週見たように、これは文字通り、文字列のすべての文字について繰り返し処理することになります。
しかし、pythonの文字列にはsplit関数があり、カンマとスペースで分割できます。
for genre in row["genres"].split(", "):
この関数がpythonで何をしてくれるかというと、カンマで区切られたジャンルのリストを、いわば展開して、カンマとスペースごとに分割し、ジャンルごとにpythonのリストにしてくれるのです。
これで、個々のジャンルのリストに関して繰り返し処理をすることができます。
そしてここで以下のコードを実行します。
db.execute("INSERT INTO genres (show_id,genre) VALUES(?,?)",...)
しかし、ここで問題があります。
確かに現在のジャンル、つまりgenreを入れることができます。
db.execute("INSERT INTO genres (show_id,genre) VALUES(?,?)",,genre)
しかし、まだここ()に何かを入れる必要があります。
最初のクエスチョンマーク?には、show_idの値が必要です。
現在のテレビ番組のIDを知るにはどうすればいいでしょうか？
これについてはライブラリが助けてくれることがわかっています。
主キーを持つテーブルに新しい行を挿入すると、ほとんどのライブラリが何らかの方法でその値を返してくれます。
15行目に戻って、INSERTを使った後のdb.executeの戻り値を変数に保存してみると、ライブラリは、このshowで使われたばかりの整数IDが何であったかを教えてくれます。
id = db.execute("INSERT INTO shows(title) VALUES(?)",title)
多分1、2、3だと思います。
プログラマーの私が知る必要はありませんし、気にする必要もありません。
しかし、戻り値は変数に格納することができます。
IDが1のThe Officeをshowsに入力し、そのジャンルがComedy、Drama、Romanceだとすると、このforループの中で、つまり入れ子になったforループの中で、1の後にComedy、1の後にDrama、1の後にRomanceを挿入し、genresテーブルに3行を一度に入れることができます。
db.execute("INSERT INTO genres (show_id,genre) VALUES(?,?)",id,genre)
(___はidになった)
それではターミナルウィンドウに戻ってみましょう。
新しいスタートを切るために、古いshows.dbはrmで削除しましょう。
プログラムを再実行してみます。
(しばらく時間がかかる)
今回は我慢強く待ってみましょう。
実際、より多くの作業が行われています。
この時点では、私のプログラムは恐らくCSVのすべての行について繰り返し処理していると思われます。
そして、showsに1つずつ番組を挿入し、genresに1つまたは複数のジャンルを挿入しています。
ちょっと遅いですね。。
もっと速いシステムで使っていたり、自分のMacでやっていたら、もっと早く終わるのかもしれません。
しかし、そもそもなぜ私が.importコマンドを使っているのか、その一例をご覧ください。
これでこのプロセスの一部が自動化されました。
残念ながら、データのフォーマットを変更することはできませんでした。
しかし、ここで重要なのは、何百行ものデータを一度に挿入するのに少し時間がかかっても、これは一度やればそれで済むということです。
先ほど、このパフォーマンスについて質問がありました。
SQLデータベースを完全にコントロールできるようになったことで、実際にパフォーマンスを向上させることができるようになったのです。
OK。予想通り、時間内に終了しました。
それではsqlite3 show.dbを実行してみましょう。
これで生のSQL環境に戻りました。
先ほど行ったSELECT * FROM showsを実行すると、先ほどと同じようにすべての内容が表示されます。
SELECT * FROM shows WHERE title = "THE OFFICE";と入力すると、これらのユニークなIDを見ることができます。
(THE OFFICEのタイトルが入ったすべての行が表示される)
重複をわざわざ排除する必要はありませんでした。
すべてをそのままにして、すべてに固有IDを与えました。
そして、SELECT * FROM genres;を実行すると、すべての値が表示されます。
(すべてのジャンルが表示される)
ここでは1行に1つのジャンルしかありません。
そして、最終的にそれらをタイトルと並べることができます。
何かが間違っています。。。
これを直したい。
それでは2回目の最後の5分間の休憩に入りましょう。
戻ってきたら何が起きていたのかを説明しますw

戻ってきました。。
解散する直前、自信を失いかけていました。
しかし、嬉しいことに、実際には全て上手くいっていました。
ただ、その正しさを疑ってしまったのです。
SELECT * FROM showsを実行すると、確かに2つの列が戻ってきます。
1つはユニークなID、いわゆる主キーで、続いて各番組のタイトルが表示されます。
また、同じように* FROM genresと検索すると、一度に1つのジャンルが得られます。
しかし、左側にあるのは主キーではなく、ここでは外部キーと呼ばれる同じ数字で、一方を他方にマッピングしています。
例えば512という番組には5つの異なるジャンルが関連付けられています。
実際、番組を遡ってみると、ゲーム・オブ・スローンズは、スリラー、歴史、アドベンチャー、アクション、戦争の5つのジャンルに属すると皆さんは決めたようですね。
さて、これがリレーショナルデータベースの意味です。
複数のテーブルにまたがって、あるテーブルと他のテーブルのデータを結びつける関係があります。
問題は、質問に答えるのが少し難しくなるのではないかということです。
なぜなら、2つのテーブルに問合せをしたり、2つの別々のクエリを実行したりして、データを結合しなければならないように思われるからです。
しかし、実際にはそうではありません。
例えば、「あなたの好きなテレビ番組のうち、ミュージカルであるものをすべて挙げてください。」という質問に答えたいとします。
番組だけを選択することはできません。なぜならそこにはもうジャンルが無いからです。
しかし、genresテーブルにはタイトルが無いので、ジャンルだけを選択することもできません。
しかし、外部キーと主キーの関係には、一方と他方をつなぐ値があります。
では、私が頭の中で何ができるかというと…genresから、特定のジャンルがMusicalであるすべてのshow_idを選択することができると思います。
SELECT show_id FROM genres WHERE genre = "Musical";
カンマやスペースを気にする必要はありません。
なぜなら、コードを使ってプログラム的に設計したこの新しいバージョンでは、ミュージカルを始めとするすべてのジャンルがただ１つの単語だからです。
Enterを押すと…
(show_idが表示される)
これらのshow_idは、すべて皆さんによってMusicalに属するとされたものです。
しかし、これでは面白くありませんし、これらのIDを一つ一つ調べるために手動で10個ほどのクエリを実行したくないのは確かです。
しかし、SQLではこんなこともできるのです。
クエリを入れ子(ネスト)にすることができるのです。
先ほどのクエリ全体を()の中に入れて、その前に次のようなものを付け加えてみましょう。
SELECT title FROM shows WHERE id IN (SELECT show_id FROM genres WHERE genre = "Musical";)
このように、pythonやC言語でforループに入れ子にするのと同じような感覚で、クエリを入れ子にすることができます。
この場合、小学校の算数のように、()括弧の中にあるものが最初に実行されます。
その後、内側のクエリの結果を使って外側のクエリが実行されます。
つまり、IDがidのリストにあるshowからタイトルを選択すると、出来上がりです。

sqlite> SELECT title FROM shows WHERE id IN (SELECT show_id FROM genres WHERE genre = "Drama");
title
FRIENDS
FRIENDS
FRIENDS
THE OFFICE
SHERLOCKS; FRIENDS
THE OFFICE

このタイトルを入力した人はこのジャンルのボックスにチェックを入れたということです。
つまり、共通点を考慮してデータベースを正規化したり、データを整理したりして、より良い設計をしたとしても、確かにまだ冗長性があります。
しかい、少なくとも今は、すべての列に1つの値だけが入っていて、わざわざカンマで区切ったリストではない、きれいなデータになっています。
例えば、皆さんがThe Officeをどのようなジャンルだと思っているのかを知りたいとします。
そこで、ちょっと逆の質問をしてみましょう。
どうすればいいでしょうか？
さて、The Officeを調べるには、まずshowからidをSELECTしてWHERE title = "THE OFFICE"とする必要があります。
SELECT id FROM shows WHERE title = "THE OFFICE";
それぞれの回答には、追跡できるように固有の識別子idをつけました。
そして、これらすべての数字があります。
さて、これは何十もの回答です。
確かに、そんなにたくさんのクエリを実行したくはありません。
しかし、サブクエリを使えば、再び助けになると思います。
この全体を()括弧で囲んでみましょう。
(SELECT id FROM shows WHERE title = "THE OFFICE");

sqlite> SELECT DISTINCT(genre) FROM genres WHERE show_id IN (SELECT id FROM shows WHERE title = "THE OFFICE") ORDER BY genre;
genre
Animation
Comedy
Documentary
Drama
Family
Horror
Reality-TV
Romance
Sci-Fi

ちょっと面白いことに、The Officeを入力した人は多様なジャンルのボックスにチェックを入れたようです。
これにもいくつか異論はあります。
しかし、ユーザーの意見を受け入れるということはこういうことなのです。
このSQL言語では、今日紹介したような新機能がたくさんあるにも関わらず、かなり簡潔に表現することができます。
そうでなければ、pythonのコードで何十行も実装しなければならず、C言語でこのようなものを実装するとなると、何行、何時間かかるかわかりません。
しかし、この設計をさらに改善することができます。
この表や図は、今の私たちの状態を表しています。
このgenresテーブルには多くの冗長性があることに気づくでしょう。

shows genres
id → show_id
title genre

コメディにチェックを入れると、コメディ、コメディ、コメディという行ができてしまいます。
show_idは異なりますが、comedyという単語が何度も出てきます。
これは、リレーショナルデータベースの世界では嫌われる傾向にあります。
なぜなら、コメディというジャンルやミュージカルというジャンルなどがある場合、理想的にはそれらを一箇所にまとめておくべきだからです。
このような冗長性を排除することを学術用語で正規化する(normalize)と言いますが、本当にこだわりたいのであれば、次のようにします。

shows shows_genres
id ←←← show_id genres
title genre_id ←←← id
name

showsテーブルにはidとtitleがあり、そこは前と同じです。
しかし、genresテーブルにはidとnameの2つの列があります。
さて、これはgenresの独自のidで、show_idとは関係ありません。
これはgenres独自のユニークな識別子であり、ここでは主キーとなっています。
つまり、genresテーブルにはコメディ、ドラマ、音楽、ミュージカル、その他すべてのジャンル名が1行ずつ入っています。
そして、3つ目のテーブルを使います。
これは俗に結合テーブルと呼ばれていますが、ここでは真ん中に描いています。
名前は自由ですが、ここではshows_genresとし、このテーブルがこれら2つのテーブル間の関係を実装していることを明確にしています。
このテーブルには何のデータも無いことに注意してください。
show_idとgenre_idという外部キーがあるだけです。
この3つ目のテーブルがあることで、コメディという単語はどこか1つの行にしか現れないようにすることができます。
ミュージカルという単語もどこか1つの行にしか出てきません。
しかし、show_idとgenre_idという、より効率的な整数を使い、それぞれ主キーと主テーブルを指し示すことで、2つのテーブルを結びつけています。
これは、データベースの世界で「多対多(many to many)の関係」と呼ばれる例です。
1つの番組には多くのジャンルがあります。
1つのジャンルが多くの番組に属することもあります。
このように3つ目のテーブルを持つことで、多対多の関係を持つことができます。
また、3つ目のテーブルでは重複するコメディ、コメディ、コメディ…のすべてを取り除くことで、データセットを真に正規化することができます。
なぜこれが重要なのでしょうか？
ジャンルの場合には大したことではないかもしれません。
しかし、現在の設計で、もし私がスペルミスをして、コメディという名前を間違えたとしたらどうでしょう。
そうするとcomedyという単語を含むすべての行を何度も変更しなければなりません。
また、番組のジャンルを変更した場合も、複数の場所で変更しなければなりません。
しかし、3つのテーブルを使ったこのアプローチでは、ジャンルの名前を変更するのは1か所だけで良く、あちこちで変更する必要はないと主張できます。
一般的に、C言語やpython、SQLでは、同一の値をあちこちにコピーペーストしないことが良いとされています。

いいでしょう。
そうは言っても、私たちが自由に使えるツールは他にあるでしょうか？
現実の世界でSQLを使っていると、BLOB、INTEGER、NUMERIC、REAL、TEXTという5つのデータ型以外にも、様々なデータ型が存在することがわかります。
BLOBはバイナリデータで、一般的には特殊な用途以外では使用しません。
INTEGERは通常32ビットの整数、NUMERICは日付や年、時間などの数値、REALは浮動小数点数、TEXTは文字列です。
しかし、携帯電話やMac、PC上のSQLiteを卒業して、実際にインターネットビジネスを展開する場合、Oracle、MySQL、PostgreSQLを実行する実際のサーバに移行すると、より洗練された、より強力なデータベースには、他のサブタイプがあることがわかります。
つまり、INTEGERの他に、32ビットではなく数ビットを使用するsmallintや、32ビットではなく64ビットを使用するbigintを指定することができるのです。
FacebookやTwitterは膨大なデータを持っているので、bigintを多用する必要があります。
一方、私たちは単純な整数で済ませることができます。なぜなら、1つのクラスに40億以上のお気に入りのテレビ番組があるわけではないからです。
REALのように32ビットの実数を使うこともできますし、より精度が必要な場合には、倍精度(double precision)というものもあります。
NUMERICは包括的な型で、日付や時間だけでなく、ブール値のようなものも扱えます。
boolean date datetime numeric(scale,precision),time,timestamp
この型の位取りと精度を使って、格納する総桁数を指定できます。
つまり、単なる整数ではない数字に関連しています。
TEXTの中には、文字の後に1つの数が続くカテゴリもあります。
char(n) varchar(n) text
これは、列内のすべての値が同じ文字数になるように指定するもので、米国の州コードのように事前に長さが決まっている場合に役立ちます。
米国では、マサチューセッツ州にMA、カリフォルニア州にCAなど、すべての州に2文字のコードが割り当てられています。
char(2)は、列のすべての値が2文字になることがわかっているので、このような場合に役立ちます。
しかし、それがわからない場合はvarchar(n)を使用することができます。
varchar(n)は最大文字数を指定します。
例えば、32文字のvarcharを指定することができます。
この場合、32文字よりも長い名前は入力できないため、もっと大きな文字を許容したい場合は、例えばvarchar(200)を指定します。
しかし、これは私たちが実際にウェブで経験することと密接な関係があります。
ウェブサイトにアクセスしてフォームに入力し始めたら、突然、それ以上文字を入力できなくなったことはありませんか？
それはなぜでしょうか？
ひとつには、プログラマが、あなたがより詳細に自分を表現し続けることを望まないからでしょう。それがカスタマーサービスサイトの苦情受付フォームであれば尚更です。
しかし、現実的にはデータベースが限られた文字数しか保存できないように設計されているからでしょう。
そして、あなたはその限界に達したのです。
C言語のようなバッファオーバーフローを未然に防ぐため、データベースは最大値nを強制的に設定します。
TEXTは、さらに大きなテキストの塊を扱うこともできます。
履歴書をコピーペーストさせたり、文書を保持させたり、もっと大きなテキストの集合を扱う場合は、TEXTを使うことになるでしょう。
では、実際のデータセットを考えてみましょう。
非常に興味深いことに、これらの非常に学術的なアイデアや推奨事項は、何百ものお気に入りではなく、何千ものお気に入りがある場合に、より効果を発揮します。
これから行うのは、IMDb(Internet Movie Database)のSQLite版ファイルのダウンロードです。
皆さんの中には、映画やその評価を調べるために、ウェブサイトのフォームの中で利用した人もいるかもしれません。
事前にすべての情報をTSVファイルとしてダウンロードするスクリプトを作成しました。
IMDbはすべてのデータをTSV(Tab-Separated Values)として提供しています。
そして、次のようにshows.dbというファイルにインポートしました。
これからshows.dbを開きますが、これは先ほど皆さんのお気に入りをもとに作成したバージョンではありません。
これは、私たちスタッフが事前にIMDb.comから何十万もの映画やテレビ番組、俳優や監督をライセンスに基づいてダウンロードし、SQLiteデータベースにインポートして作成したバージョンです。
ここに何が入っているか確認するにはどうすればいいでしょうか？
では、.schemaと入力してみましょう。
すると、そこにはたくさんのデータが入っているのがわかります。
people shows genres ratings
id id show_id show_id
name title genre rating
birth year votes
episodes

stars writers
show_id show_id
person_id person_id

peopleテーブルには、各人のid、名前、誕生年が入っています。
showsテーブルには、これまで話してきたようにid、番組のタイトル、さらに番組のデビュー年とエピソード数が記載されています。
そして、先ほどと同じようにgenresテーブルがあります。
ここでは、3つ目の表にすべてを反映させることはしませんでした。
そのため、ジャンルに重複があります。
次にratingテーブルがあります。
ここで、リレーショナルデータベースの面白さがわかると思います。
1～5のような評価を格納する評価テーブルを持ち、show_idによってその評価を番組に関連付けることができます。
そして、その番組の得票数を記録することができます。
writersは別のテーブルになっていることに注意してください。。
これはちょっとクールです。
このテーブルは、矢印の通り、showsテーブルとpeopleテーブルに関連しており、結合テーブルになっています。
show_idの外部キーとperson_idの外部キーが、それぞれshowsテーブルとpeopleテーブルを参照しているので、1人の人間が複数の番組のwriterになることも、1つの番組が複数のwriterを持つこともできる、多対多の関係になっています。
そして最後に、番組の出演者であるstarsです。
これも結合テーブルであることに注目してください。
外部キーはshow_idとperson_idの2つで、それぞれこれらのテーブルを参照しています。
ここで、リレーショナルデータベースの意味がよくわかります。
スティーブ・カレル、スティーブ・カレル、スティーブ・カレル…のように、これらのショーのすべての監督、脚本家、スターの名前を別々のテーブルに重複して入れていたら、かなり馬鹿げた悪いデザインになってしまいますよね。
俳優、監督、脚本家、その他のあらゆる役割を担う人たちは、結局のところ、ただの人間です。
リレーショナルデータベースでは、これらの人々をすべてpeopleテーブルに入れ、主キーと外部キーを使って、他の種類のテーブルを参照したり、関連付けたりすることができます。
しかし、これを行うと、データが多い場合には処理に時間がかかることがわかります。
例えば、こんなことをしてみましょう。
SELECT * FROM shows;とします。
(一気に大量のデータが表示される)
私のMacはかなり高速です。IDEからMacに切り替えたのは、時間を節約するためです。
クラウドではなく、ローカルで作業をした方が速いからです。
それでは、このIMDbデータベースにある番組の数をCOUNTで数えてみます。
SELECT COUNT() FROM shows;
153331
153,331本のテレビ番組！これは多いですね。
peopleテーブルから人の数を数えてみるとどうでしょう。
SELECT COUNT() FROM people;
457886
457,886人もの人が、主演や脚本家、その他の役割を担っているようです。
これはかなりの規模のデータセットですね。
では、先に進んで簡単なことをやってみましょう。
SELECT * FROM shows WHERE title = "The Office";を実行してみましょう。
今回は大文字小文字や空白を気にする必要はありません。
これはIMDb、権威あるソースから得たきれいなデータだからです。
(結果が表示される)
The Officeには実際に異なるバージョンがあることに注目してください。
イギリス版とアメリカ版があるのをご存知でしょう。
それらとは関係のない番組もあります。
しかし、それぞれの番組は、ここにある年号によって区別されています。
さて、これはちょっと多いですね。
では、もう一度やってみましょう。
このプログラムのタイマーをオンにして、このクエリの時間を計ってみましょう。
sqlite> .timer ON
そして、もう一度実行してみましょう。
SELECT * FROM shows WHERE title = "The Office";
(結果と実行時間が表示される)
この検索にかかった実時間(real)は0.012秒のようです。
これはかなり速いですね。あまりの速さにほとんど気がつきませんでした。
それでは次のことをしてみましょう。
showsというテーブルのタイトル列にtitle_indexというインデックスを作成してみましょう。
CREATE INDEX title_index ON shows(title);
さて、私は何をしているのでしょうか？
さて、先ほどのパフォーマンスに関する質問に最後に答えますが、デフォルトでは、今までやっていたことは確かにO(n)です。
上から下まで線形探索しているだけなので、CSVと同じようにやっていただけでは、SQLの目的が疑われます。
しかし、インデックスは対数時間が得られるような方法で、より効率的にデータを読み込むためのデータベースへの手がかりです。
インデックスとは、SQLiteデータベース、Oracleデータベース、MySQLデータベースなど、使用している製品に関わらず、メモリ上に構築される仮想的なデータ構造のことです。
そして、このような構文を使って、一般的にB-treesと呼ばれるものをメモリ上に構築します。
CREATE INDE name ON table(column, ...);
このクラスでは、ツリーについて少し話しました。
二分探索木や家系図のようなものについて話しました。
B-treesは基本的に幅が広くて、高さがそれほど無い家系図のようなものです。
C言語で見たデータ構造に似ていますが、葉っぱのノード、つまり子供や孫、ひ孫のノードをできるだけルートに近づけようとします。
そのために使用するアルゴリズムは、システムに応じて、プロプライエタリあるいは文書ベースのものになる傾向があります。
しかし、このシステムでは、物事をリストにして保存することはありません。
私たちが見ているテーブルのように、上から下へと保存されることもありません。
非常に背の高い構造に見えるテーブルは、実は覆いの下では、ツリーと呼ばれるファンシーなもので実装されています。
このようにインデックスと呼ばれるものをツリーで作成した場合、インデックスを作成するのに、この場合の0.098秒のように、若干の時間がかかります。
(実行すると実行時間が表示される)
しかし、これによって何が起こるかを考えてみましょう。
先ほど線形探索を使ってThe Officeを検索したときには、0.012秒かかりました。
インデックスを作成し、SQLiteに「メモリ上のこの素晴らしいツリーを構築してくれ」と言った後に、もう一度同じクエリをじっこうすると…
(結果と実行時間が表示される)
ほら、0.001秒です。桁違いの速さです。
確かに、私たち人間にとってはどちらも同じように高速です。
しかし、データセットがさらに大きくなり、クエリもさらに大きくなったとしましょう。
これらのインデックスは、それ以上に大きくなる可能性があります。
クエリの実行はそれ以上に時間がかかる可能性があるので、全体ではそれらの合計以上の時間がかかることになります。
しかし、この図のようにすべてのデータがあちこちに散らばっているとすると、どうすれば有益な仕事ができるのでしょうか？
映画の出演者、脚本家、スター、評価などがあちこちに散らばっていたら、どうやって元に戻せばいいのでしょうか？
私はこのような混乱の中で、これらのクエリをすべて実行する必要があると思われます。
しかし、実際にはそんな複雑なことをしなくてもいいのです。
SQLにはJOINというキーワードがあります。これがここで見る最後のキーワードです。
JOINキーワードは、暗黙的にも明示的にも使用することができ、テーブルを結合して、より大きく、より使いやすいテーブルを再構成することができます。
例えば、The Officeだけでなく、スティーブ・カレルのテレビ番組をすべて取得したいとします。
ここで、以下のようにすることによって、スティーブのidを選択できることを思い出してください。
SELECT id FROM people WHERE name = "Steve Carell";
このテーブルではIMDbから取得しているため、彼のidは異なります。
(136797と表示される)
これが彼のidです。
それでは、タイマーを止めてみましょう。
sqlite>.timer OFF
これが彼のid、136797です。
これをコピーしてコードに貼り付けることもできますが、クエリを入れ子にすれば、その必要はありません。
starsテーブルからperson_idがこの結果と同じであるshow_idをすべて選択してみましょう。
SELECT show_id FROM stars WHERE person_id = (SELECT id FROM people WHERE name = "Steve Carell");
starsという結合テーブルがあり、peopleとshowsをリンクしています。
それでは実行してみましょう。
(複数のshow_idが表示される)
これでスティーブ・カレルの出演するテレビ番組のshow_idがすべて揃いました。
たくさんありますね。そして、それらが何であるかは非常に不明確です。
そこで、これらをすべて()括弧に入れて、以下のように別の入れ子にしたクエリを実行してみましょう。
SELECT title FROM shows WHERE id IN (...直前のクエリ);
すると、彼が出演したすべての番組のタイトルが表示されます。
当時のThe Dana Carvey Showや、The Officeが一番上に表示され、最近ではApple TVのThe Morning Showなどが表示されます。
なるほど、このようにデータを再構成できるのは素晴らしいことですね。
しかし、これには様々な方法があることがわかっています。
これからの数週間、psetやlaboなどでもっと多くのことがわかるでしょう。
しかし、他にも色々なことができます。
一見すると少しわかりにくいですが、この構文をお見せしましょう。
このJOINキーワードは、次のように使えます。
people.id列とstars.person_id列が同じになるように、peopleテーブルとstarsテーブルを結合した上で、タイトルを選択することができます。
sqlite> SELECT title FROM people
...> JOIN stars ON people.id = stars.person_id
...> JOIN shows ON stars.show_id = shows.id
...> WHERE name = "Steve Carell";
つまり、このようにpeopleとstarsを結合した結果、peopleのid列とstarsのperson_id列に基づいてタイトルを選択することができるのです。
更に、stars.show_idとshows.idを一致させることで、showsテーブルに結合することができます。
このように、名前がSteve Carellである2つのテーブルの主キーと外部キーを結合しています。
これは今まで見てきた中でも最も不可解な構文です。
これは、このテーブルをこのテーブルと結合し、さらにこのテーブルと結合して、結合されたすべての行をSteve Carellという名前でフィルタリングする、ということです。
(実行すると、スティーブ・カレルが出演するすべての番組のタイトルが表示される)
ほら。これですべての答えが揃いました。
他にも色々な方法があります。
そのための構文の一部は、今は伏せておきます。
しかし、これでは少し時間がかかってしまいます。
実際、先ほどのタイマーを再起動してみましょう。
この最後のクエリを再実行してみましょう。
(実行時間が表示される)
0.5秒以上かかりました。
確かにちょっと遅かったですね。
しかし、繰り返しになりますが、この場合、インデックスが助けになります。線形探索に支配されないようにすればいいのです。
では、いくつかのインデックスを作成してみましょう。
starsテーブルのperson_id列にperson_indexというインデックスを作成します。
CREATE INDEX person_index ON stars (person_id);
先ほどの私のクエリはperson_id列を使用していました。
それでフィルタリングしていました。
それがボトルネックになっているかもしれません。
そして、show_indexという別のインデックスをstarsテーブルのshow_idに作成してみます。
CREATE INDEX show_index ON stars (show_id);
これも、上から下への線形的なボトルネックになっていたかもしれません。
そして最後に、name_indexという名前のインデックスを作成します。
これは恐らく最もわかりやすいもので、先ほどのshow titleと同様に、peopleテーブルのname列に作成します。
CREATE INDEX name_index ON people (name);
それぞれの実行時間を合計すると、ほぼ1秒かかりました。
しかし、これらのインデックスの作成にかかる時間は一度で済みます。
時間の経過とともに自動的にメンテナンスされます。
しかし、すべてのクエリでこれが発生するわけではありません。
では、もう一度SELECTコマンドを実行してみましょう。
sqlite> SELECT title FROM people
...> JOIN stars ON people.id = stars.person_id
...> JOIN shows ON stars.show_id = shows.id
...> WHERE name = "Steve Carell";
(結果と実行時間が表示される)
ほら。0.001秒になりました。
少し前にかかった0.5秒と比べて、桁違いの速さです。
ここでもリレーショナルデータベースの威力が発揮されていますね。
私たちは時間をかけていくとかの問題を作ってきましたが、最終的には、より洗練された機能や新しい構文を使って解決してきました。
リレーショナルデータベースは、データを正規化し、分解することによって冗長性を取り除き、効率的に保存することができるため、現実世界ではTwitter、Instagram、Facebook、Googleなどに使われています。
また、ツリー構造のおかげで、表計算ソフトに見られるような関係性を、対数時間に近い形で検索することができます。
しかし、問題もあります。
私たちが今日やりたかったことは、SQLで発生する2つの大きな問題を紹介することです。なぜなら、これらは残念ながら非常によく行われているからです。
注目してください。
一般的にSQL injection attacks(SQLインジェクション攻撃)として知られているものがありますが、これはユーザーによる入力を伴うあらゆるアプリケーションに脆弱性があります。
私のfavorites.pyファイルでは、CSVからの入力を取るだけなので問題ありませんでした。
しかし、もし皆さんの中の一人が悪意を持って、番組のタイトルにdeleteやupdateなどの単語を入力していたとしたら、そして私がクエリを実行する際に誤って自分のpythonコードにそれを差し込んでしまったとしたら？
私自身のコードにSQLを注入できる可能性があるのです。
どのようにして？
イェール大学のサイトにログインすると、こんな感じのフォームが出てきます。
ハーバード大学のサイトにログインするとこんな画面になります。
(IDとパスワードの入力を求めるフォーム)
ハーバードもイェールも、このような攻撃への対策は当然行っていると思います。
このログインフォームに、私のメールアドレスとして「malan@harvard.edu'--」を入力したとします。
SQLでは、「--」は何かをコメントしたいときの記号です。
'一重引用符は、Steve Carellやmalan@harvard.eduのように、何かを検索したいときに使われます。
二重引用符でも良いし、一重引用符でも構いません。
今回は、一重引用符を使用しています。
それでは、pythonのサンプルコードを見てみましょう。
ここに、ハーバード大学やイェール大学などの認証システムのバックエンドに存在すると思われるコードがあります。
誰かがpythonでこんなコードを書いたのかもしれません。

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

if len(rows) == 1:
#log user in

ユーザーがさっきウェブフォームに入力した内容が、この?クエスチョンマークのところに入力されます。
これは良いことです。
SQLの疑問符を使っているのですから、これは良いコードです。
ですから、私たちが今日説明したことを文字通り実行し、?クエスチョンマークのプレースホルダを使用すれば、SQLインジェクション攻撃からは安全です。
残念なことに、世の中にはこのことを実践しなかったり、気づかなかったり、忘れてしまったりする開発者がたくさんいます。
もし皆さんがpythonのようなアプローチに頼っていて、代わりにf-stringを使っているとしたら、それは先週からの皆さんの直感かもしれませんが、{}中括弧などを使うことができて素晴らしく便利だからでしょう。
rows = db.execute(f"SELECT * FROM users WHERE username = '{username}' AND password = '{password}'")
もし皆さんが、?クエスチョンマークのプレースホルダではなく、{}中括弧の間に文字通りユーザー名とパスワードを入力したとしましょう。
私が、自分のユーザー名malan@harvard.eduの代わりに、悪意を持ってmalan@harvard.edu'--と入力したらどうなるでしょうか？
これは、このpythonコードを騙して、本質的にこのようなことをさせる効果があります。
検索と置換をしてみましょう。
rows = db.execute(f"SELECT * FROM users WHERE username = 'malan@harvard.edu'--' AND password = '{password}'")
これはpythonを騙して、username = 'malan@harvard.edu'--' とその他のものを実行させます。
繰り返しになりますが、残念なことに「--」はコメントを意味します。
つまり、このSQLクエリのパスワード部分全体を無視するようにサーバを騙すことができるかもしれません。
SQLクエリの目的が、「このユーザー名とパスワードは有効か」をチェックしてユーザーをログインさせるか、「あなたは許可されていません」と言うかを決めることだとしたら、パスワードに関するすべてを本質的にコメントアウトすることで、私が何をしたかわかるでしょう。
私は今、理論的にはパスワードを知らずに、また入力せずにmalan@harvard.eduとしてログインしました。SQL構文と--を利用して、パスワードチェックを無視するようにしました。
db.executeは、INSERTを実行すると、新しく挿入された行のIDを返します。
db.executeを使ってデータベースのテーブルから行を選択すると、行のリストが返され、それぞれが辞書になります。
これは私のコメント付きの擬似コードです。
しかし、もし1行戻ってきたら、それはmalan@harvard.eduという名前のユーザーがいることを意味しています。
この人が誰であれ、悪意を持ってサーバを騙し、この構文を無視させたので、彼のパスワードはわかりません。
このように、SQLインジェクション攻撃は、残念ながらSQLデータベースに対する最も一般的な攻撃の1つです。
しかし、プレースホルダを使用したり、CS50やサードパーティのライブラリを使用したりすれば、完全に防ぐことができます。
この写真は、インターネットでよく見かけるミーム(文化的に遺伝する行動など)です。
(車のナンバープレートにデータベースをクラッシュさせるコードが書いてある)
この車のナンバープレートやナンバープレートがあるべき場所にズームインすると、高速道路のカメラを騙してデータベース全体を落とそうとしていることが分かります。
ZU 0666',0,0); DROP DATABASE TABL...
DROPはデータベースのテーブルを削除するSQLのキーワードです。
この人は意図的に、あるいはユーモアを持ってこのような構文を使ってSQLを実行するよう騙そうとしたのです。
'一重引用符、--、;セミコロンなどの文字は、データベースにそのまま渡されると、SQLでは危険な文字になる可能性があります。
非常に人気のある次のxkcdコミック--ちょっと読んでみてください--もまた、コンピュータサイエンスの世界ではよく知られたミームです。
しかし、今後、あなたはLittle Bobby Tablesを知っている教養ある学習者の仲間入りを果たしたことになります。
残念なことに、ここは静寂に包まれているので、このジョークで本当に笑っている人がいるかどうかはわかりません。
しかし、とにかくこれはよく知られたミームです。
SQLを知っているコンピュータサイエンティストであれば、これを知っているはずです。

そして、最後にもう１つ、紹介したい問題があります。
それは競合状態と呼ばれるコンピュータの基本的な問題で、今回初めてSQLの議論の中で明らかになりました。
現実の世界では、SQLやSQLデータベースが非常に高性能なアプリケーションによく使われています。
つまり、GoogleやFacebook、Twitterのように、大量のデータが一度にサーバに入ってくるような世界です。
例えば、少し前に、この卵に「いいね！」をクリックした人がいるかもしれません。
(world_record_eggの写真)
これはインスタグラムでも最も「いいね！」を集めた投稿です。
昨晩の時点で5000万件以上の「いいね！」がついています。
キム・カーダシアンの以前の投稿も、まだ1800万件ほどです。
このように、「いいね！」が驚異的な勢いで押し寄せてくるというのは、なかなか難しい問題です。
というのも、簡単に言うと、InstagramはSQLデータベースを備えたサーバを持っています。
そして、そのデータベースと対話するpythonやC++などのコードがあります。
そして、「いいね！」の総数をインクリメントしようとしているコードがあるとします。
これは論理的にはどうなるでしょうか？
例えば、この卵のような写真の「いいね！」の数を増やすには、まずデータベースから、その卵の写真のIDに対する現在の「いいね！」の数を選びます。
そして、それに1を加えます。
そして、データベースを更新します。
先ほどは使いませんでしたが、INSERTやDELETEがあるように、UPDATEもありますよね。
新しいカウントに1を加えた値でデータベースを更新します。
このためのコードは次のようなものになるでしょう。
CS50ライブラリを使った3行のコードで、以下を実行します。
rows = db.execute("SELECT likes FROM posts WHERE id = ?", id);
likes = rows[0]["likes"]
db.execute("UPDATE posts SET likes = ?",likes + 1);

idはこの卵のユニークな識別子です。
そして、その結果をrows変数に格納します。
これはrowsのリストです。
最初の行、つまりrows[0]に行きます。
そして、実際の数を得るためにlikes列に行きます。
その数字を「likes」という変数に格納します。
これは5000のようになっていますが、5001にしたいのです。
そのためにはどうすればいいでしょうか？
db.execute("UPDATE posts SET likes = ?　WHERE id = ?", likes + 1, id);を入力します。
しかし、InstagramやGoogle、Twitterの問題点は、サーバが１台だけではないということです。
何千ものサーバがあります。
そして、それらのサーバはすべて、インターネット上の皆さんや私からのクリックを並行して受け取っているかもしれません。
そして、クリックによって、このコードが繰り返し実行されます。
問題は、3行のコードがあり、ブライアンと私がほぼ同時にその卵をクリックしたとすると、私の3行が彼の3行より先に実行されるとは限らないし、その逆もまた然りであるということです。
時系列的に混ざってしまうかもしれません。
私の1行目が実行された後に、ブライアンの1行目が実行されるかもしれません。
私の2行目が実行された後に、ブライアンの2行目が実行されるかもしれない。
つまり、それらは別のサーバに散らばってしまうかもしれないし、時系列的に混ざってしまうかもしれません。
これは問題です。仮にブライアンと私がほぼ同時にその卵をクリックしたとします。
そして、SELECTクエリには同じ答えが返ってきます。
5000万個が現在の数です。
そして、次のコードは、たまたま私たちがいるサーバで実行され、「いいね！」に1が追加されます。
サーバは誤って卵の行を5000万1に2回更新してしまうかもしれません。
なぜなら、根本的な問題として、私のコードが実行されている間にブライアンのコードが実行されると、私たちは実質的に同時に変数の値をチェックしていることになるからです。
そして、二人とも結論を出します。ああ、現在の「いいね！」は5000万だ。そして、私たちは決断を下します。5000万に1を加えよう。そして、値を5000万1と更新します。
問題は、ブライアンのコードや、彼がたまたまInstagramで接続しているサーバが、たまたま「いいね！」の数を最初に選択していた場合、彼は実行中のコードを終了させて、私が選択すると5000万1が表示され、それに1を加えて新しいカウントが5000万2になるようにすべきだということです。
これが競合状態と呼ばれるものです。
マルチサーバ(正確にはマルチスレッド環境)でコードを書くと、時系列上に並んだコードがいつでも別のサーバ上で混ざってしまいます。
根本的な問題は、ブライアンのサーバが変数の状態をチェックしている最中は、私はロックアウトされるべきだということです。
私が同時にそのボタンをクリックすることは許されないし、私のコードが論理的に実行されることも許されないはずです。
そこで、Twitterなどでよく見られるように、このようなコードを書かなければならない場合には、トランザクションと呼ばれるものを使うことによって、この問題を解決することができます。
トランザクションにはいくつかの新しい構文が含まれますが、今日は説明しませんし、今後も使う必要はないでしょう。
しかし、トランザクションは根本的に難しい問題を解決します。

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 = ?",likes + 1);
db.execute("COMMIT")

この場合、Twitterのあなたは真ん中の3行だけを実行するべきではありません。
TwitterはまずBEGIN TRANSACTIONを実行して、最後にトランザクションをCOMMITするべきです。
そして、トランザクションは、その間のすべての行が完全に成功するか、完全に失敗するかのどちらかです。
データベースは、卵についた「いいね！」の数がわからなくなるような、おかしな状態にはなりません。
近年では問題になっていませんが、Twitterが始まったばかりの頃、人気が出過ぎて頻繁にオフラインになっていました。
Fail Whaleと呼ばれるものがありましたが、これはトラフィックが多すぎて処理できないときにウェブサイトに表示される写真です。
人々が「いいね！」を押したり、ツイートやリツイートしたりすると、膨大な量のデータが入ってくるからです。
そして、このような問題を解決するのは非常に難しいことがわかりました。
しかし、データベースのテーブルや行をこれらのトランザクションでロックすることは、根本的にこれを解決する１つの方法です。
今日の最後に、私が何年か前にトランザクションを教わったのと同じ例でこれを演じてみようと思います。
例えば、皆さんと皆さんのルームメイトが、寮に素敵な冷蔵庫を持っているとします。
そして、二人ともミルクをたくさん飲む習慣があり、二人ともミルクを飲みたくなったとします。
皆さんは私が今からそうするように、冷蔵庫に行きます。
そして、あーあ、牛乳が切れたと気づくのです。
そして今、私はこの冷蔵庫の状態を調べています。
冷蔵庫はかなり古いものですが、中身が空っぽです。
空っぽになった冷蔵庫の状態を見ると、CVSに行ってもっと牛乳を買うべきだとわかります。
では、私は何をすればいいのでしょうか？
恐らく、冷蔵庫を閉じて、CVSに向かって出発することになるでしょう。
残念ながら、私たちが最後の60秒で演じるのと同じ問題が発生します。
つまり、この物語のルームメイトであるブライアンも牛乳を欲しがっていて、私がすでに店に向かっているときに彼がやってきて、冷蔵庫の状態を調べ、あーあ、牛乳が切れたと気づくのです。
そこで彼は親切にも牛乳を補充しに行ってくれます。
では、これがどうなるか見てみましょう。
似たような、類似した解決策がないかどうか見てみましょう。
私は冷蔵庫(変数)の状態をチェックしました。
確かに牛乳が切れていますね。
すぐ戻ってきます。
CVSに行ってきます。
(その場を離れる)
(ブライアンが冷蔵庫を見に来て、その場を離れる)
(先生、外に出る→寄り道しててブライアンに先を越される)
(ブライアンが先に牛乳を買って戻ってくる)
(先生、牛乳を買って戻ってくる)
さて、戻ってきました。
牛乳を買ってきました。
冷蔵庫を開けると…おお！なぜこんなことに！？
何本もの牛乳パックがあります。
もちろん、牛乳はそんなに長くは持ちません。
ブライアンと私はそんなに牛乳を飲まないし。
だから、これは本当に深刻な問題なのです。
私たちはこの変数の値を同時に更新しようとしました。
この問題を解決するにはどうすればいいでしょうか？
実際の解決策はなんなのでしょうか？
強いて言えば、トランザクションやデータベースの世界からヒントを得ることができるでしょう。
そして、今日のことを忘れないように、ここで映像を作ってみましょう。
最後にもう一度、もう少し過激な演技をしてみましょう。
私は冷蔵庫を見ます。
牛乳を切らしていることに気がつきました。
お店に行ってこよう。
ブライアンが誤って冷蔵庫をチェックしてしまうような自体は避けたいですね。
だから、冷蔵庫には鍵をかけておこうと思います。
(鎖付きの鍵を持ち出してくる)
少し極端ですが、彼が冷蔵庫を開けられない限り、問題ないと思います。
それでは、ここにロックを取り付けてみましょう。
これで冷蔵庫はロックされました。
私は牛乳を買いに行きます。
(ブライアンが来て、冷蔵庫を開けられなくてやれやれ…なとこで、おしまい)