时间戳

首页 > 解决方案 > 更新和删除功能的问题

python - 更新和删除功能的问题

问题描述

我有一个简单的程序“电话簿”。这两个函数中的一个函数是更新一个记录函数，但是当我尝试更新单个记录/行时，它会更新插入一个记录的所有记录。我也有单个选定行的删除功能，当我按下删除按钮时，它会引发以下错误：

c.execute("DELETE FROM profile WHERE First=?, Surname=?, phone_number=?") sqlite3.OperationalError: near ",": syntax error.

这是我的代码：

def create_sql():
conn = sqlite3.connect("phone_book.db")
cur = conn.cursor()
cur.execute("CREATE TABLE IF NOT EXISTS profile (First TEXT, Surname TEXT, phone_number TEXT)")
conn.commit()
conn.close()
def get_row(event):
    try:
        cursor = tree.focus()
        content = tree.item(cursor)
        row = content["values"]
        one.set(row[0])
        two.set(row[1])
        three.set(row[2])
    except IndexError:
        pass
def update():
data1 = one.get()
data2 = two.get()
data3 = three.get()
cursor = tree.focus()
content = tree.item(cursor)
row = content["values"]
one.set(row[0])
two.set(row[1])
three.set(row[2])
conn = sqlite3.connect("phone_book.db")
cur = conn.cursor()
cur.execute("""UPDATE profile SET First=?, Surname=?, phone_number=?""", (data1, data2, data3,tree.set(row)))
conn.commit()
conn.close()
def del_sel():
    try:
        selected_item = tree.selection()[0] ## get selected item
        tree.delete(selected_item)
    except IndexError:
        pass
    conn = sqlite3.connect("phone_book.db")
    c = conn.cursor()
    c.execute("DELETE FROM profile WHERE First=? AND Surname=? AND phone_number=?"(data1, data2, data3) )
    conn.commit()
    conn.close()
def add_contact():
    if e.get() == "" and e1.get() =="" and e2.get() =="":
        messagebox.showwarning("Input Error","Przyjmniej jedno pole\nmusi być wypełnione")
    else:
        conn = sqlite3.connect("phone_book.db")
        c = conn.cursor()
        c.execute("INSERT INTO profile VALUES(:e,:e1,:e2)",
                {
                    'e':e.get(),
                    'e1':e1.get(),
                    'e2':e2.get()
                }
        )
        conn.commit()
        conn.close()
        tree.insert("",'end',values=(e.get(),e1.get(),e2.get()))
        e.delete(0,END)
        e1.delete(0,END)
        e2.delete(0,END)
    e = Entry(root,bd=2,textvariable=one)
e.grid(row=0,column=1,padx=10,pady=5)
e1 = Entry(root,bd=2,textvariable=two)
e1.grid(row=1,column=1,padx=10,pady=5)
e2 = Entry(root,bd=2,textvariable=three)
e2.grid(row=2,column=1,padx=10,pady=5)
e3 = Entry(root,bd=2)
e3.grid(row=5,column=1,pady=5)

tree = ttk.Treeview(root,height=10)
tree["columns"]=("one","two","three")
tree.column("one",width=120)
tree.column("two",width=130)
tree.column("three",width=160)
tree.heading("one", text="Imię")
tree.heading("two", text="Nazwisko")
tree.heading("three", text="Nr Telefonu")
tree["show"]="headings"
tree.grid(row=4,column=0,columnspan=6,padx=20)

标签： pythonsqlitetkinter

解决方案

更新语句应该有这样的WHERE子句：

UPDATE profile SET First=?, Surname=?, phone_number=? WHERE id=?

所以只有id你必须作为第四个参数传递的行才会被更新。
更改id为作为表主键的列的名称。

或者也许在子句中使用列First和：SurnameWHERE

UPDATE profile SET First=?, Surname=?, phone_number=? WHERE First=? AND Surname=?

这意味着您必须将要更新的行的名称和姓氏作为第 4 和第 5 个参数传递。

对于该DELETE语句，正确的语法是：

DELETE FROM profile WHERE First=? AND Surname=? AND phone_number=?

并且您还应该为?占位符传递 3 个参数，就像您为UPDATE语句所做的那样。

推荐阅读