如何将Client表字段添加到Jobs表的Tkinter Treeview搜索结果中？

我是一名A-level学生，若问题答案显而易见还请见谅，我目前陷入了思维瓶颈。我正尝试将Client表中的Firstname和Lastname字段添加到Jobs表的Tkinter Treeview中，作为数据库搜索机制的一部分。我能在数据库的标准Treeview中显示这些字段，但在Jobs表中搜索特定客户时，也希望能显示他们的姓名。由于Firstname和Lastname仅属于Client表，我尝试用查询获取并显示在Treeview中，但无法让其在搜索功能中生效。

以下是我的搜索机制代码：

def SearchTable(self,tableName,column,NameEntry,tree,tableNo):
        valid = True
        e = str(NameEntry.get())
        for ch in e:
            if tableNo == 1:
                if ch.upper() not in "ABCDEFGHIJKLMNOPQRSTUVWXYZ-":
                    valid = False
            if tableNo == 2:
                if ch not in "1234567890/":
                    valid = False
            if tableNo == 3:
                if ch not in "1234567890":
                    valid = False
        if not valid:
            NameEntry.config(bg="red")
            showerror("Planar Pete Error!","Error! Please retype your entry")
            NameEntry.delete(0,END)
            NameEntry.focus_set()
            NameEntry.config(bg="white")
        conn = sqlite3.connect("PlanarPeteVes2.db")
        curse = db.cursor()
        print(e)
        
        #curse.execute(f"SELECT Firstname, Lastname FROM Client WHERE ClientNo = 1")
        #result = str(curse.fetchall())
        #FLName = self.ProcessString(result)
        #print(FLName)
        
        curse.execute(f"SELECT * FROM {tableName} WHERE {column} = '{e}'")
        result1 = str(curse.fetchall())
        #allOfTable = self.ProcessString(result1)
        
        
        #result = str(FLName)+str(allOfTable)
        #print(result)
        count = 0
        
        for row in result1:
            if tableNo == 1:
                disp=(f'{row[0]} {row[1]} {row[2]} {row[3]} {row[4]} {row[5]} {row[6]} {row[7]}')
                tree.insert("",count,text="", values=disp)
                count +=1
            elif tableNo == 2:
                disp=(f'{row[0]} {row[1]} {row[2]} {row[3]} {row[4]}')
                tree.insert("",count,text="", values=disp)
                count +=1
            elif tableNo == 3:
                disp=(f'{row[4]} {row[5]} {row[6]}')
                tree.insert("",count,text="",values=disp)
                count +=1
               
        return NameEntry

所有注释掉的代码都是我尝试解决问题的方案，希望能得到您的建议，谢谢！

核心思路：用SQL JOIN关联两张表 #

Jobs表应该存在关联Client表的字段（比如ClientNo），直接通过JOIN语句一次性获取Jobs数据和对应的客户姓名，不需要分开查询再拼接，这是解决问题的关键。

步骤1：修复SQL查询逻辑 #

• 替换字符串拼接的查询方式，改用参数化查询避免SQL注入风险
• 针对Jobs表，通过JOIN关联Client表获取姓名字段

# 替换原有的查询代码段
if tableName == "Jobs":
    # 假设Jobs表通过ClientNo字段关联Client表
    curse.execute(f"""
        SELECT Jobs.*, Client.Firstname, Client.Lastname 
        FROM Jobs 
        JOIN Client ON Jobs.ClientNo = Client.ClientNo 
        WHERE {column} = ?
    """, (e,))
else:
    # 其他表保留原有查询逻辑，同样改用参数化方式
    curse.execute(f"SELECT * FROM {tableName} WHERE {column} = ?", (e,))

步骤2：修复结果处理逻辑 #

原代码将fetchall()结果转为字符串，导致遍历的是字符串字符而非数据库行。直接使用fetchall()返回的元组列表即可，同时在插入Treeview时加入客户姓名：

# 替换原有的结果遍历代码段
# 先清空Treeview旧内容，避免重复插入
for item in tree.get_children():
    tree.delete(item)

count = 0
result = curse.fetchall()
for row in result:
    if tableNo == 1:
        # 直接传递元组给values，不需要拼接字符串
        disp = row[:8]
        tree.insert("", count, text="", values=disp)
    elif tableNo == 2:  # 假设tableNo=2对应Jobs表
        # 取Jobs原有字段，加上最后两个客户姓名字段
        disp = row[:5] + (row[-2], row[-1])
        tree.insert("", count, text="", values=disp)
    elif tableNo == 3:
        disp = row[4:7]
        tree.insert("", count, text="", values=disp)
    count += 1

步骤3：修复其他细节问题 #

• 原代码中curse = db.cursor()应为curse = conn.cursor()，避免数据库连接对象错误
• 验证失败后直接返回，避免后续不必要的执行
• 查询完成后关闭数据库连接，避免资源泄漏

完整修改后的方法 #

def SearchTable(self, tableName, column, NameEntry, tree, tableNo):
    valid = True
    e = str(NameEntry.get())
    # 输入验证逻辑保留
    for ch in e:
        if tableNo == 1:
            if ch.upper() not in "ABCDEFGHIJKLMNOPQRSTUVWXYZ-":
                valid = False
        if tableNo == 2:
            if ch not in "1234567890/":
                valid = False
        if tableNo == 3:
            if ch not in "1234567890":
                valid = False
    if not valid:
        NameEntry.config(bg="red")
        showerror("Planar Pete Error!", "Error! Please retype your entry")
        NameEntry.delete(0, END)
        NameEntry.focus_set()
        NameEntry.config(bg="white")
        return NameEntry  # 验证失败直接返回

    conn = sqlite3.connect("PlanarPeteVes2.db")
    curse = conn.cursor()

    # 根据表名执行对应查询
    if tableName == "Jobs":
        curse.execute(f"""
            SELECT Jobs.*, Client.Firstname, Client.Lastname 
            FROM Jobs 
            JOIN Client ON Jobs.ClientNo = Client.ClientNo 
            WHERE {column} = ?
        """, (e,))
    else:
        curse.execute(f"SELECT * FROM {tableName} WHERE {column} = ?", (e,))
    
    result = curse.fetchall()
    conn.close()  # 关闭数据库连接

    # 清空Treeview旧内容
    for item in tree.get_children():
        tree.delete(item)

    count = 0
    for row in result:
        if tableNo == 1:
            disp = row[:8]
            tree.insert("", count, text="", values=disp)
        elif tableNo == 2:
            disp = row[:5] + (row[-2], row[-1])
            tree.insert("", count, text="", values=disp)
        elif tableNo == 3:
            disp = row[4:7]
            tree.insert("", count, text="", values=disp)
        count += 1

    return NameEntry

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