Σύνδεση μέσω πίνακα σύνδεσης στην Python

Ας υποθέσουμε τώρα ότι ο χρήστης έχει βρεθεί σε διαφορετικές πόλεις. Σε αυτή την περίπτωση ο πίνακας με τους χρήστες θα μπορούσε να έχει την ακόλουθη μορφή:

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

Είναι σαφές ότι η αποθήκευση δεδομένων με αυτόν τον τρόπο είναι λανθασμένη - οι πόλεις πρέπει να μεταφερθούν σε ξεχωριστό πίνακα. Εδώ είναι:

cities
id	name
1	city1
2	city2
3	city3

Ωστόσο, πρέπει να διασφαλίσουμε ότι κάθε χρήστης μπορεί να αναφέρεται σε πολλές πόλεις. Με δύο πίνακες αυτό είναι αδύνατο να γίνει.

Θα χρειαστεί να εισαγάγουμε τον λεγόμενο πίνακα σύνδεσης, ο οποίος θα συνδέει τον χρήστη με τις πόλεις του.

Σε κάθε εγγραφή αυτού του πίνακα θα αποθηκεύεται μια σύνδεση μεταξύ ενός χρήστη και μιας πόλης. Εν αυτή την περίπτωση, για έναν χρήστη σε αυτόν τον πίνακα θα υπάρχουν τόσες εγγραφές, όσες πόλεις έχει επισκεφτεί.

Εδώ είναι ο πίνακας σύνδεσής μας:

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

Ο πίνακας με τους χρήστες θα αποθηκεύει μόνο τα ονόματα των χρηστών, χωρίς συνδέσεις:

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

Ερωτήματα

Ας φτιάξουμε ένα ερώτημα με το οποίο θα ανακτήσουμε τους χρήστες μαζί με τις πόλεις τους. Για αυτό θα χρειαστούμε να κάνουμε δύο συνδέσεις (joins): η πρώτη σύνδεση θα ενώσει στους χρήστες τον πίνακα σύνδεσης, και η δεύτερη σύνδεση μέσω των συνδέσεων θα ενώσει τις πόλεις:

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

Αποτέλεσμα ερωτήματος

Το αποτέλεσμα του ερωτήματός μας στην Python θα περιέχει το όνομα κάθε χρήστη τόσες φορές, με πόσες πόλεις είναι συνδεδεμένος:

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

Θα ήταν πιο βολικό να μετατραπεί ένας τέτοιος λεξικός και να μετασχηματιστεί στον ακόλουθο:

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

Ας γράψουμε κώδικα που εκτελεί μια τέτοια μετατροπή. Θα φτιάξουμε έναν κενό λεξικό user_cities_dct, στον οποίο θα εισάγουμε σταδιακά δεδομένα για χρήστες και πόλεις που έχουν επισκεφτεί. Σε βρόχο for δηλώνουμε δύο μεταβλητές user_name και city_name, στις οποίες θα αποθηκεύονται το όνομα του χρήστη και το όνομα της πόλης. Στη συνέχεια ορίζουμε μια συνθήκη - εάν ο χρήστης δεν υπάρχει στο user_cities_dct, τότε προστίθεται σε αυτόν τον λεξικό ως κλειδί. Επίσης ορίζουμε τη συνθήκη ότι εάν το city_name δεν είναι None, τότε θα προστεθεί ως τιμή του κλειδιού:

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)

Πρακτικές ασκήσεις

Ας υποθέσουμε ότι ένα προϊόν μπορεί να ανήκει σε πολλές κατηγορίες. Περιγράψτε τη δομή αποθήκευσης.

Γράψτε ένα ερώτημα που θα ανακτά τα προϊόντα μαζί με τις κατηγορίες τους.

Εμφανίστε τα ληφθέντα δεδομένα ως μια λίστα ul έτσι, ώστε σε κάθε li να βρίσκεται πρώτα το όνομα του προϊόντος, και μετά άνω και κάτω τελεία να αναγράφονται διαχωρισμένες με κόμμα οι κατηγορίες αυτού του προϊόντος. Περίπου έτσι:

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