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:

sqlSELECT
  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:

python{'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:

python{
  '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:

pythonresult = 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

Задание 1

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

Задание 2

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

Задание 3

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:

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