请求将指定SQL查询语句转换为Peewee框架实现代码

1. 定义对应的数据模型 #

首先需要为Loot、Quests、Rewards三张表定义Peewee模型（假设已存在数据库连接对象db）：

from peewee import Model, IntegerField, CharField

class BaseModel(Model):
    class Meta:
        database = db

class Quests(BaseModel):
    questID = IntegerField(primary_key=True)
    # 可根据实际表结构补充其他字段

class Rewards(BaseModel):
    rewardID = IntegerField(primary_key=True)
    tokenContract = CharField()
    rewardCount = IntegerField()
    # 可根据实际表结构补充其他字段

class Loot(BaseModel):
    questID = IntegerField()
    rewardID = IntegerField()
    tokenID = IntegerField()
    # 可根据实际表结构补充其他字段

2. 实现对应查询逻辑 #

对应原SQL的Peewee查询代码如下：

# 执行查询，返回字典格式结果
parts_test = (Loot
              .select(Loot.tokenID, Rewards.rewardCount)
              .join(Quests, on=(Loot.questID == Quests.questID))
              .join(Rewards, on=(Loot.rewardID == Rewards.rewardID))
              .where((Quests.questID == quest_id) & (Rewards.tokenContract == PARTS_ADDR))
              .order_by(Rewards.rewardCount)
              .dicts())

# 若需要和原SQL fetchall()一致的元组列表格式，可转换结果
parts_test_list = list(row.values() for row in parts_test)

对应关系说明 #

• .select(Loot.tokenID, Rewards.rewardCount)：对应SQL的SELECT tokenID, rewardCount
• .join(Quests, on=(Loot.questID == Quests.questID))：对应INNER JOIN Quests ON Loot.questID = Quests.questID
• .join(Rewards, on=(Loot.rewardID == Rewards.rewardID))：对应INNER JOIN Rewards ON Loot.rewardID = Rewards.rewardID
• .where(...)：对应SQL的WHERE条件，Peewee用&表示逻辑与（需用括号包裹单个条件）
• .order_by(Rewards.rewardCount)：对应ORDER BY rewardCount

内容的提问来源于stack exchange，提问作者Dorius Kornely