Propojení přes spojovací tabulku v Pythonu

Předpokládejme nyní, že uživatel byl v různých městech. V tomto případě by tabulka s uživateli mohla mít následující podobu:

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

Je zřejmé, že takto ukládat data není správně - města je třeba vyčlenit do samostatné tabulky. Zde je:

cities
id	name
1	city1
2	city2
3	city3

Avšak potřebujeme zajistit, aby každý uživatel mohl odkazovat na několik měst. S použitím dvou tabulek to není možné.

Budeme potřebovat zavést tzv. spojovací tabulku, která bude propojovat uživatele s jeho městy.

V každém záznamu této tabulky bude uloženo propojení mezi uživatelem a jedním městem. Při tom pro jednoho uživatele bude v této tabulce tolik záznamů, v kolika městech byl.

Zde je naše spojovací tabulka:

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

Tabulka s uživateli bude uchovávat pouze jména uživatelů, bez propojení:

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

Dotazy

Pojďme vytvořit dotaz, pomocí kterého získáme uživatele spolu s jejich městy. K tomu budeme potřebovat provést dva spojení (JOIN): první JOIN připojí k uživatelům tabulku spojení, a druhý JOIN přes spojení připojí města:

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

Výsledek dotazu

Výsledek našeho dotazu v Pythonu bude obsahovat jméno každého uživatele tolikrát, se kolika městy je propojen:

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

Pohodlnější by bylo takový slovník překonvertovat a přeměnit jej na následující:

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

Napišme kód, který provádí takovou konverzi. Vytvoříme prázdný slovník user_cities_dct, do kterého budeme postupně vkládat data o uživatelích a městech, která navštívili. Ve smyčce for deklarujeme dvě proměnné user_name a city_name, v kterých budou uloženy jméno uživatele a název města. Dále napíšeme podmínku - pokud uživatel není v user_cities_dct, pak se přidá do tohoto slovníku jako klíč. Také uvedeme podmínku, že pokud city_name není None, pak se přidá jako hodnota klíče:

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)

Praktické úlohy

Předpokládejme, že produkt může náležet do několika kategorií. Popište strukturu uložení.

Napište dotaz, který získá produkty spolu s jejich kategoriemi.

Vypište získaná data ve formě seznamu ul tak, aby v každé li na začátku stálo jméno produktu, a za dvojtečkou přes čárku byly vypsány kategorie tohoto produktu. Přibližně takto:

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