多対多のDBを作ってみる（Using Databases with Python: Week 4）

引き続き、ミシガン大学がCoursera上で開講しているPython for Everybody Specializationの第4コース、Using Databases with Pythonを受講した記録です。前回のWeek 3では、リレーショナル・データベースの構築とPythonプログラムによる複数テーブルからのデータの抽出を学びました。

Week 4では、多対多の関係を持つデータベースモデルについて学んでいきます。

１．多対多の関係とは

＜テキストの範囲＞
Chapter 15: Using Database and SQLのつづき

これまでに学んだリレーショナル・データベースは、多対１の構造を持つものでした。多対１とは、前週の例で言えば「トラック」テーブルにある様々なレコードから、アルバムIDという外部キーを通じ、「アルバム」テーブルの一つの主キーを参照する関係のことを意味します。

これに対し多対多とは、「講座」と「生徒」のように、ある生徒は複数の講座を履修しているのに対し、ある講座には複数の生徒がいる、という関係をいいます。

これを繋げるためには、コースIDと生徒IDを相互に外部キーとしても完全な対応はできません。そのため、多対１の関係を「講座」「生徒」に持つ1つのテーブルを間に置きます。

これにより、「メンバー」テーブルと「講座」テーブルを多対１に、「メンバー」テーブルと「生徒」テーブルを多対１の関係にすることができます。ここで、メンバーテーブルは一意に定まる主キーを持たないことに注意してください。生徒ID、コースIDの両方を主キーとみなします。これは、「メンバー」テーブルは重複を許すからです。生徒IDは同じ値をいくつも持ってもよく、コースIDも同じ値をいくつも持ってもよいことになります。

このようなデータベースを構築するSQLは、

CREATE TABLE User(
    id      INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    name    TEXT,
    email   TEXT
)
CREATE TABLE Course(
    id      INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
    title   TEXT
)
CREATE TABLE Member(
    user_id   INTEGER,
    course_id INTEGER,
    role      INTEGER,
    PRIMARY KEY(user_id, course_id)
)

となります。一番下のテーブルにあるPRIMARY KEY(user_id, course_id)というのが特殊です。これは、user_idとcourse_idの組み合わせを主キーとみなし、user_id単独では重複を許し、またcourse_id単独でも重複を許します。しかし、生徒と講座の組み合わせが同じものは許さないということです。roleは例えば教授を1、生徒を0とするなど識別するものとします。

これらのテーブルにデータを入力し、3のテーブルから必要な情報を抽出してみましょう。

SELECT User.name, Member.role, Course.title
FROM User JOIN Member JOIN Course
ON Member.user_id = User.id AND Member.course_id = Course.id
ORDER BY Course.title, Member.role, DESC, User.name

これにより、各講座を履修している生徒および担当している教授のリストを表示することができます。

２．Pythonで多対多データベースを作ってみる

さて、前回の講義ではXMLを用いたデータベースの構築を取り扱いましたが、今回はJSON形式のデータを読み込みます。したがって、インポートするライブラリはjsonライブラリとなります。以下のコードの大部分は前回の講義の復習になりますので、変わっているところだけを確認していきましょう。

import json
import sqlite3
conn = sqlite3.connect('rosterdb.sqlite')
cur = conn.cursor()

cur.executescript('''
DROP TABLE IF EXISTS User;
DROP TABLE IF EXISTS Member;
DROP TABLE IF EXISTS Course;
CREATE TABLE User (
   id     INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
   name   TEXT UNIQUE
);
CREATE TABLE Course (
   id     INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
   title  TEXT UNIQUE
);
CREATE TABLE Member (
   user_id     INTEGER,
   course_id   INTEGER,
   role        INTEGER,
   PRIMARY KEY (user_id, course_id)
)
''')

fname = input('Enter file name: ')
if len(fname) < 1:
   fname = 'roster_data.json'

str_data = open(fname).read()
json_data = json.loads(str_data) #...[1]
for entry in json_data:
   name = entry[0]; #...[2]
   title = entry[1]; #...[3]
   print((name, title))

   cur.execute('''INSERT OR IGNORE INTO User (name)
       VALUES ( ? )''', ( name, ) ) #...[4]
   cur.execute('SELECT id FROM User WHERE name = ? ', (name, ))
   user_id = cur.fetchone()[0]

   cur.execute('''INSERT OR IGNORE INTO Course (title)
       VALUES ( ? )''', ( title, ) ) #...[5]
   cur.execute('SELECT id FROM Course WHERE title = ? ', (title, ))
   course_id = cur.fetchone()[0]

   cur.execute('''INSERT OR REPLACE INTO Member
       (user_id, course_id) VALUES ( ?, ? )''',
       ( user_id, course_id ) ) #...[6]
   conn.commit()

[1]で、読み込んだテキストファイルをjson形式の構造に変換します。JSON形式のファイルの取り扱いは、第３コースのWeek 6で取り扱いました。
元のJSONファイルは、

[
 [
   "Suzuki",
   "si110",
   1
 ],
]

のような形式で記載されています。
したがって、[2]では名前を、[3]では講座名をそれぞれname変数とtitle変数に格納しています。
そして、[4]ではUserテーブルに名前を記録し、名前が既にUserテーブルに存在していない場合にuser_idを割り振ります。[5]ではCourseテーブルに講座名を記録し、講座名がCourseテーブルに存在していない場合にcourse_idを割り振ります。

最後に、Memberテーブルにuser_idとcourse_idの組み合わせを主キーとして記録していきます。Memberテーブルは、以下のように講座を登録している生徒のIDと、講座のIDが紐づいているような形となります。

このプログラムはroleを読み込んでおらず、またデータベースにも記録していないため、枠はあるもののデータは保存されていません。そのため、roleのColumnはすべてNULLが入っています。

プログラム自体は前回の講義を少し変化させただけのもので、理解しやすかったと思います。次回はいよいよ第４コースの最終回、ビジュアライゼーションに入っていきます。
それでは、次回のエントリもお楽しみに！