Python에서 연결 테이블을 통한 연결

이제 사용자가 여러 도시에 있었다고 가정해 보겠습니다. 이 경우 사용자 테이블은 다음과 같이 보일 수 있습니다:

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

이런 식으로 데이터를 저장하는 것은 분명히 잘못된 방법입니다. 도시는 별도의 테이블로 분리해야 합니다. 다음은 그 테이블입니다:

cities
id	name
1	city1
2	city2
3	city3

그러나 각 사용자가 여러 도시를 참조할 수 있도록 해야 합니다. 두 개의 테이블로는 이것이 불가능합니다.

사용자와 그의 도시를 연결하는 소위 연결 테이블을 도입해야 합니다.

이 테이블의 각 레코드는 사용자와 하나의 도시 간의 연결을 저장합니다. 동시에, 하나의 사용자에 대해 이 테이블에는 그가 방문한 도시 수만큼 레코드가 있습니다.

다음은 우리의 연결 테이블입니다:

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

사용자 테이블은 연결 없이 사용자 이름만 저장합니다:

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

쿼리

사용자를 그들의 도시와 함께 가져오는 쿼리를 만들어 봅시다. 이를 위해 두 개의 조인이 필요합니다: 첫 번째 조인은 사용자에게 연결 테이블을 연결하고, 두 번째 조인은 연결을 통해 도시를 연결합니다:

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

쿼리 결과

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}

이런 사전을 변환하여 다음과 같이 만드는 것이 더 편리할 것입니다:

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

이러한 변환을 수행하는 코드를 작성해 봅시다. 사용자와 그들이 방문한 도시에 대한 데이터를 점진적으로 입력할 빈 사전 user_cities_dct를 만듭니다. for 루프에서 사용자 이름과 도시 이름을 저장할 두 변수 user_name과 city_name를 선언합니다. 그런 다음 조건을 작성합니다 - 만약 사용자가 user_cities_dct에 없으면, 그 사용자는 이 사전에 키로 추가됩니다. 또한 city_name이 None이 아니면 키의 값으로 추가된다는 조건을 지정합니다:

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)

실습 문제

제품이 여러 카테고리에 속할 수 있다고 가정합니다. 저장 구조를 설명하세요.

제품을 그들의 카테고리와 함께 가져오는 쿼리를 작성하세요.

얻은 데이터를 ul 목록 형태로 출력하되, 각 li에 먼저 제품 이름이 오고 콜론 뒤에 해당 제품의 카테고리가 쉼표로 구분되어 나열되도록 하세요. 대략 다음과 같이:

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