スプレッドシートとデータベース

スプレッドシートとデータベースの異同を検討しましょう。スプレッドシートでは、各行がそれぞれのレコードであり、各列がそれぞれのレコードのフィールドに保存されたデータの種類を表します。例えば、図16-1は私のネコのスプレッドシートです。列は名前、生年月日、毛色、体重（キログラム）です。

この同じ情報をデータベースに保存できます。データベースに含まれるテーブルをスプレッドシートになぞらえることができます。テーブルにはそれぞれのレコード（行やエントリーと呼ばれることもあります）の特性に応じたカラム（列）があります。SQLiteのようなデータベースはリレーショナルデータベースと呼ばれます。リレーション（関係）というのは、データベースに含まれる複数のテーブル間の関係のことです（後述します）。

スプレッドシートもデータベースも、保存しているデータにラベルをつけます。スプレッドシートは、列にはアルファベット、行には数字のラベルを自動的につけます。さらに、先ほどの例のネコのスプレッドシートでは、最初の行が列を説明する名前になっています。2行目以下は一つの行が一匹のネコを表しています。SQLデータベースでは、各テーブルにたいていIDカラムがあります。IDカラムはレコードの主キーで、レコードを明確に特定できる一意の整数です。SQLiteでは、このカラムがrowidと呼ばれ、自動的にテーブルに追加されます。

スプレッドシートの行を削除するとその下の行が繰り上がり、行番号が変わります。しかしデータベースのレコードの主キーIDは一意であって変更されません。この特性は多くの状況で役立ちます。ネコの名前が変わったり体重の増減があったりした場合にどうなるでしょうか。名前のアルファベット順に行を並べ替えたい場合はどうすればよいでしょうか。ネコには、データが変わっても一定である固有のID番号が必要です。SQLiteのテーブルのrowidカラムに相当するようなID列をスプレッドシートに設けることも可能です。行が削除されたり移動されたりしても、このID値は同じままです。それを図16-2に示しました。Row IDが5から10の行を削除しました。

スプレッドシートは、データベースとは全く異なるやり方で使われることもあります。行ベースのデータを保存するのではなく、テンプレートとして使われることがあります。図16-3のようなスプレッドシートを目にしたことがあるでしょう。

このようなスプレッドシートは、背景色、セル結合、フォントなどのフォーマットを多用しており、人間の目には見やすいです。行ベースのデータのスプレッドシートは無限に下へと新しいデータを追加できるのに対し、こうしたスプレッドシートはサイズが固定されていて空欄を埋めていく方式のデザインになっています。Pythonプログラムがデータを抽出することではなく、人間が印刷して眺めることが意図されています。

データベースは見た目のためのものではなく、生のデータを保存します。さらに重要なこととして、スプレッドシートはどのセルにどのようなデータでも入れられる柔軟性がありますが、データベースにはソフトウェアがデータを取得しやすくするための厳格な構造があります。上に示した例のようにデータがフォーマットされていたら、第14章のopenpyxlライブラリや第15章のEZSheetsライブラリを使って、ExcelやGoogleスプレッドシートのまま処理したほうがよいでしょう。

SQLiteと他のSQLデータベース

他のSQLデータベースに慣れている人は、SQLiteがそれとどう違うのか気になるところでしょう。端的に言うと、SQLiteは単純さと強力さのバランスを保っています。SQLで大量のデータを読み書きする完全なリレーショナルデータベースでありながら、Pythonプログラム内で実行され一つのファイルで操作が完結します。sys、mathその他のPython標準ライブラリと同じようにsqlite3モジュールをインポートします。

SQLiteと他のデータベースソフトウェアとの主な違いを列挙します。

• SQLiteは一つのファイルにデータを保存するので、移動、コピー、バックアップなどが簡単
• SQLiteは、組み込みデバイスや数十年前のノートパソコンなど、リソースが乏しいコンピュータでも実行できる
• SQLiteはサーバーレスであるため、バックグラウンドのサーバーアプリケーションが不要であり、ネットワーク通信も不要
• ユーザー目線では、SQLiteはPythonプログラムの一部であり、インストールと設定が不要
• 速度パフォーマンスの観点からは、SQLiteはメモリ内で動作してプログラムの終了前にファイルに書き出すことができる
• 他のSQLデータベースと同様に、カラムには数値型やテキスト型などのデータ型があるが、SQLiteはカラムのデータ型を厳密に強制はしない
• パーミッション（権限）その他のユーザーロールの設定が不要であり、他のSQLデータベースにあるようなGRANT文やREVOKE文は存在しない
• SQLiteはパブリックドメインのソフトウェアであり、商用利用でもどのような利用でも制限なしにできる

SQLiteの大きな弱点は、（ソーシャルメディアのウェブアプリで行われるような）数十万の同時書き込み操作を効率的に扱えないことです。その点を除けば、SQLiteは他のデータベースと同じくらい強力で、GBやTB単位のデータを確実に処理でき、同時読み取りを高速で簡単に行えます。

SQLiteは、他のデータベースよりも、テキストファイル（や第18章で取り上げるJSON、XML、CSVファイル）をopen()関数を使って操作することに近いです。大量のデータを保存して素早く検索する必要があるなら、SQLiteがJSONやスプレッドシートファイルよりも優れた方法になります。

データベースとテーブルの作成

