Pythonにおけるリンクテーブルを介した関連付け
次に、ユーザーが複数の都市に訪れたとします。この場合、ユーザーテーブルは次のようになる可能性があります。
| id | name | city |
|---|---|---|
| 1 | user1 | city1, city2, city3 |
| 2 | user2 | city1, city2 |
| 3 | user3 | city2, city3 |
| 4 | user4 | city1 |
このようにデータを保存することは明らかに不適切です。都市は別のテーブルに分割する必要があります。以下がそのテーブルです。
| id | name |
|---|---|
| 1 | city1 |
| 2 | city2 |
| 3 | city3 |
しかし、各ユーザーが複数の都市を参照できるようにする必要があります。2つのテーブルだけではこれは不可能です。
ここで、いわゆるリンクテーブルを導入する必要があります。これはユーザーとその都市を関連付けます。
このテーブルの各レコードは、1人のユーザーと1つの都市の間の関連を保存します。このテーブルでは、1人のユーザーに対して、そのユーザーが訪れた都市の数だけレコードが存在することになります。
以下がリンクテーブルです。
| id | user_id | city_id |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 3 |
| 4 | 2 | 1 |
| 5 | 2 | 2 |
| 6 | 3 | 2 |
| 7 | 3 | 3 |
| 8 | 4 | 1 |
ユーザーテーブルは、関連なしでユーザー名のみを保存します。
| id | name |
|---|---|
| 1 | user1 |
| 2 | user2 |
| 3 | user3 |
| 4 | user4 |
| 5 | user5 |
クエリ
ユーザーとその都市を一緒に取得するクエリを作成してみましょう。そのためには、2つのJOINが必要です。最初のJOINでユーザーテーブルにリンクテーブルを結合し、2番目のJOINでリンクを介して都市テーブルを結合します。
sqlSELECT
users.name as user_name, cities.name as city_name
FROM
users
LEFT JOIN users_cities ON users_cities.user_id=users.id
LEFT JOIN cities ON users_cities.city_id=cities.id
クエリの結果
Pythonでのこのクエリの結果は、各ユーザーの名前を、そのユーザーが関連付けられている都市の数だけ含むことになります。
python{'user_name': 'user1', 'city_name': 'city1'}
{'user_name': 'user1', 'city_name': 'city2'}
{'user_name': 'user1', 'city_name': 'city3'}
{'user_name': 'user2', 'city_name': 'city1'}
{'user_name': 'user2', 'city_name': 'city2'}
{'user_name': 'user3', 'city_name': 'city2'}
{'user_name': 'user3', 'city_name': 'city3'}
{'user_name': 'user4', 'city_name': 'city1'}
{'user_name': 'user5', 'city_name': None}
このような辞書を変換して、次の形式にすると便利です。
python{
'user1': ['city1', 'city2', 'city3'],
'user2': ['city1', 'city2'],
'user3': ['city2', 'city3'],
'user4': ['city1'],
'user5': []
}
このような変換を実行するコードを書いてみましょう。ユーザーとその訪問した都市に関するデータを徐々に入力していくための空の辞書user_cities_dctを作成します。forループの中で、ユーザー名と都市名を格納する2つの変数user_nameとcity_nameを宣言します。次に条件を指定します。もしユーザーがuser_cities_dctに存在しない場合、そのユーザーを辞書のキーとして追加します。また、city_nameがNoneでない場合、それをキーの値として追加する条件も指定します。
pythonresult = cursor.fetchall()
user_cities_dct = {}
for row in result:
user_name = row['user_name']
city_name = row['city_name']
if user_name not in user_cities_dct:
user_cities_dct[user_name] = []
if city_name is not None:
user_cities_dct[user_name].append(city_name)
print(user_cities_dct)
実践的な課題
商品が複数のカテゴリーに属することができるとします。保存構造を説明してください。
商品とそのカテゴリーを一緒に取得するクエリを書いてください。
取得したデータをulリストとして出力してください。各liに、最初に商品名、その後コロンとカンマでその商品のカテゴリーを列挙します。例は以下の通りです。
html<ul>
<li>product1: category1, category2, category3</li>
<li>product2: category1, category3</li>
<li>product3: category1</li>
</ul>