""" ============================================================================= 第 6 讲 · 工具模块：一个 Yelp 式的餐厅数据库（结构化字段 + 自由文本的评论） ============================================================================= 对应讲义：lectures/lecture06_structured_data.md 第 0.4 节 ----------------------------------------------------------------------------- 【这个模块做什么？】 ----------------------------------------------------------------------------- 幻灯片里的"餐厅助手"基于 Yelp 的数据：每家餐厅有一些**结构化的字段**（地点、菜系、价格档、 评分、评论数……），也有**自由文本**（评论、招牌菜）。这个模块生成一个虚构的版本，存进 Python 自带的 SQLite 数据库： 表 restaurants：id, name, location, address, phone, opening_hours, cuisines, price, rating, num_reviews, popular_dishes 表 reviews ：id, restaurant_id, author, text 几个刻意的设计： · cuisines 是一个**大的枚举类型**（50 个值，真实的 Yelp 有 200 多个），比如 "coffee & tea"、"cafe"、 "sushi bars"。用户说 "café"、"coffee"、"sushi"，数据库里却没有这些字面值 —— 这是第 3 节的问题。 · price 是一个**小的枚举类型**（cheap / moderate / expensive / luxury），可以整个写进提示词。 · 每家餐厅有几个**隐藏的特征**（适合带孩子、景色好、很辣、浪漫、适合聚会、有户外座位）， 数据库里没有这些字段，只体现在评论的文字里，而且说法各不相同（"brought our kids" / "family-friendly" / "the children loved it"）。它们是第 6 节"混合数据"问题的标准答案。 ----------------------------------------------------------------------------- 【SQL 的写法说明】 ----------------------------------------------------------------------------- 幻灯片用 PostgreSQL 的数组写法：'chinese' = ANY(cuisines)。SQLite 没有数组类型，这里把菜系存成 用 "|" 分隔的一段文字（例如 "chinese|dim sum"），并注册一个自定义函数 has(cuisines, 'chinese')。 所以： 幻灯片：SELECT * FROM restaurants WHERE 'chinese' = ANY(cuisines) 本讲 ：SELECT * FROM restaurants WHERE has(cuisines, 'chinese') ----------------------------------------------------------------------------- 【用法】 ----------------------------------------------------------------------------- from restaurant_world import make_world world = make_world(seed=0) rows = world.query("SELECT name, rating FROM restaurants WHERE has(cuisines, 'chinese') LIMIT 3") """ import random import sqlite3 from dataclasses import dataclass LOCATIONS = ["Palo Alto", "Mountain View", "Menlo Park", "Sunnyvale", "Bernal Heights", "Mission District"] # 50 个菜系（Yelp 的分类风格：有些很细，比如 "sushi bars"、"coffee & tea"）。 # 按"大类"分组，一家餐厅的几个菜系通常来自同一个大类。 CUISINE_GROUPS = { "chinese": ["chinese", "szechuan", "cantonese", "dim sum", "taiwanese", "hot pot", "noodles"], "japanese": ["japanese", "sushi bars", "ramen", "izakaya"], "asian": ["korean", "thai", "vietnamese", "singaporean", "indian"], "latin": ["mexican", "tacos", "brazilian", "peruvian", "latin american"], "european": ["italian", "pizza", "french", "mediterranean", "greek", "modern european", "wine bars"], "american": ["american (traditional)", "american (new)", "burgers", "barbeque", "southern", "soul food", "seafood", "steakhouses", "sandwiches", "cocktail bars"], "healthy": ["vegan", "vegetarian", "salad", "juice bars & smoothies"], "cafe": ["coffee & tea", "cafe", "breakfast & brunch", "bakeries", "desserts", "ice cream & frozen yogurt"], "other": ["middle eastern", "ethiopian"], } CUISINES = [c for group in CUISINE_GROUPS.values() for c in group] # 各大类的比例（中餐、欧式、美式、拉美等更常见），以及每个大类的"通用"菜系值（列表里的第一个） # 出现的概率 —— 真实的 Yelp 上，"chinese"、"italian" 这样的通用分类远比 "izakaya" 常见。 GROUP_WEIGHTS = {"chinese": 0.18, "japanese": 0.1, "asian": 0.12, "latin": 0.12, "european": 0.14, "american": 0.14, "healthy": 0.06, "cafe": 0.1, "other": 0.04} P_GENERIC = 0.6 PRICES = ["cheap", "moderate", "expensive", "luxury"] # 隐藏的特征：数据库里没有这一列，只出现在评论里。每种特征有几种说法（评论里随机用一种）。 FEATURES = { "family_friendly": ["great place to bring the kids", "very family-friendly", "our children loved it", "they have a kids menu"], "view": ["the view of the bay is stunning", "amazing view from the patio", "you can see the Golden Gate"], "spicy": ["the food is really spicy", "bring tissues, the chili oil is fierce", "very hot and numbing dishes"], "romantic": ["perfect for a date night", "very romantic atmosphere", "candlelit and intimate"], "groups": ["great for large groups", "we came with twelve people and they handled it well", "big tables for parties"], "outdoor": ["lovely outdoor seating", "we sat on the patio", "nice sidewalk tables"], } FEATURE_LABELS = {"family_friendly": "适合带孩子", "view": "景色好", "spicy": "很辣", "romantic": "浪漫", "groups": "适合聚会", "outdoor": "有户外座位"} FILLER = ["service was friendly", "the portions are generous", "parking can be tricky", "a bit noisy on weekends", "prices are fair", "the staff was attentive", "we waited twenty minutes for a table", "the menu changes seasonally", "solid choice in the neighborhood", "the dessert was the highlight"] AUTHORS = ["Bob", "Alice", "Carol", "Dave", "Erin", "Frank", "Grace", "Heidi", "Ivan", "Judy"] _NAME_A = ["Golden", "Little", "Blue", "Red", "Happy", "Lucky", "Old", "Green", "Silver", "Sunny", "Royal", "Jade"] _NAME_B = ["Dragon", "Garden", "Table", "Spoon", "Lantern", "Kitchen", "Bowl", "Oak", "Harbor", "Lotus", "Fig", "Pearl"] _NAME_C = ["", " House", " Bistro", " Grill", " Eatery", " & Co."] @dataclass class Restaurant: """ 一家餐厅（生成数据时用；查询时请直接用 SQL）。 【字段】和数据库的列一一对应，另外： features : set[str]，隐藏的特征（FEATURES 的键）。数据库里**没有**这一列，只体现在评论里。 """ id: int name: str location: str address: str phone: str opening_hours: str cuisines: list price: str rating: float num_reviews: int popular_dishes: str features: set reviews: list # list[(author, text)] def has(cuisines, value): """ SQL 里的自定义函数 has(cuisines, 'chinese')：菜系列表里是否包含这个值（**精确匹配**，区分大小写）。 【参数】cuisines：数据库里存的文字，例如 "chinese|dim sum"；value：要找的值 【返回】1 或 0（SQLite 没有布尔类型，用 1/0 表示真假） 【边界】cuisines 为 NULL 时返回 0。精确匹配正是第 3 节问题的根源："coffee" ≠ "coffee & tea"。 """ if cuisines is None or value is None: return 0 return 1 if value in cuisines.split("|") else 0 class World: """ 餐厅世界：数据库连接 + 生成数据时的全部信息（含隐藏特征，用作标准答案）。 【常用】 world.query(sql, params=()) → list[tuple]：执行一条 SQL，返回全部结果行 world.restaurants → list[Restaurant] world.by_id[id] → Restaurant """ def __init__(self, restaurants): self.restaurants = restaurants self.by_id = {r.id: r for r in restaurants} self.db = sqlite3.connect(":memory:") # 【Python 小知识】create_function(SQL 里的名字, 参数个数, Python 函数)： # 把一个 Python 函数注册成 SQL 函数，之后就可以在 SQL 里写 has(cuisines, 'chinese')。 # 第 7 讲的 SUQL 正是用同样的机制，把"让 LLM 读一段文字"变成 SQL 里的一个函数。 self.db.create_function("has", 2, has) self.db.execute("""CREATE TABLE restaurants ( id INTEGER PRIMARY KEY, name TEXT, location TEXT, address TEXT, phone TEXT, opening_hours TEXT, cuisines TEXT, price TEXT, rating REAL, num_reviews INTEGER, popular_dishes TEXT)""") self.db.execute("CREATE TABLE reviews (id INTEGER PRIMARY KEY, restaurant_id INTEGER, author TEXT, text TEXT)") self.db.executemany("INSERT INTO restaurants VALUES (?,?,?,?,?,?,?,?,?,?,?)", [(r.id, r.name, r.location, r.address, r.phone, r.opening_hours, "|".join(r.cuisines), r.price, r.rating, r.num_reviews, r.popular_dishes) for r in restaurants]) rows, k = [], 0 for r in restaurants: for author, text in r.reviews: rows.append((k, r.id, author, text)) k += 1 self.db.executemany("INSERT INTO reviews VALUES (?,?,?,?)", rows) def query(self, sql, params=()): """执行 SQL，返回全部结果行（list[tuple]）。SQL 有语法错误时抛出 sqlite3.Error。""" return self.db.execute(sql, params).fetchall() def review_text(self, rid): """一家餐厅的全部评论，拼成一段文字。""" return " ".join(text for _a, text in self.by_id[rid].reviews) def _dishes(cuisines, rng): pool = {"chinese": "mapo tofu, dan dan noodles", "szechuan": "chili oil wontons", "dim sum": "har gow, siu mai", "ramen": "tonkotsu ramen", "sushi bars": "omakase, salmon nigiri", "mexican": "al pastor tacos", "italian": "cacio e pepe", "pizza": "margherita pizza", "thai": "pad see ew, green curry", "coffee & tea": "oat latte", "cafe": "croissant, cappuccino", "burgers": "double cheeseburger", "indian": "butter chicken", "korean": "bibimbap", "vietnamese": "pho"} found = [pool[c] for c in cuisines if c in pool] return found[0] if found else rng.choice(["house special", "chef's tasting menu", "seasonal plate"]) def make_world(seed=0, n=600): """ 生成 n 家餐厅。同一个 seed 总是生成同一个世界。 【参数】n：餐厅数，不能超过 864（名字的组合数） 【思路】 · 菜系：按 GROUP_WEIGHTS 选一个大类，再从里面选 1~3 个值；以 P_GENERIC 的概率带上大类的通用值； · 价格、评分、评论数随机，但有一点相关（贵的店评分略高）； · 隐藏特征：每种特征有 12%~25% 的餐厅具备（"很辣"只出现在中餐、泰餐、印度菜等）； · 评论：每家 3 条。具备的特征一定在某条评论里被提到（说法随机），其余是无关的闲话。 【返回】World """ rng = random.Random(seed) restaurants = [] spicy_ok = {"chinese", "szechuan", "hot pot", "thai", "indian", "korean", "mexican", "tacos"} # 名字：从 12 × 12 × 6 = 864 种组合里不重复地抽 n 个 all_names = [f"{a} {b}{c}" for a in _NAME_A for b in _NAME_B for c in _NAME_C] names = rng.sample(all_names, n) for i in range(n): name = names[i] group = rng.choices(list(GROUP_WEIGHTS), weights=list(GROUP_WEIGHTS.values()))[0] members = CUISINE_GROUPS[group] cuisines = rng.sample(members, k=min(len(members), rng.choice([1, 1, 2, 3]))) if members[0] not in cuisines and rng.random() < P_GENERIC: cuisines = [members[0]] + cuisines[:-1] if len(cuisines) > 1 else [members[0]] + cuisines price = rng.choices(PRICES, weights=[0.35, 0.4, 0.18, 0.07])[0] rating = round(min(5.0, max(2.0, rng.gauss(3.9 + 0.15 * PRICES.index(price), 0.45))) * 2) / 2 features = set() for f, p in [("family_friendly", 0.22), ("view", 0.12), ("romantic", 0.15), ("groups", 0.2), ("outdoor", 0.25)]: if rng.random() < p: features.add(f) if set(cuisines) & spicy_ok and rng.random() < 0.45: features.add("spicy") mentions = [rng.choice(FEATURES[f]) for f in sorted(features)] texts = mentions + rng.sample(FILLER, 3) rng.shuffle(texts) reviews = [] for j in range(3): chunk = texts[j::3] reviews.append((rng.choice(AUTHORS), ". ".join(s[0].upper() + s[1:] for s in chunk) + ".")) loc = rng.choice(LOCATIONS) restaurants.append(Restaurant( id=i, name=name, location=loc, address=f"{rng.randint(10, 999)} {rng.choice(['Main', 'University', 'Castro', 'Valencia', 'El Camino', 'Murphy'])} St, {loc}", phone=f"(650) {rng.randint(200, 999)}-{rng.randint(1000, 9999)}", opening_hours=rng.choice(["11am-9pm", "8am-3pm", "5pm-11pm", "11am-11pm"]), cuisines=cuisines, price=price, rating=rating, num_reviews=rng.randint(5, 2500), popular_dishes=_dishes(cuisines, rng), features=features, reviews=reviews)) return World(restaurants) if __name__ == "__main__": w = make_world() print(w.query("SELECT COUNT(*) FROM restaurants")[0][0], "家餐厅；", w.query("SELECT COUNT(*) FROM reviews")[0][0], "条评论") for row in w.query("SELECT name, location, cuisines, price, rating FROM restaurants LIMIT 3"): print(row) r = w.restaurants[0] print("隐藏特征：", r.features, "\n评论：", r.reviews)