Legătura prin tabelă de legătură în Python

Să presupunem că acum utilizatorul a fost în diferite orașe. În acest caz tabela cu utilizatori ar putea avea următoarea formă:

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

Este clar că a stoca datele în acest fel este incorect - orașele trebuie mutate într-o tabelă separată. Iată-o:

cities
id	name
1	city1
2	city2
3	city3

Cu toate acestea, trebuie să facem astfel încât fiecare utilizator să poată face referire la mai multe orașe. Cu două tabele acest lucru este imposibil.

Vom avea nevoie să introducem așa-numita tabelă de legătură, care va lega utilizatorul de orașele sale.

În fiecare înregistrare a acestei tabele va fi stocată legătura dintre un utilizator și un oraș. În același timp, pentru un utilizator în această tabelă vor fi atâtea înregistrări, în câte orașe acesta a fost.

Iată tabela noastră de legătură:

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

Tabela cu utilizatori va stoca doar numele utilizatorilor, fără legături:

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

Interogări

Să facem o interogare cu care vom extrage utilizatorii împreună cu orașele lor. Pentru aceasta vom avea nevoie să facem două join-uri: primul join va atașa la utilizatori tabela de legătură, iar al doilea join prin legături va atașa orașele:

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

Rezultatul interogării

Rezultatul interogării noastre în Python va conține numele fiecărui utilizator de atâtea ori, cu câte orașe este asociat:

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

Ar fi mai convenabil să convertim un astfel de dicționar și să îl transformăm în următorul:

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

Să scriem codul care realizează o astfel de conversie. Vom face un dicționar gol user_cities_dct, în care vom introduce treptat date despre utilizatori și orașele în care au fost. În ciclul for declarăm două variabile user_name și city_name, în care vor fi stocate numele utilizatorului și denumirea orașului. Apoi specificăm condiția - dacă utilizatorul nu se află în user_cities_dct, atunci acesta se va adăuga în acest dicționar ca cheie. De asemenea, vom indica condiția că dacă city_name nu este None, atunci acesta se va adăuga ca valoare a cheii:

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)

Sarcini practice

Să presupunem că un produs poate aparține la mai multe categorii. Descrieți structura de stocare.

Scrieți o interogare care va extrage produsele împreună cu categoriile lor.

Afișați datele obținute sub forma unei liste ul astfel încât în fiecare li să se afle la început numele produsului, iar după două puncte să fie enumerate prin virgulă categoriile acestui produs. Aproximativ astfel:

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