Vinculando através de uma tabela de junção em Python

Suponha que um usuário tenha estado em cidades diferentes. Neste caso, a tabela de usuários poderia ter a seguinte aparência:

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

É óbvio que armazenar dados assim está incorreto - as cidades precisam ser movidas para uma tabela separada. Aqui está:

cities
id	name
1	city1
2	city2
3	city3

No entanto, precisamos fazer com que cada usuário possa referenciar várias cidades. Isso é impossível de fazer usando apenas duas tabelas.

Precisaremos introduzir uma chamada tabela de junção, que irá vincular o usuário às suas cidades.

Cada registro nesta tabela armazenará uma ligação entre um usuário e uma cidade. Além disso, para um único usuário, haverá quantos registros nesta tabela quantas forem as cidades em que ele esteve.

Aqui está nossa tabela de junção:

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

A tabela de usuários armazenará apenas os nomes dos usuários, sem vínculos:

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

Consultas

Vamos fazer uma consulta que buscará os usuários junto com suas cidades. Para isso, precisaremos fazer dois joins: o primeiro join juntará a tabela de junção aos usuários, e o segundo join juntará as cidades através dos vínculos:

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 da Consulta

O resultado da nossa consulta em Python conterá o nome de cada usuário quantas vezes quantas cidades estiverem vinculadas a ele:

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

Seria mais conveniente converter tal dicionário e transformá-lo no seguinte:

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

Vamos escrever o código que realiza tal conversão. Faremos um dicionário vazio user_cities_dct, no qual iremos gradualmente inserir dados sobre os usuários e as cidades que visitaram. No loop for declaramos duas variáveis user_name e city_name, nas quais serão armazenados o nome do usuário e o nome da cidade. Em seguida, especificamos uma condição - se o usuário não estiver em user_cities_dct, então ele será adicionado a este dicionário como uma chave. Também indicamos a condição de que se city_name não for None, então ele será adicionado como um valor da chave:

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)

Tarefas Práticas

Suponha que um produto possa pertencer a várias categorias. Descreva a estrutura de armazenamento.

Escreva uma consulta que buscará os produtos junto com suas categorias.

Exiba os dados obtidos na forma de uma lista ul de modo que em cada li primeiro apareça o nome do produto, e depois dois pontos, seguidos pela listagem separada por vírgulas das categorias desse produto. Algo assim:

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