Povezivanje kroz tabelu veze u Pythonu

Pretpostavimo sada da je korisnik bio u različitim gradovima. U ovom slučaju tabela sa korisnicima bi mogla da izgleda ovako:

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

Jasno je da je ovakav način čuvanja podataka neispravan - gradove treba izdvojiti u posebnu tabelu. Evo je:

cities
id	name
1	city1
2	city2
3	city3

Međutim, potrebno je da omogućimo da svaki korisnik može da referencira više gradova. To je nemoguće učiniti pomoću samo dve tabele.

Biče nam potrebno da uvedemo takozvanu tabelu veze, koja će da povezuje korisnika sa njegovim gradovima.

U svakom zapisu ove tabele biće sačuvana veza između jednog korisnika i jednog grada. Pri tome za jednog korisnika u ovoj tabeli će biti onoliko zapisa, u koliko je gradova bio.

Evo naše tabele veze:

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 sa korisnicima će da čuva samo imena korisnika, bez veza:

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

Upiti

Hajde da napravimo upit pomoću kojeg ćemo izvući korisnike zajedno sa njihovim gradovima. Za ovo će nam biti potrebno da izvršimo dva spajanja (JOIN): prvo spajanje će pridružiti korisnicima tabelu veze, a drugo spajanje po vezama će pridružiti gradove:

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 upita

Rezultat našeg upita u Pythonu će sadržati ime svakog korisnika onoliko puta, sa koliko gradova 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}

Bilo bi praktičnije takav rečnik konvertovati i pretvoriti ga u sledeći:

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

Napišimo kod koji izvršava takvu konverziju. Napravićemo prazan rečnik user_cities_dct, u koji ćemo postepeno unositi podatke o korisnicima i gradovima u kojima su bili. U petlji for deklarišemo dve promenljive user_name i city_name, u kojima će se čuvati ime korisnika i naziv grada. Zatim napišemo uslov - ako korisnika nema u user_cities_dct, onda će on biti dodat u ovaj rečnik kao ključ. Takođe ćemo navesti uslov, da ako city_name nije None, onda će biti dodat kao 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čni zadaci

Neka proizvod može pripadati više kategorija. Opišite strukturu čuvanja.

Napišite upit koji će dohvatiti proizvode zajedno sa njihovim kategorijama.

Ispišite dobijene podatke u obliku liste ul tako da u svakoj li na početku stoji ime proizvoda, a posle dvotačke zarezom budu nabrojane kategorije tog proizvoda. Otprilike ovako:

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