Povezovanje preko tabele povezav v Pythonu

Recimo, da je bil uporabnik v različnih mestih. V tem primeru bi lahko tabela z uporabniki izgledala takole:

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

Jasno je, da tako shranjevanje podatkov ni pravilno - mesta je treba premakniti v ločeno tabelo. Tukaj je:

cities
id	name
1	city1
2	city2
3	city3

Vendar moramo poskrbeti, da se lahko vsak uporabnik sklicuje na več mest. S pomočjo dveh tabel tega ni mogoče narediti.

Potrebovali bomo tako imenovano tabelo povezav, ki bo povezovala uporabnika z njegovimi mesti.

V vsakem zapisu te tabele bo shranjena povezava med uporabnikom in enim mestom. Pri tem bo za enega uporabnika v tej tabeli toliko zapisov, v koliko mestih je bil.

Tukaj je naša tabela povezav:

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 z uporabniki bo shranjevala samo imena uporabnikov, brez povezav:

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

Poizvedbe

Naredimo poizvedbo, s katero bomo pridobili uporabnike skupaj z njihovimi mesti. Za to bomo potrebovali narediti dve združitvi (join): prva združitev bo k uporabnikom priključila tabelo povezav, druga združitev pa bo po povezavah priključila mesta:

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

Rezultat poizvedbe

Rezultat naše poizvedbe v Pythonu bo vseboval ime vsakega uporabnika tolikokrat, s kolikor mest je povezan:

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

Bolj priročno bi bilo takšen slovar pretvoriti v naslednjega:

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

Napišimo kodo, ki izvede takšno pretvorbo. Naredimo prazen slovar user_cities_dct, v katerega bomo postopoma vnašali podatke o uporabnikih in mestih, v katerih so bili. V zanki for deklariramo dve spremenljivki user_name in city_name, v katerih bosta shranjeni ime uporabnika in naziv mesta. Nato napišemo pogoj - če uporabnika ni v user_cities_dct, se bo dodal v ta slovar kot ključ. Prav tako navedemo pogoj, da če city_name ni None, se bo dodal kot vrednost ključa:

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)

Praktične naloge

Recimo, da izdelek lahko pripada več kategorijam. Opišite strukturo shranjevanja.

Napišite poizvedbo, ki bo pridobila izdelke skupaj z njihovimi kategorijami.

Izpišite pridobljene podatke v obliki seznama ul tako, da v vsaki li sprva stoji ime izdelka, za dvopičjem pa so ločeni z vejico našteti kategorije tega izdelka. Približno takole:

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