Seostamine läbi seostustabeli Pythonis

Oletame nüüd, et kasutaja on käinud erinevates linnades. Sel juhul võiks kasutajate tabel välja näha järgmine:

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

On arusaadav, et andmete hoidmine sel viisil on vale - linnad tuleks viia eraldi tabelisse. Siin see on:

cities
id	name
1	city1
2	city2
3	city3

Siiski peame me tegema nii, et iga kasutaja saaks viidata mitmele linnale. Kahe tabeliga seda teha ei saa.

Meil on vaja kasutusele võtta nn seostustabel, mis seob kasutaja tema linnadega.

Selle tabeli igas kirjes hoitakse seost kasutaja ja ühe linna vahel. Sel juhul on ühe kasutaja jaoks selles tabelis nii palju kirjeid, kui paljudes linnades ta on käinud.

Siin on meie seostustabel:

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

Kasutajate tabel hoiab ainult kasutajate nimesid, ilma seosteta:

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

Päringud

Teeme päringu, mille abil saame kasutajad koos nende linnadega. Selleks on meil vaja teha kaks ühendust (join): esimene ühendus lisab kasutajatele seostustabeli ja teine ühendus seoste kaudu lisab linnad:

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

Päringu tulemus

Meie päringu tulemus Pythonis sisaldab iga kasutaja nime nii mitu korda, kui paljude linnadega ta on seotud:

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

Mugavam oleks sellist sõnastikku ümber teisendada ja muuta see järgnevaks:

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

Kirjutame koodi, mis teeb sellise teisenduse. Teeme tühja sõnastiku user_cities_dct, millele lisame järk-järgult andmeid kasutajate ja linnade kohta, kus nad on käinud. Tsüklis for deklareerime kaks muutujat user_name ja city_name, milles hoitakse kasutaja nime ja linna nimetust. Seejärel kirjutame tingimuse - kui kasutajat pole user_cities_dct sees, siis ta lisatakse sellesse sõnastikse võtmena. Samuti määrame tingimuse, et kui city_name ei ole None, siis see lisatakse võtme väärtusena:

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)

Praktilised ülesanded

Oletame, et toode võib kuuluda mitmesse kategooriasse. Kirjeldage andmestruktuuri.

Kirjutage päring, mis toob tooted koos nende kategooriatega.

Kuva saadud andmed loendina ul nii, et igas li oleks alguses toote nimi ja seejärel kooloniga eraldatuna loetletud selle toote kategooriad. Umbes nii:

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