Saskarnes veidošana ar saistīšanas tabulu Python

Pieņemsim, ka lietotājs atradās dažādās pilsētās. Šajā gadījumā lietotāju tabulai varētu būt šāda forma:

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

Saprotams, ka datus šādi glabāt nav pareizi - pilsētas ir jāizceļ atsevišķā tabulā. Lūk, tā:

cities
id	name
1	city1
2	city2
3	city3

Tomēr mums ir jāpanāk, lai katrs lietotājs varētu atsaukties uz vairākām pilsētām. Izmantojot divas tabulas, to izdarīt nav iespējams.

Mums būs jāievada tā sauktā saistīšanas tabula, kas savienos lietotāju ar viņa pilsētām.

Katrā šīs tabulas ierakstā tiks glabāta saite starp lietotāju un vienu pilsētu. Vienam lietotājam šajā tabulā būs tik ierakstu, cik pilsētās viņš atradies.

Lūk, mūsu saistīšanas tabula:

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

Lietotāju tabula glabās tikai lietotāju vārdus, bez saitēm:

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

Pieprasījumi

Izveidosim pieprasījumu, ar kura palīdzību iegūsim lietotājus kopā ar viņu pilsētām. Šim nolūkam mums būs nepieciešami divi savienojumi (join): pirmais savienojums pievienos lietotājiem saistīšanas tabulu, un otrais savienojums pēc saitēm pievienos pilsētas:

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

Pieprasījuma rezultāts

Mūsu pieprasījuma rezultāts Python saturēs katra lietotāja vārdu tik reižu, cik pilsētas ar viņu ir saistītas:

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

Ērtāk būtu šādu vārdnīcu pārveidot un pārvērst to sekojošā:

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

Uzrakstīsim kodu, kas veic šādu konvertēšanu. Izveidosim tukšu vārdnīcu user_cities_dct, kuru pakāpeniski aizpildīsim ar datiem par lietotājiem un pilsētām, kurās viņi atradās. Ciklā for deklarējam divus mainīgos user_name un city_name, kuros tiks glabāts lietotāja vārds un pilsētas nosaukums. Tālāk norādām nosacījumu - ja lietotāja nav user_cities_dct, tad viņš tiks pievienots šai vārdnīcai kā atslēga. Norādām arī nosacījumu, ka, ja city_name nav None, tad tas tiks pievienots kā vērtība atslēgai:

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)

Praktiskie uzdevumi

Pieņemsim, ka produkts var piederēt vairākām kategorijām. Izklāstiet glabāšanas struktūru.

Uzrakstiet pieprasījumu, kas iegūs produktus kopā ar to kategorijām.

Izvadiet iegūtos datus kā sarakstu ul tā, lai katrā li sākumā atrastos produkta nosaukums, un pēc kola ar komatu tiktu uzskaitītas šī produkta kategorijas. Apmēram šādi:

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