Liên kết thông qua bảng liên kết trong Python

Bây giờ giả sử một người dùng đã ở nhiều thành phố khác nhau. Trong trường hợp này, bảng người dùng có thể có dạng như sau:

users
id	name	city
1	user1	city1, city2, city3
2	user2	city1, city2
3	user3	city2, city3
4	user4	city1

Rõ ràng là lưu trữ dữ liệu như vậy là không đúng - các thành phố cần được đưa vào một bảng riêng. Đây là bảng đó:

cities
id	name
1	city1
2	city2
3	city3

Tuy nhiên, chúng ta cần đảm bảo rằng mỗi người dùng có thể tham chiếu đến nhiều thành phố. Chỉ với hai bảng thì không thể làm được điều này.

Chúng ta cần sử dụng cái gọi là bảng liên kết, bảng này sẽ liên kết người dùng với các thành phố của họ.

Mỗi bản ghi trong bảng này sẽ lưu trữ một liên kết giữa một người dùng và một thành phố. Đồng thời, đối với một người dùng, trong bảng này sẽ có bao nhiêu bản ghi tương ứng với số thành phố mà người đó đã ở.

Đây là bảng liên kết của chúng ta:

users_cities
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

Bảng người dùng sẽ chỉ lưu trữ tên người dùng, không có các liên kết:

users
id	name
1	user1
2	user2
3	user3
4	user4
5	user5

Truy vấn

Hãy tạo một truy vấn để lấy thông tin người dùng cùng với các thành phố của họ. Để làm điều này, chúng ta cần thực hiện hai phép nối (join): phép nối đầu tiên sẽ kết hợp bảng người dùng với bảng liên kết, và phép nối thứ hai sẽ kết hợp các thành phố thông qua các liên kết:

SELECT
	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

Kết quả truy vấn

Kết quả truy vấn của chúng ta trong Python sẽ chứa tên của mỗi người dùng nhiều lần, tương ứng với số thành phố mà họ liên kết:

{'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}

Sẽ thuận tiện hơn nếu chuyển đổi từ điển như vậy và biến nó thành từ điển sau:

{
	'user1': ['city1', 'city2', 'city3'],
	'user2': ['city1', 'city2'],
	'user3': ['city2', 'city3'],
	'user4': ['city1'],
	'user5': []
}

Hãy viết code thực hiện việc chuyển đổi như vậy. Tạo một từ điển trống user_cities_dct, vào đó chúng ta sẽ dần dần nhập dữ liệu về người dùng và các thành phố mà họ đã đến. Trong vòng lặp for, khai báo hai biến user_name và city_name, trong đó sẽ lưu trữ tên người dùng và tên thành phố. Sau đó, viết điều kiện - nếu người dùng không có trong user_cities_dct, thì họ sẽ được thêm vào từ điển này dưới dạng khóa. Đồng thời, chỉ định điều kiện rằng nếu city_name không phải là None, thì nó sẽ được thêm làm giá trị của khóa:

result = 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)

Bài tập thực hành

Giả sử một sản phẩm có thể thuộc về nhiều danh mục. Hãy mô tả cấu trúc lưu trữ.

Viết truy vấn lấy các sản phẩm cùng với danh mục của chúng.

Hiển thị dữ liệu thu được dưới dạng danh sách ul sao cho trong mỗi li đầu tiên là tên sản phẩm, sau đó là dấu hai chấm và liệt kê các danh mục của sản phẩm đó, phân cách bằng dấu phẩy. Ví dụ như sau:

<ul>
	<li>product1: category1, category2, category3</li>
	<li>product2: category1, category3</li>
	<li>product3: category1</li>
</ul>