Verknüpfung über eine Verknüpfungstabelle in Python

Nehmen wir an, der User war in verschiedenen Städten. In diesem Fall könnte die Tabelle mit den Usern folgendermaßen aussehen:

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

Es ist klar, dass es falsch ist, Daten so zu speichern - die Städte müssen in eine separate Tabelle ausgelagert werden. Hier ist sie:

cities
id	name
1	city1
2	city2
3	city3

Allerdings müssen wir es so einrichten, dass jeder User auf mehrere Städte verweisen kann. Mit zwei Tabellen ist das unmöglich.

Wir müssen eine sogenannte Verknüpfungstabelle einführen, die den User mit seinen Städten verknüpft.

In jedem Datensatz dieser Tabelle wird eine Verknüpfung zwischen einem User und einer Stadt gespeichert. Dabei wird für einen User in dieser Tabelle es so viele Datensätze geben, in wie vielen Städten er war.

Hier ist unsere Verknüpfungstabelle:

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

Die Tabelle mit den Usern wird nur die Namen der User speichern, ohne Verknüpfungen:

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

Abfragen

Lassen Sie uns eine Abfrage erstellen, mit der wir die User zusammen mit ihren Städten abrufen. Dafür benötigen wir zwei Joins: der erste Join fügt den Usern die Verknüpfungstabelle hinzu, und der zweite Join fügt über die Verknüpfungen die Städte hinzu:

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

Ergebnis der Abfrage

Das Ergebnis unserer Abfrage in Python wird den Namen jedes Users so oft enthalten, mit wie vielen Städten er verknüpft ist:

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

Es wäre bequemer, ein solches Wörterbuch umzuwandeln und in folgendes zu verwandeln:

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

Schreiben wir einen Code, der eine solche Konvertierung durchführt. Wir erstellen ein leeres Wörterbuch user_cities_dct, in das wir schrittweise Daten über User und Städte, in denen sie waren, eintragen. In der Schleife for deklarieren wir zwei Variablen user_name und city_name, in denen der Name des Users und der Name der Stadt gespeichert werden. Als nächstes geben wir eine Bedingung vor - wenn der User nicht in user_cities_dct vorhanden ist, wird er als Schlüssel zu diesem Wörterbuch hinzugefügt. Wir geben auch die Bedingung an, dass wenn city_name nicht None ist, er als Wert des Schlüssels hinzugefügt wird:

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 Aufgaben

Nehmen wir an, ein Produkt kann mehreren Kategorien angehören. Beschreiben Sie die Speicherstruktur.

Schreiben Sie eine Abfrage, die die Produkte zusammen mit ihren Kategorien abruft.

Geben Sie die erhaltenen Daten in Form einer Liste ul aus, so dass in jedem li zuerst der Name des Produkts steht, und nach dem Doppelpunkt die Kategorien dieses Produkts aufgezählt werden. Ungefähr so:

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