Liaison via table de liaison en Python

Supposons maintenant qu'un utilisateur ait été dans différentes villes. Dans ce cas, la table des utilisateurs pourrait avoir l'aspect suivant :

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

Il est clair que stocker les données ainsi est incorrect - les villes doivent être extraites dans une table séparée. La voici :

cities
id	name
1	city1
2	city2
3	city3

Cependant, nous devons faire en sorte que chaque utilisateur puisse référencer plusieurs villes. Avec deux tables, cela est impossible.

Nous aurons besoin d'introduire ce qu'on appelle une table de liaison, qui reliera l'utilisateur à ses villes.

Dans chaque enregistrement de cette table sera stockée une liaison entre un utilisateur et une ville. De plus, pour un utilisateur, il y aura dans cette table autant d'enregistrements que de villes où il est allé.

Voici notre table de liaison :

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

La table des utilisateurs ne stockera que les noms des utilisateurs, sans les liaisons :

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

Requêtes

Faisons une requête qui récupérera les utilisateurs avec leurs villes. Pour cela, nous aurons besoin de faire deux jointures : la première jointure ajoutera à la table des utilisateurs la table de liaison, et la deuxième jointure ajoutera via les liaisons les villes :

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

Résultat de la requête

Le résultat de notre requête en Python contiendra le nom de chaque utilisateur autant de fois qu'il est lié à des villes :

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

Il serait plus pratique de reconvertir un tel dictionnaire et de le transformer en celui-ci :

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

Écrivons le code effectuant une telle conversion. Créons un dictionnaire vide user_cities_dct, dans lequel nous entrerons progressivement les données sur les utilisateurs et les villes qu'ils ont visitées. Dans la boucle for, nous déclarons deux variables user_name et city_name, dans lesquelles seront stockés le nom de l'utilisateur et le nom de la ville. Ensuite, nous spécifions une condition - si l'utilisateur n'est pas dans user_cities_dct, alors il sera ajouté à ce dictionnaire en tant que clé. Nous indiquerons également la condition que si city_name n'est pas None, alors il sera ajouté comme valeur de la clé :

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)

Tâches pratiques

Supposons qu'un produit puisse appartenir à plusieurs catégories. Décrivez la structure de stockage.

Écrivez une requête qui récupérera les produits avec leurs catégories.

Affichez les données obtenues sous forme de liste ul de telle sorte que dans chaque li se trouve d'abord le nom du produit, et après les deux points, soient énumérées séparées par des virgules les catégories de ce produit. Par exemple :

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