Связывание через таблицу связи в Python

Пусть теперь юзер был в разных городах. В этом случае таблица с юзерами могла бы иметь следующий вид:

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

Понятно, что так хранить данные неправильно - города нужно вынести в отдельную таблицу. Вот она:

cities
id	name
1	city1
2	city2
3	city3

Однако, нам нужно сделать так, чтобы каждый юзер мог ссылаться на несколько городов. С помощью двух таблиц это сделать невозможно.

Нам понадобится ввести так называемую таблицу связи, которая будет связывать юзера с его городами.

В каждой записи этой таблицы будет хранится связь между юзером и одним городом. При этом для одного юзера в этой таблице будет столько записей, в скольких городах он был.

Вот наша таблица связи:

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

Таблица с юзерами будет хранить только имена юзеров, без связей:

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

Запросы

Давайте сделаем запрос, с помощью которого вытащим юзеров вместе с их городами. Для этого нам понадобится сделать два джоина: первый джоин присоединит к юзерам таблицу связи, а второй джоин по связям присоединит города:

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

Результат запроса

Результат нашего запроса в Python будет содержать имя каждого юзера столько раз, со сколькими городами он связан:

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}

Удобнее было бы переконвертировать такой словарь и превратить его в следующий:

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

Напишем код, выполняющий такую конвертацию. Сделаем пустой словарь user_cities_dct, в который будем постепенно вводить данные о юзерах и городах, в которым они побывали. В цикле for объявляем две переменные user_name и city_name, в которых будут хранится имя юзера и название города. Далее прописываем условие - если юзера нет в user_cities_dct, то он добавится в этот словарь в качестве ключа. Также укажем условие, что если city_name не None, то он добавится как значение ключа:

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)

Практические задачи

Задание 1

Пусть товар может принадлежать нескольким категориям. Распишите структуру хранения.

Задание 2

Напишите запрос, который достанет товары вместе с их категориями.

Задание 3

Выведите полученные данные в виде списка ul так, чтобы в каждой li вначале стояло имя продукта, а после двоеточия через запятую перечислялись категории этого продукта. Примерно так:

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