Свързване чрез таблица за свързване в 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

Заявки

Нека направим заявка, с която да извлечем потребителите заедно с техните градове. За това ще ни трябват две свързвания (join): първото свързване ще присъедини към потребителите таблицата за свързване, а второто свързване по връзките ще присъедини градовете:

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>