Collegamento tramite tabella di relazione in Python

Supponiamo ora che un utente sia stato in diverse città. In questo caso la tabella degli utenti potrebbe avere il seguente aspetto:

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

È ovvio che memorizzare i dati in questo modo non sia corretto - le città devono essere spostate in una tabella separata. Eccola:

cities
id	name
1	city1
2	city2
3	city3

Tuttavia, dobbiamo fare in modo che ogni utente possa riferirsi a più città. Con due tabelle questo è impossibile.

Dobbiamo introdurre la cosiddetta tabella di relazione, che collegherà l'utente con le sue città.

In ogni record di questa tabella verrà memorizzata una relazione tra un utente e una città. Inoltre, per un utente in questa tabella ci saranno tanti record quante sono le città in cui è stato.

Ecco la nostra tabella di relazione:

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

La tabella degli utenti conterrà solo i nomi degli utenti, senza relazioni:

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

Query

Facciamo una query che estragga gli utenti insieme alle loro città. Per fare questo avremo bisogno di due join: il primo join collegherà agli utenti la tabella di relazione, e il secondo join tramite le relazioni collegherà le città:

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

Risultato della query

Il risultato della nostra query in Python conterrà il nome di ogni utente tante volte quante sono le città a cui è collegato:

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

Sarebbe più comodo convertire un tale dizionario e trasformarlo nel seguente:

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

Scriviamo il codice che esegue questa conversione. Creiamo un dizionario vuoto user_cities_dct, in cui inseriremo gradualmente i dati sugli utenti e le città che hanno visitato. Nel ciclo for dichiariamo due variabili user_name e city_name, in cui verranno memorizzati il nome dell'utente e il nome della città. Quindi specifichiamo una condizione - se l'utente non è presente in user_cities_dct, allora viene aggiunto a questo dizionario come chiave. Indichiamo anche la condizione che se city_name non è None, allora viene aggiunto come valore della chiave:

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)

Compiti pratici

Supponiamo che un prodotto possa appartenere a diverse categorie. Descrivete la struttura di memorizzazione.

Scrivete una query che estragga i prodotti insieme alle loro categorie.

Visualizzate i dati ottenuti sotto forma di lista ul in modo che in ogni li ci sia prima il nome del prodotto, e dopo i due punti, separate da virgola, siano elencate le categorie di questo prodotto. Circa così:

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