Yhdistäminen liitostaulun kautta Pythonissa

Oletetaan nyt, että käyttäjä on käynyt eri kaupungeissa. Tässä tapauksessa käyttäjätaululla voisi olla seuraava muoto:

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

On selvää, että tällä tavalla tietojen tallentaminen on väärin - kaupungit on siirrettävä erilliseen taulukkoon. Tässä se on:

cities
id	name
1	city1
2	city2
3	city3

Meidän on kuitenkin tehtävä niin, että jokainen käyttäjä voi viitata useaan kaupunkiin. Kahdella taulukolla tätä on mahdotonta tehdä.

Tarvitsemme niin kutsutun liitostaulun, joka yhdistää käyttäjän hänen kaupunkeihinsa.

Jokaisessa tämän taulun tietueessa tallennetaan yhteys käyttäjän ja yhden kaupungin välillä. Samalla yhdelle käyttäjälle tässä taulussa on niin monta tietuetta, kuin monessa kaupungissa hän on käynyt.

Tässä on liitostaulumme:

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

Käyttäjätaulukko tallentaa vain käyttäjien nimet, ilman yhteyksiä:

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

Kyselyt

Tehdään kysely, jonka avulla saamme käyttäjät yhdessä heidän kaupunkiensa kanssa. Tätä varten tarvitsemme kaksi liitosta: ensimmäinen liitos yhdistää käyttäjiin liitostaulun, ja toinen liitos yhdistää liitosten kautta kaupungit:

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

Kyselyn tulos

Kyselymme tulos Pythonissa sisältää kunkin käyttäjän nimen niin monta kertaa, kuinka moneen kaupunkiin hän on liitetty:

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

Olisi kätevämpää muuntaa tällainen sanakirja ja muuttaa se seuraavaksi:

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

Kirjoitetaan koodi, joka suorittaa tällaisen muunnoksen. Tehdään tyhjä sanakirja user_cities_dct, johon syötämme vähitellen tietoja käyttäjistä ja kaupungeista, joissa he ovat käyneet. Silmukassa for esittelemme kaksi muuttujaa user_name ja city_name, joissa säilytetään käyttäjän nimeä ja kaupungin nimeä. Seuraavaksi määritämme ehdon - jos käyttäjää ei ole sanakirjassa user_cities_dct, niin hän lisätään tähän sanakirjaan avaimeksi. Määritämme myös ehdon, että jos city_name ei ole None, niin se lisätään avaimen arvoksi:

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)

Käytännön tehtävät

Oletetaan, että tuote voi kuulua useisiin kategorioihin. Kuvaile tallennusrakenne.

Kirjoita kysely, joka hakee tuotteet yhdessä niiden kategorioiden kanssa.

Tulosta saadut tiedot ul-listana siten, että jokaisessa li on aluksi tuotteen nimi, ja kaksoispisteen jälkeen pilkuilla eroteltuina luetellaan tuotteen kategoriat. Suunnilleen näin:

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