Kapcsolótáblán keresztüli összekapcsolás Pythonban

Tegyük fel, hogy a felhasználó különböző városokban járt. Ebben az esetben a felhasználók táblája a következőképpen nézhet ki:

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

Érthető, hogy így nem megfelelő az adatok tárolása - a városokat ki kell vinni egy külön táblába. Itt van:

cities
id	name
1	city1
2	city2
3	city3

Azonban úgy kell elrendeznünk, hogy minden felhasználó több városra is hivatkozhat. Két táblával ez lehetetlen.

Be kell vezetnünk egy úgynevezett kapcsolótáblát, amely összekapcsolja a felhasználót a városaival.

Ennek a táblának minden bejegyzése egy felhasználó és egy város közötti kapcsolatot tárol. Egy felhasználó esetében ebben a táblában annyi bejegyzés lesz, ahány városban járt.

Itt a kapcsolótáblánk:

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

A felhasználók táblája csak a felhasználók neveit fogja tárolni, kapcsolatok nélkül:

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

Lekérdezések

Készítsünk egy lekérdezést, amellyel lekérjük a felhasználókat városaikkal együtt. Ehhez két összekapcsolásra lesz szükségünk: az első összekapcsolja a felhasználókat a kapcsolótáblával, a második összekapcsolás pedig a kapcsolatokon keresztül csatolja a városokat:

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

A lekérdezés eredménye

A lekérdezésünk eredménye Pythonban mindegyik felhasználó nevét annyiszor fogja tartalmazni, ahány várossal kapcsolatban áll:

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

Kényelmesebb lenne egy ilyen szótárat átalakítani a következővé:

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

Írjunk kódot, amely végrehajtja az ilyen átalakítást. Készítsünk egy üres szótárat user_cities_dct, amelybe fokozatosan bevisszük az adatokat a felhasználókról és a városokról, amelyekben jártak. A for ciklusban deklarálunk két változót user_name és city_name, amelyekben a felhasználó neve és a város neve tárolódik. Ezután írunk egy feltételt - ha a felhasználó nincs a user_cities_dct-ben, akkor ő hozzáadódik ehhez a szótárhoz kulcsként. Megadjuk azt a feltételt is, hogy ha a city_name nem None, akkor az kulcs értékeként adódik hozzá:

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)

Gyakorlati feladatok

Tegyük fel, hogy egy termék több kategóriába is tartozhat. Írja le a tárolási szerkezetet.

Írjon lekérdezést, amely lekéri a termékeket kategóriáikkal együtt.

Jelenítse meg a kapott adatokat egy ul listaként úgy, hogy minden li-ben először a termék neve álljon, majd kettőspont után vesszővel elválasztva sorolja fel a termék kategóriáit. Hozzávetőlegesen így:

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