如何获取使用变量的SQL查询结果？

Question

请帮帮我。我正在创建一个小的图形用户界面（GUI）字典，想把这个界面和数据库连接起来。我可以正确地把单词和定义输入到数据库里，但我似乎无法从数据库中查找单词，以便在我的界面输出框中显示相应的定义。

模块 1（主程序）：

from tkinter import *
import libraryentrySQL
import sqlite3
import os



def click():
    entered_text = entry.get() #collect text from text entry box
    output.delete(0.0,END) #clears text box - start clearing from 0.0 (from line 0) to END (after last character)
    new_db = sqlite3.connect('dictionary.db')
    c=new_db.cursor()
    try:
        definition = c.execute("SELECT definition FROM Dictionary WHERE word=%s", (entered_text))
    except:
        definition = "No word found in dictionary, try again!"
    output.insert(END, definition) #this inserts the contents of variable 'definition' at the beginning (END) - because it was cleared before, END is the at the start

def clickentry(): #this function is run when the 2nd button (entry is pressed)
    def definition_submitted(word, definition):
        new_db = sqlite3.connect('dictionary.db')
        c=new_db.cursor()
        c.execute("INSERT INTO Dictionary VALUES (?, ?)", (word, definition))
        new_db.commit()
        new_db.close()

    definition_window = libraryentrySQL.DefinitionWindow(window, definition_submitted) #this creates the object 'definition window' and passes to it 'the window variable'
                                                                                        #so that it can have a canvas
                                                                                        #and also passes the function 'definition_submitted' so that as the new word and definition are entered
                                                                                        #in the this object (second window) it can be passed into the function and the dictionary updated

window = Tk()

window.title("My Little Dictionary")

#Create the Label
Label(window, text="Enter the word you want defining:").grid(row=0, column=0, sticky=W)

#create entry box
entry=Entry(window, width=20, bg="light green")
entry.grid(row=1, column=0, sticky=W)

#create submit button
Button(window, text="Submit", width=5, command=click).grid(row=2, column=0, sticky=W)

#create second label
Label(window, text="\nDefinition").grid(row=3, column=0, sticky=W)

#create text box
output=Text(window, width=75, height=6, wrap=WORD, background="light green")
output.grid(row=4, column=0, sticky=W)

#create submit button to open enter new definition window
Button(window, text="Enter a New Definition", width=20, command=clickentry).grid(row=5, column=0, sticky=W)


#Create the Dictionary.db if not already present
if not os.path.isfile("dictionary.db"):
    new_db = sqlite3.connect('dictionary.db')
    c=new_db.cursor()

    c.execute('''CREATE TABLE Dictionary
    (word text,
    definition text)''')

    c.execute('''INSERT INTO Dictionary VALUES
    ('Algorithm', 'Step by step instructions to complete a task')''')

    new_db.commit()
    new_db.close()

window.mainloop()

模块 2（输入单词和定义的窗口）：

from tkinter import *

class DefinitionWindow(Toplevel):
    def __init__(self, root, click_callback):
        Toplevel.__init__(self, root)
        self.click_callback = click_callback
        self.title("Library entry")

        #Create the Label
        Label(self, text="Enter the word you want to add:").grid(row=0, column=0, sticky=W)

        #create entry box
        self.word_entry=Entry(self, width=20, bg="light green")
        self.word_entry.grid(row=1, column=0, sticky=W)

        #create second label
        Label(self, text="\nDefinition").grid(row=2, column=0, sticky=W)

        #create entry box
        self.definition_entry = Entry(self, width=50, bg="light green")
        self.definition_entry.grid(row=3, column=0, sticky=W)

        #create submit button
        Button(self, text="Submit", width=5, command=self.clicked).grid(row=4, column=0, sticky=W)

    def clicked(self):
        self.click_callback(self.word_entry.get(), self.definition_entry.get()) #when this function is called (on submit button click) it takes the entered
                                                                                #word and definition and assigns it to click_callback, which is an attribute of DefinitionWindow??
        self.destroy() #after the word and definition are added to the call_back variable, the frame containing this instance of definition window is closed

我哪里做错了？我知道是“SELECT”这个SQL命令不对。任何帮助都将不胜感激。谢谢！

Answer 1

这不是在Python中使用SQL查询的正确方式。

execute这个方法返回的不是一个值，而是一个游标（cursor）。当没有找到任何结果时，不会抛出异常，游标就会是空的。

在except块中盲目处理所有异常会隐藏任何编程错误。

另外，参数的标记不是%s，而是?。

最后，Python中的元组如果只有一个值，必须加上逗号，以便和单个表达式区分开：

c = new_db.cursor()
c.execute("SELECT definition FROM Dictionary WHERE word = ?", (entered_text,))
for row in c:
    definition = row[0]
    break
else:
    definition = "No word found in dictionary, try again!"
output.insert(END, definition)