Koppelen via koppeltabel in Python

Stel dat een gebruiker in verschillende steden is geweest. In dit geval zou de tabel met gebruikers er als volgt uit kunnen zien:

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

Het is duidelijk dat het op deze manier opslaan van gegevens onjuist is - steden moeten in een aparte tabel worden geplaatst. Hier is deze:

cities
id	name
1	city1
2	city2
3	city3

Echter, we moeten ervoor zorgen dat elke gebruiker naar meerdere steden kan verwijzen. Met twee tabellen is dit onmogelijk.

We zullen een zogenaamde koppeltabel moeten introduceren, die de gebruiker met zijn steden zal verbinden.

In elke record van deze tabel wordt een verbinding tussen een gebruiker en één stad opgeslagen. Voor één gebruiker zullen er in deze tabel zoveel records zijn als het aantal steden waar hij is geweest.

Hier is onze koppeltabel:

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

De tabel met gebruikers zal alleen gebruikersnamen opslaan, zonder verbindingen:

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

Query's

Laten we een query maken waarmee we gebruikers samen met hun steden kunnen ophalen. Hiervoor hebben we twee joins nodig: de eerste join voegt de koppeltabel aan de gebruikers toe, en de tweede join voegt via de verbindingen de steden toe:

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

Queryresultaat

Het resultaat van onze query in Python zal de naam van elke gebruiker zo vaak bevatten als het aantal steden waarmee hij verbonden is:

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

Het zou handiger zijn om zo'n woordenboek te converteren en om te zetten in het volgende:

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

Laten we code schrijven die een dergelijke conversie uitvoert. We maken een leeg woordenboek user_cities_dct, waarin we geleidelijk gegevens over gebruikers en de steden waar ze zijn geweest zullen invoeren. In de lus for declareren we twee variabelen user_name en city_name, waarin de gebruikersnaam en de stadnaam worden opgeslagen. Vervolgens specificeren we een voorwaarde - als de gebruiker niet in user_cities_dct staat, dan wordt hij aan dit woordenboek toegevoegd als sleutel. We geven ook de voorwaarde aan dat als city_name niet None is, deze als waarde van de sleutel wordt toegevoegd:

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)

Praktische opdrachten

Stel dat een product tot meerdere categorieën kan behoren. Beschrijf de opslagstructuur.

Schrijf een query die de producten samen met hun categorieën ophaalt.

Geef de verkregen gegevens weer als een lijst ul, zodat in elke li eerst de productnaam staat, en na de dubbele punt de categorieën van dit product worden opgesomd, gescheiden door komma's. Ongeveer zo:

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