Forbindelse gennem forbindelsestabel i Python

Lad os nu sige, at brugeren har været i forskellige byer. I dette tilfælde kunne tabellen med brugere se sådan ud:

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

Det er klart, at det er forkert at lagre data på denne måde - byer skal flyttes til en separat tabel. Her er den:

cities
id	name
1	city1
2	city2
3	city3

Men vi skal gøre det sådan, at hver bruger kan henvise til flere byer. Dette er umuligt at gøre med kun to tabeller.

Vi bliver nødt til at introducere en såkaldt forbindelsestabel, som vil forbinde brugeren med hans byer.

I hver post i denne tabel vil der være gemt en forbindelse mellem en bruger og en by. Ved det for en enkelt bruger vil der i denne tabel være lige så mange poster, som byer han har været i.

Her er vores forbindelsestabel:

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

Tabellen med brugere vil kun gemme brugeres navne, uden forbindelser:

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

Forespørgsler

Lad os lave en forespørgsel, som henter brugere sammen med deres byer. For at gøre dette har vi brug for at lave to joins: det første join vil tilføje forbindelsestabellen til brugerne, og det andet join vil tilføje byer via forbindelserne:

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

Forespørgselsresultat

Resultatet af vores forespørgsel i Python vil indeholde hver brugers navn lige så mange gange, som byer han er forbundet med:

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

Det ville være mere bekvemt at konvertere en sådan ordbog og omdanne den til følgende:

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

Lad os skrive kode, der udfører en sådan konvertering. Lav en tom ordbog user_cities_dct, som vi gradvist vil indsætte data i om brugere og byer, de har besøgt. I løkken for deklarerer vi to variable user_name og city_name, hvor brugerens navn og byens navn vil blive gemt. Derefter skriver vi en betingelse - hvis brugeren ikke findes i user_cities_dct, så vil han blive tilføjet til denne ordbog som en nøgle. Vi angiver også betingelsen, at hvis city_name ikke er None, så vil den blive tilføjet som værdi for nøglen:

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)

Praktiske opgaver

Lad os sige, at et produkt kan tilhøre flere kategorier. Beskriv lagringsstrukturen.

Skriv en forespørgsel, der henter produkter sammen med deres kategorier.

Vis de modtagne data som en liste ul, således at i hver li står produktets navn først, og efter kolon opremses produktets kategorier adskilt af komma. Cirka sådan her:

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