import sqlite3
?
# 建立數(shù)據(jù)庫(kù)連接
conn = sqlite3.connect("example.db")
?
# 查詢分類(lèi)
categories_query = "SELECT * FROM categories;"
categories_cursor = conn.execute(categories_query)
?
for category in categories_cursor.fetchall():
    category_id = category[0]
    category_name = category[1]
?
    # 顯示分類(lèi)名稱
    print(f"Category: {category_name}")
    
    # 查詢?cè)摲诸?lèi)的商品
    items_query = "SELECT id, name FROM items WHERE category_id = ? ORDER BY name;"
    items_cursor = conn.execute(items_query, (category_id,))
    
    for item in items_cursor.fetchall():
        # 顯示商品 ID 和名稱
        print(f"  Item ID: {item[0]}, Item Name: {item[1]}")
?
conn.close()

import sqlite3
?
# 建立數(shù)據(jù)庫(kù)連接
conn = sqlite3.connect("example.db")
?
# 使用 JOIN 查詢分類(lèi)和商品
query = """
    SELECT 
        c.id AS category_id, 
        c.name AS category_name, 
        i.id AS item_id, 
        i.name AS item_name
    FROM categories c
    LEFT JOIN items i ON c.id = i.category_id
    ORDER BY c.name, i.name;
"""
cursor = conn.execute(query)
?
last_category_id = None
?
for row in cursor.fetchall():
    category_id = row[0]
    category_name = row[1]
    item_id = row[2]
    item_name = row[3]
?
    # 如果分類(lèi)發(fā)生變化，渲染分類(lèi)名稱
    if category_id != last_category_id:
        print(f"Category: {category_name}")
?
    # 顯示商品信息（如果存在）
    if item_id is not None:
        print(f"  Item ID: {item_id}, Item Name: {item_name}")
?
    last_category_id = category_id
?
conn.close()

query = """
    SELECT 
        c.id AS category_id, 
        c.name AS category_name, 
        COUNT(i.id) AS item_count
    FROM categories c
    LEFT JOIN items i ON c.id = i.category_id
    GROUP BY c.id, c.name
    ORDER BY c.name;
"""

import sqlite3
?
# 建立數(shù)據(jù)庫(kù)連接
conn = sqlite3.connect("example.db")
?
# 查詢分類(lèi)及商品數(shù)據(jù)
query = """
    SELECT 
        c.id AS category_id, 
        c.name AS category_name, 
        i.id AS item_id, 
        i.name AS item_name
    FROM categories c
    LEFT JOIN items i ON c.id = i.category_id
    ORDER BY c.name, i.name;
"""
cursor = conn.execute(query)
?
categories = {}
category_items = []
?
last_category_id = None
last_category_name = None
?
for row in cursor.fetchall():
    category_id = row[0]
    category_name = row[1]
    item_id = row[2]
    item_name = row[3]
?
    # 在分類(lèi)發(fā)生變化時(shí)，將當(dāng)前分類(lèi)的商品數(shù)據(jù)存儲(chǔ)
    if last_category_id is not None and category_id != last_category_id:
        categories[last_category_name] = category_items
        category_items = []
?
    # 添加商品信息到當(dāng)前分類(lèi)
    if item_id is not None:
        category_items.append({"item_id": item_id, "item_name": item_name})
?
    last_category_id = category_id
    last_category_name = category_name
?
categories[last_category_name] = category_items
?
# 渲染數(shù)據(jù)
for category_name, items in categories.items():
    print(f"Category: {category_name}")
    print(f"{len(items)} items")
?
    for item in items:
        print(f"  Item ID: {item['item_id']}, Item Name: {item['item_name']}")
?
conn.close()