การเชื่อมโยงผ่านตารางความสัมพันธ์ใน 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

คำสั่งค้นหา

ลองมาสร้างคำสั่งค้นหาที่จะดึงข้อมูลผู้ใช้ พร้อมกับเมืองของพวกเขาออกมากัน เพื่อการนี้เราจำเป็นต้องทำการเชื่อม (join) สองครั้ง: การเชื่อมครั้งแรกจะเชื่อมตารางความสัมพันธ์เข้ากับผู้ใช้ และการเชื่อมครั้งที่สองจะเชื่อมเมืองตามความสัมพันธ์:

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>