Linking via Link Table in Python

Now let's say the user was in different cities. In this case, the table with users could look like this:

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

It is clear that it is wrong to store data this way - the cities need to be put into a separate table. Here it is:

cities
id	name
1	city1
2	city2
3	city3

However, we need to make it so that each user can link to multiple cities. This is impossible to do with two tables.

We will need to introduce a so-called link table, which will link the user to his cities.

Each record of this table will store a connection between a user and one city. At the same time, for one user in this table there will be as many records as there are cities he has been to.

Here is our connection table:

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

The table with users will store only user names, without relationships:

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

Requests

Let's make a query that will extract users along with their cities. To do this, we will need to make two joins: the first join will attach the connection table to the users, and the second join by connections will attach the cities:

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

Query result

The result of our Python query will contain each user's name as many times as there are cities with which he is associated:

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

It would be more convenient to convert such a dictionary and turn it into the following:

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

Let's write code that performs such a conversion. Let's create an empty dictionary user_cities_dct, into which we will gradually enter data about users and the cities they have visited. In the for cycle, we declare two variables user_name and city_name, which will store the user name and the city name. Next, we write a condition - if the user is not in user_cities_dct, then he will be added to this dictionary as a key. We will also specify the condition that if city_name is not None, then it will be added as a key value:

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)

Practical tasks

Let the product belong to several categories. Describe the storage structure.

Write a query that will retrieve products along with their categories.

Output the obtained data as a list ul so that in each li the product name comes first, and after a colon, the categories of this product are listed separated by commas. Something like this:

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