Łączenie przez tabelę łączenia w Pythonie

Załóżmy teraz, że użytkownik był w różnych miastach. W tym przypadku tabela z użytkownikami mogłaby mieć następujący wygląd:

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

Oczywiste jest, że przechowywanie danych w ten sposób jest nieprawidłowe - miasta należy przenieść do oddzielnej tabeli. Oto ona:

cities
id	name
1	city1
2	city2
3	city3

Jednakże, musimy sprawić, aby każdy użytkownik mógł odnosić się do kilku miast. Za pomocą dwóch tabel jest to niemożliwe.

Będziemy potrzebować wprowadzić tak zwaną tabelę łączenia, która będzie łączyć użytkownika z jego miastami.

W każdym wpisie tej tabeli będzie przechowywane połączenie między użytkownikiem a jednym miastem. Przy tym dla jednego użytkownika w tej tabeli będzie tyle wpisów, w ilu miastach był.

Oto nasza tabela łączenia:

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

Tabela z użytkownikami będzie przechowywać tylko imiona użytkowników, bez połączeń:

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

Zapytania

Zróbmy zapytanie, za pomocą którego pobierzemy użytkowników razem z ich miastami. Do tego będziemy potrzebować wykonać dwa złączenia: pierwsze złączenie przyłączy do użytkowników tabelę łączenia, a drugie złączenie po połączeniach przyłączy miasta:

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

Wynik zapytania

Wynik naszego zapytania w Pythonie będzie zawierał imię każdego użytkownika tyle razy, z iloma miastami jest powiązany:

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

Wygodniej byłoby przekonwertować taki słownik i przekształcić go w następujący:

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

Napiszmy kod wykonujący taką konwersję. Stwórzmy pusty słownik user_cities_dct, do którego będziemy stopniowo wprowadzać dane o użytkownikach i miastach, w których byli. W pętli for deklarujemy dwie zmienne user_name i city_name, w których będą przechowywane imię użytkownika i nazwa miasta. Następnie opisujemy warunek - jeśli użytkownika nie ma w user_cities_dct, to dodaje się on do tego słownika jako klucz. Również wskażemy warunek, że jeśli city_name nie jest None, to doda się jako wartość klucza:

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)

Zadania praktyczne

Załóżmy, że produkt może należeć do kilku kategorii. Opisz strukturę przechowywania.

Napisz zapytanie, które pobierze produkty razem z ich kategoriami.

Wyświetl otrzymane dane w postaci listy ul tak, aby w każdej li na początku stała nazwa produktu, a po dwukropku przez przecinek wymienione były kategorie tego produktu. Mniej więcej tak:

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