[Tutor] Looking for help in sqlite3 UPDATE and DELETE selection in treeview (ttk tkinter)

[Alan Gauld]

On 12/05/2021 13:46, Piotr Mazek wrote:

> I'm writing application to manage sqlite3 database in window made in tkinter
> with treeview.
> 
> Successfully wrote two functions: to show database, and to add new row.

Well done, the rest should be straightforward.

> But stuck on Update and delete selection. I think there are some stupid
> mistakes, but can't see them.

There are a few odd things in your code. See comments below...

>     def updateRecord():
> 
>         def updateR():
>             conn = sqlite3.connect('db_member.db')
>             c = conn.cursor()
>             # INSERT into table
> # I wish to get values from selection and place them in corresponding Entry
> fields, then change some value and save new state
> 
>             c.execute("UPDATE `member` SET (:mem_id, :firstname, :lastname,
> :address)"))

First point:
This will UPDATE every row in the table. If you only want to
modify a single row you need a WHERE clause to specify
the row(or rows) affected.

Second:
I've never seen the :mem_id syntax before. I assume it substitutes the
local variable name somehow? But I can't find any documentation of that
style?

Normally I'd expect an update statement to look like:

update = """
UPDATE or ROLLBACK member
SET
mem_id=?
firstname=?
lastname=?
address=?
WHERE mem_id = ?
"""

cur.execute(update,(mem_id, firstname, lastname, address, mem_id))

>             conn.commit()
>             conn.close()
> 
>             updateRoot.destroy()

>         updateRoot = Toplevel()
>         updateRoot.title("Update record")
>         updateRoot.iconbitmap('printer.ico')
>         lbl_fname = Label(updateRoot, text="First Name")
>         lbl_fname.grid(row=0, column=0, pady=10)
>         lbl_lname = Label(updateRoot, text="Last Name")
>         lbl_lname.grid(row=1, column=0, pady=10)
>         lbl_addr = Label(updateRoot, text="Address")
>         lbl_addr.grid(row=2, column=0, pady=10)
>         en_fname = Entry(updateRoot)
>         en_fname.grid(row=0, column=1, padx=10)
>         en_lname = Entry(updateRoot)
>         en_lname.grid(row=1, column=1, padx=10)
>         en_addr = Entry(updateRoot)
>         en_addr.grid(row=2, column=1, padx=10)
>         btn = Button(updateRoot, text="Update", command=updateR)
>         btn.grid(row=3, column=1, columnspan=2, sticky='we', padx=20)
> 
>         updateRoot.mainloop()

I may be wrong but I don't think you need to call mainloop()
here, the parent mainloop() will see the events for your
TopLevel too. I think...

>     def deleteRecord():
>         conn = sqlite3.connect('db_member.db')
>         c = conn.cursor()
> 
>         # DELETE from table
>         userid = tree.selection()[0]
>         tree.delete(userid)
> 
> # here I have no idea how to delete selected row from database
> 
>         c.execute("DELETE FROM `member` WHERE mem_id = ?", (userid))

You need to pass the arguments as a tuple, simply putting parens around
is not enough, you need a comma after the userid, its the comma that
makes it a tuple.

Also, I notice that you are putting the table name in quotes. That
should not be necessary (and may be an error?) since the entire SQL
statement is already in quotes. I certainly never quote table names
in my SQL. That may be part of the source of  the problem?


>         conn.commit()
>         conn.close()
Finally, I notice you are not detecting errors. You should really
use a try/except to catch database error exceptions and take
action (eg Rollback). You can re-raise them during debugging
if you want to see the stack-trace.

Of course you may have simplified the code for clarity in which
case just ignore me! :-)

-- 
Alan G
Author of the Learn to Program web site
http://www.alan-g.me.uk/
http://www.amazon.com/author/alan_gauld
Follow my photo-blog on Flickr at:
http://www.flickr.com/photos/alangauldphotos