最初のデータベースとテーブルをSQLを使って作成してみましょう。SQLは、正規表現のように、Python内で使えるミニ言語です。正規表現と同様に、SQLクエリはPythonの文字列値として書けます。正規表現を使わずにそれと同等のテキストのパターンマッチングを行うPythonのコードを書けるのと同じように、SQLクエリを使わずにPythonの辞書やリストのデータを検索するPythonのコードを書くことは確かにできます。しかし正規表現やSQLデータベースクエリを書くと、最初は新しい技術の学習を求められますが、長期的にはそうした作業がずっとシンプルになります。新しいデータベースでテーブルを作成するクエリの書き方から始めましょう。

ネコについての情報を保存するexample.dbという名前のファイルでサンプルのSQLiteデータベースを作成します。データベースを作成するには、まずsqlite3モジュールをインポートします（3はSQLiteのメジャーバージョンの3で、Python3とは関係ありません）。SQLiteデータベースは一つのファイルで完結します。ファイル名は何でもいいですが、慣習的に.dbというファイル拡張子にします。.sqliteという拡張子も一般的に用いられます。

データベースは複数のテーブルを持ち、各テーブルは一定の型のデータを保存します。例えば、あるテーブルにネコのレコードを保存し、別のテーブルに最初のテーブルのネコのワクチン接種レコードを保存します。テーブルはタプルのリストで、それぞれのタプルがテーブルの行だと考えることができます。catsテーブルは本質的には[('Zophie', '2021-01-24', 'black', 5.6), ('Colin', '2016-12-24', 'siamese', 6.2), ...]というタプルのリストと同じです。

データベースを作成して、ネコのデータを保存するテーブルを作成し、何匹かのネコのレコードを挿入して、データベースからそのデータを読み取り、データベース接続を閉じてみましょう。

>>> import sqlite3
>>> conn = sqlite3.connect('example.db', isolation_level=None)

>>> import sqlite3
>>> conn = sqlite3.connect('example.db', isolation_level=None)
>>> conn.execute('CREATE TABLE IF NOT EXISTS cats (name TEXT NOT NULL,
birthdate TEXT, fur TEXT, weight_kg REAL) STRICT')
<sqlite3.Cursor object at 0x00000201B2399540>

>>> import sqlite3
>>> sqlite3.sqlite_version
'3.xx.xx'

>>> import sqlite3
>>> conn = sqlite3.connect('example.db', isolation_level=None)
>>> conn.execute('SELECT name FROM sqlite_schema WHERE type="table"').fetchall()
[('cats',)]

>>> import sqlite3
>>> conn = sqlite3.connect('example.db', isolation_level=None)
>>> conn.execute('PRAGMA TABLE_INFO(cats)').fetchall()
[(0, 'name', 'TEXT', 1, None, 0), (1, 'birthdate', 'TEXT', 0, None, 0), (2,
'fur', 'TEXT', 0, None, 0), (3, 'weight_kg', 'REAL', 0, None, 0)]

データベースのCRUD操作

CRUDはデータベースが実行する4つの基本操作――作成（Create）、読み取り（Read）、更新（Update）、削除（Delete）――の頭字語です。SQLiteでは、これらの操作を、それぞれ、INSERT文、SELECT文、UPDATE文、DELETE文で行います。conn.execute()に文字列として渡すことになる、それぞれの文の例を示します。

• INSERT INTO cats VALUES ("Zophie", "2021-01-24", "black", 5.6)
• SELECT rowid, * FROM cats ORDER BY fur
• UPDATE cats SET fur = "gray tabby" WHERE rowid = 1
• DELETE FROM cats WHERE rowid = 1

ほとんどのアプリケーションやソーシャルメディアサイトは、実際のところ、CRUDデータベースの手の込んだユーザーインターフェイスです。写真を投稿したり返信したりすると、どこかにあるデータベースにレコードを作成しています。ソーシャルメディアのタイムラインをスクロールしたら、データベースからレコードを読み取っています。投稿を編集や削除したら、更新や削除の操作を実行しています。新しいアプリやプログラミング言語やクエリ言語の使い方を学ぶ際には、CRUDの基本操作を軸に考えてください。

>>> import sqlite3
>>> conn = sqlite3.connect('example.db', isolation_level=None)
>>> conn.execute('CREATE TABLE IF NOT EXISTS cats (name TEXT NOT NULL, birthdate TEXT,
fur TEXT, weight_kg REAL) STRICT')
<sqlite3.Cursor object at 0x00000201B2399540>
>>> conn.execute('INSERT INTO cats VALUES ("Zophie", "2021-01-24", "black", 5.6)')
<sqlite3.Cursor object at 0x00000162842E78C0>

>>> cat_name = 'Zophie'
>>> cat_bday = '2021-01-24'
>>> fur_color = 'black'
>>> cat_weight = 5.6
>>> conn.execute(f'INSERT INTO cats VALUES ("{cat_name}", "{cat_bday}",
"{fur_color}", {cat_weight})')
<sqlite3.Cursor object at 0x0000022B91BB7C40>

>>> conn.execute('INSERT INTO cats VALUES (?, ?, ?, ?)', [cat_name, cat_bday,
fur_color, cat_weight])
<sqlite3.Cursor object at 0x0000022B91BB7C40>

>>> import sqlite3
>>> conn = sqlite3.connect('example.db', isolation_level=None)
>>> conn.execute('SELECT * FROM cats').fetchall()
[('Zophie', '2021-01-24', 'black', 5.6)]

>>> conn.execute('SELECT rowid, name FROM cats').fetchall()
[(1, 'Zophie')]

