Kobling via koblingstabell i Python

La oss nå si at brukeren har vært i forskjellige byer. I dette tilfellet kunne tabellen med brukere sett slik ut:

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 feil å lagre data på denne måten - byene må flyttes til en egen tabell. Her er den:

cities
id	name
1	city1
2	city2
3	city3

Imidlertid trenger vi å sørge for at hver bruker kan referere til flere byer. Dette er umulig å gjøre med bare to tabeller.

Vi må innføre en såkalt koblingstabell, som vil koble brukeren til hans byer.

I hver post i denne tabellen vil det lagres en kobling mellom en bruker og en by. Samtidig vil det for en bruker i denne tabellen være like mange poster som antall byer han har vært i.

Her er vår koblingstabell:

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 brukere vil kun lagre brukernavn, uten koblinger:

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

Spørringer

La oss lage en spørring som henter brukere sammen med deres byer. For å gjøre dette trenger vi å utføre to joins: den første joinen vil koble brukere til koblingstabellen, og den andre joinen vil via koblingene koble byene:

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

Resultat av spørringen

Resultatet av spørringen vår i Python vil inneholde navnet til hver bruker like mange ganger som antall byer han er koblet til:

{'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 mer praktisk å konvertere en slik ordbok og gjøre den om til følgende:

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

La oss skrive kode som utfører en slik konvertering. Vi lager en tom ordbok user_cities_dct, som vi gradvis vil fylle med data om brukere og byene de har besøkt. I en for-løkke deklarerer vi to variabler user_name og city_name, der brukerens navn og byens navn vil bli lagret. Deretter spesifiserer vi en betingelse - hvis brukeren ikke finnes i user_cities_dct, vil han blir lagt til i denne ordboken som en nøkkel. Vi vil også spesifisere betingelsen at hvis city_name ikke er None, vil den bli lagt til som en verdi for nøkkelen:

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 oppgaver

La et produkt kunne tilhøre flere kategorier. Beskriv lagringsstrukturen.

Skriv en spørring som henter produkter sammen med deres kategorier.

Vis de innhentede dataene som en liste ul slik at i hver li står produktets navn først, og etter kolon, listes kategoriene til dette produktet opp, atskilt med komma. Omtrent slik:

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