Länkning via kopplingstabell i Python

Låt oss nu anta att en användare har varit i olika städer. I det här fallet skulle tabellen med användare kunna se ut så här:

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

Det är tydligt att det är felaktigt att lagra data på detta sätt - städerna bör flyttas till en separat tabell. Här är den:

cities
id	name
1	city1
2	city2
3	city3

Men vi måste se till att varje användare kan referera till flera städer. Det är omöjligt att göra detta med bara två tabeller.

Vi behöver införa en så kallad kopplingstabell som kommer att länka användaren med hans/hennes städer.

I varje post i denna tabell kommer en koppling mellan en användare och en stad att lagras. Samtidigt kommer det för en användare i denna tabell att finnas lika många poster som antalet städer han/hon har varit i.

Här är vår kopplingstabell:

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 användare kommer endast att lagra användarnamn, utan kopplingar:

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

Frågor

Låt oss skapa en fråga som hämtar användare tillsammans med deras städer. För detta behöver vi göra två kopplingar (joins): den första kopplingen kommer att ansluta kopplingstabellen till användarna, och den andra kopplingen kommer via kopplingarna att ansluta städerna:

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

Frågeresultat

Resultatet av vår fråga i Python kommer att innehålla varje användares namn lika många gånger som antalet städer han/hon är kopplad till:

{'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 vore bekvämare att konvertera en sådan ordbok och omvandla den till följande:

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

Låt oss skriva kod som utför en sådan konvertering. Vi skapar en tom ordbok user_cities_dct, i vilken vi gradvis kommer att lägga in data om användare och de städer de har besökt. I en loop for deklarerar vi två variabler user_name och city_name, i vilka användarens namn och stadens namn kommer att lagras. Sedan specificerar vi ett villkor - om användaren inte finns i user_cities_dct, så kommer han/hon att läggas till i denna ordbok som en nyckel. Vi anger också villkoret att om city_name inte är None, så kommer den att läggas till som ett värde för nyckeln:

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)

Praktiska uppgifter

Antag att en produkt kan tillhöra flera kategorier. Beskriv lagringsstrukturen.

Skriv en fråga som hämtar produkter tillsammans med deras kategorier.

Visa erhållna data som en lista med ul så att i varje li står först produktens namn, och efter kolon enumeras produktens kategorier, separerade med kommatecken. Ungefär så här:

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