0

I have a simple program "phonebook". One function of two these functions is update one record function but when I try to update single record/row it updates all records insted of one. I also have delete function of single selected row and when I press delete button it raises the error below:

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

Here is my code:

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)

1 Answer 1

1

The update statement should have a WHERE clause like this:

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

so only the row with the id that you must pass as the 4th parameter will be updated.
Change id to the name of the column that is the primary key of your table.

Or maybe use the columns First and Surname in the WHERE clause:

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

This means that you must pass as 4th and 5th parameter the name and surname of the row that you want to update.

For the DELETE statement the correct syntax is:

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

and you should also pass 3 parameters for the ? placeholders just like you do for the UPDATE statement.

Sign up to request clarification or add additional context in comments.

13 Comments

(For updating) So I have to add 4th column with Id?
Don't you have a primary key in the table? A column that is unique?
I added to this code above function which creates table in sqlite3 should I add sth other?
You don't have a primary key in the table. So the problem is: when you want to update a row, only 1 row, how will you make sure that only 1 row will be updated? See my edited answer.
I've got an error cur.execute("""UPDATE profile SET First=?, Surname=?, phone_number=? WHERE First=? AND Surname=?""", (data1, data2, data3)) sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 5, and there are 3 supplied.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.