>>> import sqlite3
>>> conn = sqlite3.connect('sweigartcats.db', isolation_level=None)
>>> for row in conn.execute('SELECT * FROM cats'):
...     print('Row data:', row)
...     print(row[0], 'is one of my favorite cats.')
...
Row data: ('Zophie', '2021-01-24', 'gray tabby', 5.6)
Zophie is one of my favorite cats.
Row data: ('Miguel', '2016-12-24', 'siamese', 6.2)
Miguel is one of my favorite cats.
Row data: ('Jacob', '2022-02-20', 'orange and white', 5.5)
Jacob is one of my favorite cats.
--snip--

>>> import sqlite3
>>> conn = sqlite3.connect('sweigartcats.db', isolation_level=None)
>>> conn.execute('SELECT * FROM cats WHERE fur = "black"').fetchall()
[❶('Zophie', '2021-01-24', 'black', 5.6), ('Toby', '2021-05-17', 'black',
6.8), ('Thor', '2013-05-14', 'black', 5.2), ('Sassy', '2017-08-20', 'black',
7.5), ('Hope', '2016-05-22', 'black', 7.6)]

>>> import pprint
>>> matching_cats = conn.execute('SELECT * FROM cats WHERE fur = "black"
OR birthdate >= "2024-01-01"').fetchall()
>>> pprint.pprint(matching_cats)
[('Zophie', '2021-01-24', 'black', 5.6),
 ('Toby', '2021-05-17', 'black', 6.8),
 ('Taffy', '2024-12-09', 'white', 7.0),
 ('Hollie', '2024-08-07', 'calico', 6.0),
 ('Lewis', '2024-03-19', 'orange tabby', 5.1),
 ('Thor', '2013-05-14', 'black', 5.2),
 ('Shell', '2024-06-16', 'tortoiseshell', 6.5),
 ('Jasmine', '2024-09-05', 'orange tabby', 6.3),
 ('Sassy', '2017-08-20', 'black', 7.5),
 ('Hope', '2016-05-22', 'black', 7.6)]

>>> conn.execute('SELECT rowid, name FROM cats WHERE name LIKE "%y"').fetchall()
[(5, 'Toby'), (11, 'Molly'), (12, 'Dusty'), (17, 'Mandy'), (18, 'Taffy'), (25, 'Rocky'), (27,
'Bobby'), (30, 'Misty'), (34, 'Mitsy'), (38, 'Colby'), (40, 'Riley'), (46, 'Ruby'), (65,
'Daisy'), (67, 'Crosby'), (72, 'Harry'), (77, 'Sassy'), (85, 'Lily'), (93, 'Spunky')]
>>> conn.execute('SELECT rowid, name FROM cats WHERE name LIKE "Ja%"').fetchall()
[(3, 'Jacob'), (49, 'Java'), (75, 'Jasmine'), (80, 'Jamison')]

>>> conn.execute('SELECT rowid, name FROM cats WHERE name LIKE "%ob%"').fetchall()
[(3, 'Jacob'), (5, 'Toby'), (27, 'Bobby')]

>>> conn.execute('SELECT rowid, name FROM cats WHERE name GLOB "*m*"').fetchall()
[(4, 'Gumdrop'), (9, 'Thomas'), (44, 'Sam'), (63, 'Cinnamon'), (75, 'Jasmine'),
(79, 'Samantha'), (80, 'Jamison')]

>>> import sqlite3, pprint
>>> conn = sqlite3.connect('sweigartcats.db', isolation_level=None)
>>> pprint.pprint(conn.execute('SELECT * FROM cats ORDER BY fur').fetchall())
[('Iris', '2017-07-13', 'bengal', 6.8),
 ('Ruby', '2023-12-22', 'bengal', 5.0),
 ('Elton', '2020-05-28', 'bengal', 5.4),
 ('Toby', '2021-05-17', 'black', 6.8),
 ('Thor', '2013-05-14', 'black', 5.2),
--snip--
 ('Celine', '2015-04-18', 'white', 7.3),
 ('Daisy', '2019-03-19', 'white', 6.0)]

>>> cur = conn.execute('SELECT * FROM cats ORDER BY fur, birthdate')
>>> pprint.pprint(cur.fetchall())
[('Iris', '2017-07-13', 'bengal', 6.8),
 ('Elton', '2020-05-28', 'bengal', 5.4),
 ('Ruby', '2023-12-22', 'bengal', 5.0),
 ('Thor', '2013-05-14', 'black', 5.2),
 ('Hope', '2016-05-22', 'black', 7.6),
--snip--
 ('Ginger', '2020-09-22', 'white', 5.8),
 ('Taffy', '2024-12-09', 'white', 7.0)]

>>> cur = conn.execute('SELECT * FROM cats ORDER BY fur ASC, birthdate DESC')
>>> pprint.pprint(cur.fetchall())
[('Ruby', '2023-12-22', 'bengal', 5.0),
 ('Elton', '2020-05-28', 'bengal', 5.4),
 ('Iris', '2017-07-13', 'bengal', 6.8),
 ('Toby', '2021-05-17', 'black', 6.8),
 ('Sassy', '2017-08-20', 'black', 7.5),
--snip--
 ('Mitsy', '2015-05-29', 'white', 5.0),
 ('Celine', '2015-04-18', 'white', 7.3)]

>>> import sqlite3
>>> conn = sqlite3.connect('sweigartcats.db', isolation_level=None)
>>> conn.execute('SELECT * FROM cats').fetchall()[:3]  # これは非効率
[('Zophie', '2021-01-24', 'gray tabby', 5.6), ('Miguel', '2016-12-24',
'siamese', 6.2), ('Jacob', '2022-02-20', 'orange and white', 5.5)]

>>> conn.execute('SELECT * FROM cats LIMIT 3').fetchall()
[('Zophie', '2021-01-24', 'gray tabby', 5.6), ('Miguel', '2016-12-24',
'siamese', 6.2), ('Jacob', '2022-02-20', 'orange and white', 5.5)]

>>> conn.execute('SELECT * FROM cats WHERE fur="orange" ORDER BY birthdate LIMIT 4').fetchall()
[('Mittens', '2013-07-03', 'orange', 7.4), ('Piers', '2014-07-08', 'orange', 5.2),
('Misty', '2016-07-08', 'orange', 5.2), ('Blaze', '2023-01-16', 'orange', 7.4)]

>>> import sqlite3
>>> conn = sqlite3.connect('sweigartcats.db', isolation_level=None)
>>> conn.execute('CREATE INDEX idx_name ON cats (name)')
<sqlite3.Cursor object at 0x0000013EC121A040>
>>> conn.execute('CREATE INDEX idx_birthdate ON cats (birthdate)')
<sqlite3.Cursor object at 0x0000013EC121A040>

>>> conn.execute('SELECT name FROM sqlite_schema WHERE type = "index" AND
tbl_name = "cats"').fetchall()
[('idx_name',), ('idx_birthdate',)]

>>> conn.execute('SELECT name FROM sqlite_schema WHERE type = "index" AND
tbl_name = "cats"').fetchall()
[('idx_birthdate',) ('idx_name',)]
>>> conn.execute('DROP INDEX idx_name')
<sqlite3.Cursor object at 0x0000013EC121A040>
>>> conn.execute('SELECT name FROM sqlite_schema WHERE type = "index" AND
tbl_name = "cats"').fetchall()
[('idx_birthdate',)]

>>> import sqlite3
>>> conn = sqlite3.connect('sweigartcats.db', isolation_level=None)
>>> conn.execute('SELECT * FROM cats WHERE rowid = 1').fetchall()
[('Zophie', '2021-01-24', 'black', 5.6)]
>>> conn.execute('UPDATE cats SET fur = "gray tabby" WHERE rowid = 1')
<sqlite3.Cursor object at 0x0000013EC121A040>
>>> conn.execute('SELECT * FROM cats WHERE rowid = 1').fetchall()
[('Zophie', '2021-01-24', 'gray tabby', 5.6)]

>>> conn.execute('UPDATE cats SET fur = "orange and white" WHERE fur = "white and orange"')

>>> import sqlite3
>>> conn = sqlite3.connect('sweigartcats.db', isolation_level=None)
>>> conn.execute('SELECT rowid, * FROM cats WHERE rowid = 1').fetchall()
[(1, 'Zophie', '2021-01-24', 'gray tabby', 5.6)]
>>> conn.execute('DELETE FROM cats WHERE rowid = 1')
<sqlite3.Cursor object at 0x0000020322D183C0>
>>> conn.execute('SELECT * FROM cats WHERE rowid = 1').fetchall()
[]

トランザクションのロールバック

いくつかのクエリをまとめて実行するか全部実行しないかのどちらかにしたいことが時々あります。しかし、クエリを実行してみないと、そのどちらがいいのかわかりません。こうした状況に対応する一つの方法は、トランザクションを開始することです。クエリを実行し、すべてのクエリをデータベースにコミットしてトランザクションを完了させるか、ロールバックしてデータベースを何事もなかったかのような状態にするかのいずれかになります。

SQLiteデータベースに自動コミットモードで接続していれば、通常、1回のconn.execute()呼び出しごとにトランザクションを開始して終了します。しかし、BEGINクエリで新しいトランザクションを開始することもできます。そうすると、conn.commit()を呼び出してトランザクションを完了させるか、conn.rollback()を呼び出してすべてのクエリをなかったことにするかのいずれかになります。

例えば、catsテーブルに2匹の新しいネコを追加して、そのトランザクションをロールバックしてみます。そうすればテーブルは変更されないままです。

>>> import sqlite3
>>> conn = sqlite3.connect('sweigartcats.db', isolation_level=None)
>>> conn.execute('BEGIN')
<sqlite3.Cursor object at 0x00000219C8BF7C40>
>>> conn.execute('INSERT INTO cats VALUES ("Socks", "2022-04-04", "white", 4.2)')
<sqlite3.Cursor object at 0x00000219C8BF7C40>
>>> conn.execute('INSERT INTO cats VALUES ("Fluffy", "2022-10-30", "gray", 4.5)')
<sqlite3.Cursor object at 0x00000219C8BF7C40>
>>> conn.rollback()  # これによりINSERT文がなかったことになる
>>> conn.execute('SELECT * FROM cats WHERE name = "Socks"').fetchall()
[]
>>> conn.execute('SELECT * FROM cats WHERE name = "Fluffy"').fetchall()
[]

新しいネコのSocksとFluffyは、データベースに挿入されていません。

反対に、実行したクエリをすべて適用したければ、conn.commit()を呼び出してデータベースに変更をコミットします。

>>> conn.execute('BEGIN')
<sqlite3.Cursor object at 0x00000219C8BF7C40>
>>> conn.execute('INSERT INTO cats VALUES ("Socks", "2022-04-04", "white", 4.2)')
<sqlite3.Cursor object at 0x00000219C8BF7C40>
>>> conn.execute('INSERT INTO cats VALUES ("Fluffy", "2022-10-30", "gray", 4.5)')
<sqlite3.Cursor object at 0x00000219C8BF7C40>
>>> conn.commit()
>>> conn.execute('SELECT * FROM cats WHERE name = "Socks"').fetchall()
[('Socks', '2022-04-04', 'white', 4.2)]
>>> conn.execute('SELECT * FROM cats WHERE name = "Fluffy"').fetchall()
[('Fluffy', '2022-10-30', 'gray', 4.5)]

SocksとFluffyのネコはデータベースのレコードに存在します。

データベースのバックアップ

ある私の友人が、収集用のスポーツカードを専門に扱っているECサイトで使っているデータベースに変更を加えようとしていました。いくつかのカードに含まれる誤りを訂正しなければならず、UPDATE cards SET name = 'Chris Clemons'と入力したところで、その友人のネコがキーボートの上に乗り、ENTERキーを押しました。WHERE節がなかったので、このクエリはウェブサイトで販売中の数千枚のカードすべてを更新してしまいました。

幸い、その友人はデータベースのバックアップを取得していたので、以前の状態に復元することができました。（バックアップを取得していて本当によかったです。というのも、同じことが再び起こりました。そのネコはわざとやっているのでしょう。）

プログラムが現在SQLiteデータベースに接続していなければ、データベースファイルを単純にコピーするだけでバックアップを取得できます。Pythonプログラムでは、第11章で説明したように、shutil.copy('sweigartcats.db', 'backup.db')を呼び出せばファイルをコピーできます。しかし、ソフトウェアから絶えずデータベースの内容を読み書きしているなら、（単純にファイルをコピーするというわけにはいかないので）Connectionオブジェクトのbackup()メソッドを使う必要があります。対話型シェルで次のように入力してみてください。

>>> import sqlite3
>>> conn = sqlite3.connect('sweigartcats.db', isolation_level=None)
>>> backup_conn = sqlite3.connect('backup.db', isolation_level=None)
>>> conn.backup(backup_conn)

backup()メソッドは、他のクエリが実行中であっても、sweigartcats.dbデータベースの内容をbackup.dbファイルへと安全にバックアップします。これでデータを安全にバックアップできましたから、ネコが好きなだけキーボートの上に乗ることができます。

テーブルの変更と削除

データベースにテーブルを作成して行を挿入してから、テーブル名やカラム名を変更したくなることがあるかもしれません。テーブルのカラムの追加や削除、あるいはテーブル全体を削除したい場合もあるでしょう。ALTER TABLEクエリを実行すればこれらの操作ができます。

以下の対話型シェルの例では、sweigartcats.dbデータベースファイルの最初の状態から始めます。ALTER TABLE RENAMEクエリを実行して、テーブル名をcatsからfelinesに変更します。

>>> import sqlite3
>>> conn = sqlite3.connect('sweigartcats.db', isolation_level=None)
>>> conn.execute('SELECT name FROM sqlite_schema WHERE type="table"').fetchall()
[('cats',)]
>>> conn.execute('ALTER TABLE cats RENAME TO felines')  # テーブル名の変更
<sqlite3.Cursor object at 0x000001EDDB477C40>
>>> conn.execute('SELECT name FROM sqlite_schema WHERE type="table"').fetchall()
[('felines',)]

テーブル内のカラム名を変更するには、ALTER TABLE RENAME COLUMNクエリを実行します。例えば、カラム名をfurからdescriptionに変更してみましょう。

>>> conn.execute('PRAGMA TABLE_INFO(felines)').fetchall()[2]  # 3番目のカラムを表示
(2, 'fur', 'TEXT', 0, None, 0)
>>> conn.execute('ALTER TABLE felines RENAME COLUMN fur TO description')
<sqlite3.Cursor object at 0x000001EDDB477C40>
>>> conn.execute('PRAGMA TABLE_INFO(felines)').fetchall()[2]  # 3番目のカラムを表示
(2, 'description', 'TEXT', 0, None, 0)

テーブルに新しいカラムを追加するには、ALTER TABLE ADD COLUMNクエリを実行します。例えば、felinesテーブルに、ブール値が入るis_lovedカラムを新しく追加してみます。SQLiteでは、0がfalseを、1がtrueを表します。is_lovedのデフォルト値は1にします。

>>> conn.execute('ALTER TABLE felines ADD COLUMN is_loved INTEGER DEFAULT 1')
<sqlite3.Cursor object at 0x000001EDDB477C40>
>>> import pprint
>>> pprint.pprint(conn.execute('SELECT * FROM felines LIMIT 3').fetchall())
[('Zophie', '2021-01-24', 'gray tabby', 5.6, 1),
 ('Miguel', '2016-12-24', 'siamese', 6.2, 1),
 ('Jacob', '2022-02-20', 'orange and white', 5.5, 1)]

私はどのネコについても1を保存するので、is_lovedカラムは不要だとわかりました。そこで、ALTER TABLE DROP COLUMNクエリでこのカラムを削除します。

>>> conn.execute('PRAGMA TABLE_INFO(felines)').fetchall()  # すべてのカラムを表示
[(0, 'name', 'TEXT', 1, None, 0), (1, 'birthdate', 'TEXT', 0, None, 0), (2, 'description', 'TEXT',
0, None, 0), (3, 'weight_kg', 'REAL', 0, None, 0), (4, 'is_loved', 'INTEGER', 0, '1', 0)]
>>> conn.execute('ALTER TABLE felines DROP COLUMN is_loved')  # カラムの削除
<sqlite3.Cursor object at 0x000001EDDB477C40>
>>> conn.execute('PRAGMA TABLE_INFO(felines)').fetchall()  # すべてのカラムを表示
[(0, 'name', 'TEXT', 1, None, 0), (1, 'birthdate', 'TEXT', 0, None, 0), (2, 'description', 'TEXT',
0, None, 0), (3, 'weight_kg', 'REAL', 0, None, 0)]

削除されるカラムに保存されているデータはすべて削除されます。

テーブル全体を削除したければ、DROP TABLEクエリを実行します。

>>> conn.execute('SELECT name FROM sqlite_schema WHERE type="table"').fetchall()
[('felines',)]
>>> conn.execute('DROP TABLE felines')  # テーブル全体を削除
<sqlite3.Cursor object at 0x000001EDDB477C40>
>>> conn.execute('SELECT name FROM sqlite_schema WHERE type="table"').fetchall()
[]

テーブルやカラムの変更は控えめにしてください。これらを変更すると、対応するプログラムのクエリも変更しなければならないからです。

外部キーで複数のテーブルを結合する

SQLiteのテーブル構造はかなり厳格です。例えば、それぞれの行についてカラムの数が定まっています。しかし、現実世界のデータは一つのテーブルに収まるほと単純ではないことがしばしばあります。リレーショナルデータベースでは、複雑なデータを複数のテーブルに保存し、外部キーでテーブルを結合します。

ネコが接種したワクチンの情報を保存したいとしましょう。ネコが接種するワクチンは複数になる可能性があるので、catsテーブルにカラムを追加するわけにはいきません。さらに、ワクチンの接種ごとに、接種日と担当医師をリスト化したいです。SQLのテーブルはカラムのリストを保存するのに適していません。vaccination1、vaccination2、vaccination3といった名前のカラムを作りたくないです。vaccination1、vaccination2といった名前の変数を作りたくないのと同じです。カラムや変数を作りすぎると、コードが冗長になり、散らかって読みにくくなってしまいます。逆にこの要領で最初に用意するカラムが少なすぎると、必要に応じて追加するときにプログラムを更新しなければならなくなります。

一つの行に追加するデータの量が変わる場合は、別のテーブルの行にデータを追加するのが理に適っています。そうすれば、別テーブルの行を元のメインとなるテーブルの行から参照できます。sweigartcats.dbデータベースで、対話型シェルで以下の内容を入力して、vaccinationsという2つ目のテーブルを追加してください。

>>> import sqlite3
>>> conn = sqlite3.connect('sweigartcats.db', isolation_level=None)
>>> conn.execute('PRAGMA foreign_keys = ON')
<sqlite3.Cursor object at 0x000001E730AD03C0>
>>> conn.execute('CREATE TABLE IF NOT EXISTS vaccinations (vaccine TEXT,
date_administered TEXT, administered_by TEXT, cat_id INTEGER,
FOREIGN KEY(cat_id) REFERENCES cats(rowid)) STRICT')
<sqlite3.Cursor object at 0x000001CA42767D40>

新しいvaccinationsテーブルには、INTEGER型のcat_idという名前のカラムがあります。このカラムの整数値はcatsテーブルの行のrowidの値に対応します。別のテーブルの主キーを参照するので、cat_idカラムを外部キーと呼びます。

catsテーブルでは、ネコのZophieのrowidは1です。そのネコのワクチンを記録するには、vaccinationsテーブルにcat_idの値が1の行を挿入します。

>>> conn.execute('INSERT INTO vaccinations VALUES ("rabies", "2023-06-06", "Dr. Echo", 1)')
<sqlite3.Cursor object at 0x000001CA42767D40>
>>> conn.execute('INSERT INTO vaccinations VALUES ("FeLV", "2023-06-06", "Dr. Echo", 1)')
<sqlite3.Cursor object at 0x000001CA42767D40>
>>> conn.execute('SELECT * FROM vaccinations').fetchall()
[('rabies', '2023-06-06', 'Dr. Echo', 1), ('FeLV', '2023-06-06', 'Dr. Echo', 1)]

他のネコについてもrowidを使ってワクチンの記録ができます。Mangoのワクチン記録を追加するなら、catsテーブルのMangoのrowidを探し、その値をvaccinationsテーブルのcat_idカラムに入れてレコードを追加します。

>>> conn.execute('SELECT rowid, * FROM cats WHERE name = "Mango"').fetchall()
[(23, 'Mango', '2017-02-12', 'tuxedo', 6.8)]
>>> conn.execute('INSERT INTO vaccinations VALUES ("rabies", "2023-07-11", "Dr. Echo", 23)')
<sqlite3.Cursor object at 0x000001CA42767D40>

inner join（内部結合）と呼ばれるSELECTクエリを実行して、両方のテーブルの行を結合した結果を得ることができます。例えば、対話型シェルに以下の内容を入力して、catsテーブルのデータとvaccinationsの行を結合した結果を取得してみてください。

>>> conn.execute('SELECT * FROM cats INNER JOIN vaccinations ON cats.rowid = 
vaccinations.cat_id').fetchall()
[('Zophie', '2021-01-24', 'gray tabby', 5.6, 'rabies', '2023-06-06', 'Dr. Echo', 1),
 ('Zophie', '2021-01-24', 'gray tabby', 5.6, 'FeLV', '2023-06-06', 'Dr. Echo', 1),
 ('Mango', '2017-02-12', 'tuxedo', 6.8, 'rabies', '2023-07-11', 'Dr. Echo', 23)]

cat_idをINTEGER型のカラムにしてFOREIGN KEY(cat_id) REFERENCES cats(rowid)構文を使わずに外部キーとして利用することも可能ではありますが、構文を使った外部キーにはデータの整合性を保つための安全装置が組み込まれています。例えば、存在しないネコのcat_idを使ったワクチンレコードの挿入や更新はできません。「孤立した」ワクチンレコードを残してしまわないように、SQLiteでは（catsテーブルの）ネコを削除する前にそのネコのワクチンレコードをすべて削除することが強制されます。

こうした安全装置はデフォルトで無効になっています。sqlite3.connect()呼び出しのあとでPRAGMAクエリを実行すると有効にできます。

>>> conn.execute('PRAGMA foreign_keys = ON')

外部キーと結合にはさらに発展的な機能がありますが、本書での説明はこれくらいにします。

インメモリデータベースとバックアップ

プログラムから多数のクエリを実行するのであれば、インメモリデータベースを利用するとデータベースの速度を劇的に向上させられる可能性があります。インメモリデータベースは、コンピュータのハードドライブ上のファイルではなく、すべてメモリ上に保存されます。そのおかげで変更がとても高速になります。しかし、backup()メソッドでインメモリデータベースをファイルに保存することを忘れないようにしなければなりません。プログラムが実行中にクラッシュすれば、プログラムの変数に格納された値を失ってしまうのと同じように、インメモリデータベース全体を失ってしまいます。

以下の例では、インメモリデータベースを作成し、test.dbというファイルにデータベースを保存します。

>>> import sqlite3
>>> memory_db_conn = sqlite3.connect(':memory:',
isolation_level=None)  # インメモリデータベースを作成
>>> memory_db_conn.execute('CREATE TABLE test (name TEXT, number REAL)')
<sqlite3.Cursor object at 0x000001E730AD0340>
>>> memory_db_conn.execute('INSERT INTO test VALUES ("foo", 3.14)')
<sqlite3.Cursor object at 0x000001D9B0A07EC0>
>>> file_db_conn = sqlite3.connect('test.db', isolation_level=None)
>>> memory_db_conn.backup(file_db_conn)  # データベースをtest.dbファイルに保存

SQLiteデータベースファイルのロードも同様にbackup()メソッドで行います。

>>> import sqlite3
>>> file_db_conn = sqlite3.connect('sweigartcats.db', isolation_level=None)
>>> memory_db_conn = sqlite3.connect(':memory:', isolation_level=None)
>>> file_db_conn.backup(memory_db_conn)
>>> memory_db_conn.execute('SELECT * FROM cats LIMIT 3').fetchall()
[('Zophie', '2021-01-24', 'gray tabby', 5.6), ('Miguel', '2016-12-24',
'siamese', 6.2), ('Jacob', '2022-02-20', 'orange and white', 5.5)]

インメモリデータベースには欠点があります。例外を処理せずにプログラムがクラッシュすると、データベースを失ってしまいます。try文でコードを囲んで例外を捕捉し、except文でデータベースをファイルに保存することにより、そのリスクを緩和できます。第4章でtry文とexcept文による例外処理を説明しました。

データベースを複製する

Connectionオブジェクトについてiterdump()メソッドを呼び出すと、データベースの複製を取得できます。このメソッドは、データベースを再作成するのに必要なSQLiteのクエリのテキストを生成するイテレータを返します。イテレーターをforループで使うかlist()関数に渡して文字列のリストに変換します。例えば、sweigartcats.dbデータベースを再作成するのに必要なSQLiteのクエリを取得するには、対話型シェルで以下の内容を入力します。

>>> import sqlite3
>>> conn = sqlite3.connect('sweigartcats.db', isolation_level=None)
>>> with open('sweigartcats-queries.txt', 'w', encoding='utf-8') as fileObj:
...     for line in conn.iterdump():
...         fileObj.write(line + '\n')

このコードを実行すると、データベースを再作成する以下のSQLiteクエリが書かれたsweigartcats-queries.txtファイルが作成されます。

BEGIN TRANSACTION;
CREATE TABLE "cats" (name TEXT NOT NULL, birthdate TEXT, fur TEXT, weight_kg REAL) STRICT;
INSERT INTO "cats" VALUES('Zophie','2021-01-24','gray tabby',5.6);
INSERT INTO "cats" VALUES('Miguel','2016-12-24','siamese',6.2);
INSERT INTO "cats" VALUES('Jacob','2022-02-20','orange and white',5.5);
--snip--
INSERT INTO "cats" VALUES('Spunky','2015-09-04','gray',5.9);
INSERT INTO "cats" VALUES('Shadow','2021-01-18','calico',6.0);
COMMIT;

このクエリのテキストは、まず間違いなく、元のデータベースよりもサイズが大きくなります。他方で、人間にとって読みやすく、PythonのコードやSQLiteアプリ（次に説明します）で複製する前に、簡単に編集できるという利点があります。

SQLiteアプリ

データベースとは本質的には関係のないPythonのコードを書かずに、SQLiteデータベースを直接調べたいことがあるかもしれません。そのためには、sqlite3コマンドをインストールして、ターミナルコマンドラインウィンドウから実行します。https://sqlite.org/cli.htmlにドキュメントがあります。

Windowsでは、https://sqlite.org/download.htmlから“A bundle of command line tools for managing SQLite database files”と書かれたファイルをダウンロードして、システムのPATHが通っているフォルダにsqlite3.exeプログラムを配置します。（PATH環境変数とターミナルウィンドウについては第12章を参照してください。）macOSにはsqlite3コマンドが最初からインストールされています。Ubuntu Linuxでは、sudo apt install sqlite3でインストールしてください。

次に、ターミナルウィンドウで、sqlite3 example.dbを実行してexample.dbのデータベースに接続します。このファイルが存在しなければ、sqlite3は空のデータベースでこのファイルを作成します。それから、SQLクエリを入力します。Pythonのconn.execute()に渡したクエリとは異なり、末尾にセミコロンをつけなければなりません。

例えば、ターミナルウィンドウで以下の内容を入力してください。

C:\Users\Al>sqlite3 example.db
SQLite version 3.xx.xx
Enter ".help" for usage hints.
sqlite> CREATE TABLE IF NOT EXISTS cats (name TEXT NOT NULL,
birthdate TEXT, fur TEXT, weight_kg REAL) STRICT;
sqlite> INSERT INTO cats VALUES ('Zophie', '2021-01-24', 'gray tabby', 4.7);
sqlite> SELECT * from cats;
Zophie|2021-01-24|gray tabby|4.7

この例からわかるように、sqlite3コマンドラインツールは一種のSQLite対話型シェルを提供し、そのsqlite>プロンプトでクエリを入力します。.helpコマンドで、.tables（データベースのテーブルを表示する）や.headers（カラムヘッダーのオン／オフを切り替える）などの、さらなるコマンドが表示されます。

sqlite> .tables
cats
sqlite> .headers on
sqlite> SELECT * from cats;
name|birthdate|fur|weight_kg
Zophie|2021-01-24|gray tabby|4.7

コマンドラインツールが不親切だと思われるなら、SQLiteデータベースをGUIで表示するフリーでオープンソースのアプリもあります。Windows、macOS、Linuxで使えます。

• DB Browser for SQLite (https://sqlitebrowser.org)
• SQLite Studio (https://sqlitestudio.pl)
• DBeaver Community (https://dbeaver.io)

これらのGUIアプリはSQLiteデータベースを扱いやすくしてくれますが、テキストベースのSQLiteクエリの構文を学ぶことには意義があります。

まとめ

コンピュータでは大量のデータを扱えますが、データをテキストファイルやスプレッドシートに保存すると、構造化が不十分です。SQLiteなどのSQLデータベースを活用すると、大量の情報を効率的に保存できるだけでなく、SQL言語で必要なデータを正確に取得できます。

SQLiteは素晴らしいデータベースで、sqlite3モジュールがPythonの標準ライブラリに同梱されています。SQLiteのSQLには他のリレーショナルデータベースで用いられるSQLと異なる点が多少あるもののほぼ同じなので、SQLiteの学習はデータベース一般へのいい導入になります。

SQLiteデータベースは専用のサーバーが不要で、一つのファイルに保存されます。複数のテーブルが含まれ（テーブルはスプレッドシートになぞらえられます）、各テーブルには複数のカラムがあります。INSERT、SELECT、UPDATE、DELETEのクエリでCRUD（Create、Read、Update、Delete）操作を行い、テーブルの値を編集します。ALTER TABLEとDROP TABLEのクエリでテーブルやカラム自体を変更できます。外部キーという仕組みで複数テーブルのレコードを結合できます。

SQLiteやデータベースについては、1つの章で説明できないほどたくさんの事柄があります。SQLデータベース一般についてもっと知りたければ、Anthony DeBarrosのPractical SQL, 2nd edition (No Starch Press, 2022)をおすすめします。

練習問題

  1. example.dbという名前のファイルのSQLデータベースについてConnectionオブジェクトを取得するPythonのコードを書いてください。

  2. studentsという名前のテーブルに、first_name、last_name、favorite_colorという名前のTEXT型のカラムを作成するPythonのコードを書いてください。

  3. 自動コミットモードでSQLiteデータベースに接続する方法を教えてください。

  4. SQLiteでINTEGER型のデータとREAL型のデータはどう違いますか？

  5. 厳格モードを設定するとテーブルはどうなりますか？

  6. 'SELECT * FROM cats'というクエリ中の*はどういう意味ですか？

  7. CRUDは何の略ですか？

  8. ACIDは何の略ですか？

  9. テーブルに新しいレコードを追加するクエリを書いてください。

10. テーブルからレコードを削除するクエリを書いてください。

11. UPDATEクエリでWHERE節を指定しないとどうなりますか？

12. インデックスとは何ですか？　catsという名前のテーブルのbirthdateという名前のカラムにインデックスを作成するコードはどのように書きますか？

13. 外部キーとは何ですか？

14. catsという名前のテーブルを削除するコードを書いてください。

15. インメモリデータベースを作成する際に「ファイル名」をどう指定しますか？

16. データベースを複製する方法を教えてください。

練習プログラム

以下の練習プログラムを書いてください。

CREATE TABLE IF NOT EXISTS meals (name TEXT) STRICT
CREATE TABLE IF NOT EXISTS ingredients (name TEXT,
meal_id INTEGER, FOREIGN KEY(meal_id) REFERENCES meals
(rowid)) STRICT

> onigiri:rice,nori,salt,sesame seeds
Meal added: onigiri
> chicken and rice:chicken,rice,cream of chicken soup
Meal added: chicken and rice
> onigiri
Ingredients of onigiri:
  rice
  nori
  salt
  sesame seeds
> chicken
Meals that use chicken:
  chicken and rice
> rice
Meals that use rice:
  onigiri
chicken and rice
> quit