Связывание через таблицу связи в Python
Пусть теперь юзер был в разных городах. В этом случае таблица с юзерами могла бы иметь следующий вид:
| id | name | city |
|---|---|---|
| 1 | user1 | city1, city2, city3 |
| 2 | user2 | city1, city2 |
| 3 | user3 | city2, city3 |
| 4 | user4 | city1 |
Понятно, что так хранить данные неправильно - города нужно вынести в отдельную таблицу. Вот она:
| id | name |
|---|---|
| 1 | city1 |
| 2 | city2 |
| 3 | city3 |
Однако, нам нужно сделать так, чтобы каждый юзер мог ссылаться на несколько городов. С помощью двух таблиц это сделать невозможно.
Нам понадобится ввести так называемую таблицу связи, которая будет связывать юзера с его городами.
В каждой записи этой таблицы будет хранится связь между юзером и одним городом. При этом для одного юзера в этой таблице будет столько записей, в скольких городах он был.
Вот наша таблица связи:
| 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 |
Таблица с юзерами будет хранить только имена юзеров, без связей:
| id | name |
|---|---|
| 1 | user1 |
| 2 | user2 |
| 3 | user3 |
| 4 | user4 |
| 5 | user5 |
Запросы
Давайте сделаем запрос, с помощью которого вытащим юзеров вместе с их городами. Для этого нам понадобится сделать два джоина: первый джоин присоединит к юзерам таблицу связи, а второй джоин по связям присоединит города:
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
Результат запроса
Результат нашего запроса в 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}
Удобнее было бы переконвертировать такой словарь и превратить его в следующий:
{
'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, то он добавится как значение
ключа:
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)
Практические задачи
Пусть товар может принадлежать нескольким категориям. Распишите структуру хранения.
Напишите запрос, который достанет товары вместе с их категориями.
Выведите полученные данные в виде списка
ul так, чтобы в каждой li вначале
стояло имя продукта, а после двоеточия через
запятую перечислялись категории этого продукта.
Примерно так:
<ul>
<li>product1: category1, category2, category3</li>
<li>product2: category1, category3</li>
<li>product3: category1</li>
</ul>