Vinculación a través de tabla de enlace en Python

Supongamos ahora que el usuario ha estado en diferentes ciudades. En este caso, la tabla de usuarios podría tener el siguiente aspecto:

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

Está claro que almacenar datos así es incorrecto: las ciudades deben llevarse a una tabla separada. Aquí está:

cities
id	name
1	city1
2	city2
3	city3

Sin embargo, necesitamos hacer que cada usuario pueda hacer referencia a varias ciudades. Con dos tablas esto es imposible de hacer.

Necesitaremos introducir la llamada tabla de enlace, que vinculará al usuario con sus ciudades.

En cada registro de esta tabla se almacenará un vínculo entre un usuario y una ciudad. Para un usuario, habrá en esta tabla tantos registros como ciudades en las que haya estado.

Aquí está nuestra tabla de enlace:

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 tabla de usuarios almacenará solo los nombres de usuario, sin vínculos:

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

Consultas

Hagamos una consulta con la cual obtengamos los usuarios junto con sus ciudades. Para esto necesitaremos hacer dos uniones (joins): la primera unión adjuntará la tabla de enlace a los usuarios, y la segunda unión adjuntará las ciudades a través de los enlaces:

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

Resultado de la consulta

El resultado de nuestra consulta en Python contendrá el nombre de cada usuario tantas veces como ciudades con las que esté relacionado:

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

Sería más conveniente convertir dicho diccionario y transformarlo en el siguiente:

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

Escribamos el código que realice dicha conversión. Hagamos un diccionario vacío user_cities_dct, en el cual introduciremos gradualmente datos sobre los usuarios y las ciudades en las que han estado. En el bucle for declaramos dos variables user_name y city_name, en las cuales se almacenarán el nombre del usuario y el nombre de la ciudad. Luego, especificamos una condición: si el usuario no está en user_cities_dct, entonces se añadirá a este diccionario como clave. También indicaremos la condición de que si city_name no es None, entonces se añadirá como valor de la clave:

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)

Tareas prácticas

Supongamos que un producto puede pertenecer a varias categorías. Describa la estructura de almacenamiento.

Escriba una consulta que obtenga los productos junto con sus categorías.

Muestre los datos obtenidos en forma de lista ul de modo que en cada li primero esté el nombre del producto, y después de dos puntos, se listen separadas por comas las categorías de este producto. Aproximadamente así:

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