How do I delete or edit records from data base

Question

I apologize in advance for my bad English... I am new in the programming world so I don't really know what I'm doing. I'm trying to make a basic address book in Python, Tkinter. I managed somehow to write code to add records in the database but cannot write it to delete or edit selected records. Help would be appreciate

import datetime
import tkinter
from tkinter import *
import tkinter as tk
from tkinter import ttk
from PIL import Image, ImageTk
import sqlite3


def date_for_humans():
    date = datetime.datetime.now().date()
    date = str(date)
    x = date.split("-")
    return x[2] + "." + x[1] + "." + x[0] + "."


# creating windows for show contact
def add():
    kontakti = Toplevel()
    kontakti.title("Lista kontakta")
    kontakti.iconbitmap(r"phonebookicon.ico")
    kontakti.geometry("400x500")
    kontakti.resizable(False, False)

    def submit():
        # create data base or connect to one
        conn = sqlite3.connect("Imenik.db")

        # create cursor
        c = conn.cursor()

        # insert into a table
        c.execute("INSERT INTO Kontakti VALUES (:f_name, :l_name, :number)",
                  {
                    "f_name": f_name.get(),
                    "l_name": l_name.get(),
                    "number": number.get()
                  })

        # commint changes
        conn.commit()

        conn.close()

        f_name.delete(0,END)
        l_name.delete(0, END)
        number.delete(0, END)

    # creating and coloring top frame
    topframe = tk.Frame(kontakti, height=150, bg="#ffffff")
    topframe.pack(fill=X)

    # creating and coloring bottom farame
    bottomframe = tk.Frame(kontakti, height=350, bg="#34ebeb")
    bottomframe.pack(fill=X)

    # creating text at the top of app:
    text2 = Label(kontakti, text="DODAVANJE KONTAKTA", font="ariel 15 bold", bg="#ffffff", fg="black")
    text2.place(x=80, y=20)

    # creating close button
    button1 = Button(kontakti, text="Zatvori prozor", bg="white", fg="black",
                     activebackground="#A9A9A9", font=("Helvetica", 10, "bold"), command=kontakti.destroy)
    button1.place(x=295, y=465)

    # create text boxes
    f_name = Entry(kontakti, width=30)
    f_name.place(x=80, y=200, height=20)
    l_name = Entry(kontakti, width=30)
    l_name.place(x=80, y=230, height=20)
    number = Entry(kontakti, width=30)
    number.place(x=80, y=260, height=20)

    # create text box labels
    f_name_label = Label(kontakti, text="Ime", bg="#34ebeb")
    f_name_label.place(x=20, y=200)
    l_name_label = Label(kontakti, text="Prezime", bg="#34ebeb")
    l_name_label.place(x=20, y=230)
    number_label = Label(kontakti, text="Broj", bg="#34ebeb")
    number_label.place(x=20, y=260)

    # create sumbit button
    submint_btn = Button(kontakti, text="Dodaj kontakt", bg="white", fg="black",
                         activebackground="#A9A9A9", font=("Helvetica", 10, "bold"), command=submit)
    submint_btn.place(x=40, y=320)


def edit():

    c.execute("UPDATE Kontakti SET f_name=?, l_name=?, number=? WHERE f_name= ? AND l_name = ? AND number=?",
              (new_value_for_f_name, new_value_for_l_name, new_value_for_number, f_name, l_name, number))
    conn.commit()


def delete():

    f_name = listbox.curselection()[0]
    l_name = listbox.curselection()[1]
    number = listbox.curselection()[2]

    c.execute("DELETE * FROM Kontakti WHERE f_name = ? AND l_name = ? AND number = ?", (f_name, l_name, number))
    conn.commit()


def leave():
    root.destroy()


# creating a main window:
root = Tk()
root.title("Imenik App")
root.iconbitmap(r"phonebookicon.ico")
root.geometry("650x550")
root.resizable(False, False)

# creating and coloring top frame:
topFrame = tk.Frame(root, height=150, bg="#ffffff")
topFrame.pack(fill=X)


# creating and coloring bottom frame:
bottomFrame = tk.Frame(root, height=500, bg="#34ebeb")
bottomFrame.pack_propagate(False)
bottomFrame.pack(fill=X)

listbox = Listbox(bottomFrame)
listbox.place(x=40, y=40, height=340, width=200)
scrollbar = Scrollbar(bottomFrame)
scrollbar.place(height=340, x=240, y=40)

