How to delete a table row in sqlite3?

Question

I’m trying to crud sqlite3 with a GUI through Tkinter my table was created in DB Browser for sqlite. I’ve developed a little but it’s not working:

from tkinter import *
from tkinter import ttk
import sqlite3


#db_name = 'bancodados.db'

root = Tk()
root.geometry('600x500+340+50')
root.title('Cadastro')
root.resizable(False,False)
root.configure(bg='azure')

frame = LabelFrame(root, text='Adicionar Novo',bg='azure')
frame.grid(padx=200,pady=30)
lb = Label(frame,text='Nome:',width=15,bg='azure')
lb.grid(row=1,column=0)
lb2 = Label(frame, text='Preço:',bg='azure')
lb2.grid(row=2,column=0)
def add():
    query = '''INSERT INTO produto(name, price) VALUES(?, ?)'''
    cursor.execute(query, (name.get(), price.get()))

    for row in cursor:
        tree.insert('', 0, text=row[1], value=row[2])
    cursor.execute('SELECT * FROM produto ORDER BY name DESC')
    view()
    conn.commit()
    tree.mainloop()
def delete():
    cursor.execute('DELETE FROM produto WHERE price = id',())
    conn.commit()


    for row in cursor:
        tree.insert('', 0, text=row[1], value=row[2])
    view()
    tree.mainloop()
    cursor.close()
i = Button(frame,text='Adicionar',bg='azure',command=add)
i.grid(row=4,column=1,pady=4)
o = Label(frame)
o.grid(row=0,column=0)


name = Entry(frame)
name.grid(row=1,column=1,padx=5)

price = Entry(frame)
price.grid(row=2,column=1,padx=5)

tree = ttk.Treeview(height=12,column=2)
tree.grid(row=1,column=0,columnspan=2)
tree.heading('#0', text='Nome',anchor=CENTER)
tree.heading('#1', text='Preço',anchor=CENTER)
#vs = ttk.Scrollbar()
#vs.grid()
#tree.configure(yscrollcommand=vs.set)

bt_ed = Button(text='Editar',bg='azure')
bt_ed.place(x=330,y=450)
bt_del = Button(text='Deletar',bg='azure',command=delete)
bt_del.place(x=270,y=450)

conn = sqlite3.connect('bancodados.db')
cursor = conn.cursor()
cursor.execute('SELECT * FROM produto ')


def view():

    records = tree.get_children()
    for element in records:
        tree.delete(element)
    for row in cursor:
        tree.insert('', 0, text=row[1], value=row[2])

view()
root.mainloop()

Answer 1

Good morning ! Your code had many errors so I just rewrote some parts, now I think it’s working according to what Oce wants !

from tkinter import *
from tkinter import ttk
import sqlite3

conn = sqlite3.connect('bancodados.db')
cursor = conn.cursor()

root = Tk()
root.geometry('600x500+340+50')
root.title('Cadastro')
root.resizable(False,False)
root.configure(bg='azure')

frame = LabelFrame(root, text='Adicionar Novo',bg='azure')
frame.grid(padx=200,pady=30)
lb = Label(frame,text='Nome:',width=15,bg='azure')
lb.grid(row=1,column=0)
lb2 = Label(frame, text='Preço:',bg='azure')
lb2.grid(row=2,column=0)

nome = StringVar()
preco = StringVar()

def add():
    var1 = nome.get()
    var2 = preco.get()

    cur = cursor.execute('INSERT INTO produto(name, price) VALUES (?, ?)',(var1, var2,))

    tree.delete(*tree.get_children())

    cur2 = cursor.execute('SELECT * FROM produto')
    fetch = cursor.fetchall()
    for fetch in fetch:
        tree.insert("", END, values = fetch)

    conn.commit()

#Nessa função você consegue deletar a linha da tabela atraves da Treeview 
 selecionado o item que deseja deletar.
def delete():
    selection = tree.selection()
    for selection in tree.selection():
        cursor.execute('DELETE FROM produto WHERE name = ?',(tree.set(selection, "#1"),))

    tree.delete(*tree.get_children())

    cur2 = cursor.execute('SELECT * FROM produto')
    fetch = cursor.fetchall()
    for fetch in fetch:
        tree.insert("", END, values = fetch)

    conn.commit()


i = Button(frame,text='Adicionar',bg='azure',command=add)
i.grid(row=4,column=1,pady=4)
o = Label(frame)
o.grid(row=0,column=0)


name = Entry(frame, textvariable = nome)
name.grid(row=1,column=1,padx=5)

price = Entry(frame, textvariable = preco)
price.grid(row=2,column=1,padx=5)

tree = ttk.Treeview(height=12,column=2, columns=('Nome', "Preco"))
tree.grid(row=1,column=0,columnspan=2)
tree['show'] = 'headings'
tree.heading('#0', text='Nome',anchor=CENTER)
tree.heading('#1', text='Preço',anchor=CENTER)
#vs = ttk.Scrollbar()
#vs.grid()
#tree.configure(yscrollcommand=vs.set)

bt_ed = Button(text='Editar',bg='azure')
bt_ed.place(x=330,y=450)
bt_del = Button(text='Deletar',bg='azure',command=delete)
bt_del.place(x=270,y=450)

def view():
    cur2 = cursor.execute('SELECT * FROM produto')
    fetch = cursor.fetchall()
    for fetch in fetch:
        tree.insert("", END, values = fetch)
view()

root.mainloop()