# Insert elements into the listbox

conn = sqlite3.connect("Imenik.db")
c = conn.cursor()
a = c.execute("SELECT *,oid FROM Kontakti")
records = c.fetchall()

for record in records:
    listbox.insert(END, str(record[0]) + " " + str(record[1]))

listbox.config(yscrollcommand=scrollbar.set)
scrollbar.config(command=listbox.yview)

# creating text at the top of app:
text1 = Label(root, text="IMENIK", font="ariel 35 bold", bg="#ffffff", fg="black")
text1.place(x=240, y=40)

# displaying date and time at the top of app:

datel = Label(root, text="Danasnji datum: " + date_for_humans(), font="ariel 10 bold", bg="#ffffff", fg="black")
datel.place(x=450, y=125)

# displaying icon at the top of the app:
image1 = Image.open("phonebook1.png")
image1 = image1.resize((90, 90), Image.ANTIALIAS)
image = ImageTk.PhotoImage(image1)
label1 = tkinter.Label(image=image, bg="#ffffff")
label1.image = image
label1.place(x=80, y=30)

conn.commit()
conn.close()


'''
# create data base or connect to one
conn = sqlite3.connect("Imenik.db")

# create cursor
c = conn.cursor()

# create table

c.execute("""CREATE TABLE kontakti (
        first_name txt,
        last_name txt,
        number integer
        )""")


# commint changes
conn.commit()

conn.close()
'''

# adding button 1, add contacts
viewButton = Button(root, text="Dodaj kontakt", pady=10, padx=70, bg="white", fg="black",
                    activebackground="#A9A9A9", font=("Helvetica", 10, "bold"), command=add)
viewButton.place(x=380, y=200)

# adding button 2, edit contacts
addButton = Button(root, text="Izmeni kontakt", pady=10, padx=67, bg="white", fg="black",
                   activebackground="#A9A9A9", font=("Helvetica", 10, "bold"), command=edit)
addButton.place(x=380, y=260)

# adding button 3, delete contacts
deleteButton = Button(root, text="Obrisi kontakt", pady=10, padx=70, bg="white", fg="black",
                      activebackground="#A9A9A9", font=("Helvetica", 10, "bold"), command=delete)
deleteButton.place(x=380, y=320)

# adding button 4, exit button
exitButton = Button(root, text="Izlaz", pady=5, padx=50, bg="white", fg="black",
                    activebackground="#A9A9A9", font=("Helvetica", 10, "bold"), command=leave)
exitButton.place(x=505, y=510)

root.mainloop()

If anything is unclear please ask I really need help. Thanks in advance!

Answer 1

Hi and welcome to Stack Overflow. For you to know which data is selected with the mouse, you can use the following way:

name_of_listbox.curselection()

This will return a list of the contents of the rows currently selected. It gives us a list because there is an option to select multiple rows of data in a ListBox. You can iterate through each and grab the data out of them. Store the data in a variable and use them for UPDATE and DELETE commands.

EDIT:

For deleting records:

f_name = listbox.curselection()[0]
l_name = listbox.curselection()[1]
number = listbox.curselection()[2]

c1.execute("DELETE * FROM Kontakti WHERE f_name = ? AND l_name = ? AND number = ?", (f_name, l_name, number))
conn1.commit()

For modifying the values:

c1.execute("UPDATE Kontakti SET f_name=?, l_name=?, number=? WHERE f_name= ? AND l_name = ? AND number=?",(new_value_for_f_name,new_value_for_l_name,new_value_for_number, f_name, l_name, number))
conn1.commit()

I quite did not get the way you were selecting first name, last name and number from a single listbox. So I have kept the name of the listbox the same.

Answer 2

You are using literal sql script and basically you can delete and update like below.

An advice ORM (Object Relational Mapping) is more effortless rather than raw query.

For Delete

DELETE <TableName> WHERE Id = <Value>

conn = sqlite3.connect("Imenik.db")
c = conn.cursor()
sql = "DELETE TableName WHERE Id = ?"
id = 1
c.execute(sql, (id,))

For Update

UPDATE <TableName> SET <ColumnName> = <NewValue> WHERE Id = <Value>

conn = sqlite3.connect("Imenik.db")
c = conn.cursor()
sql = "UPDATE TableName SET ColumnName = ? WHERE Id = ?"
id = 1
new_value = 'hello'
# params order left to right (important).
c.execute(sql, (new_value, id))