Double insert into listbox SQLITE3 & TKINTER

Question

I have a problem with my program. When I click on the save button, the first data I entered fits correctly. But when I then insert new data, the first data gets copied into my listbox and then the new ones are inserted too.

I thinks the problem's here :

def save_register():
    firstname = entry_firstname.get()
    name = entry_name.get()
    adress = entry_adress.get()
    phone = entry_phone.get()
    connection = sql3.connect("gestionnaire.db")
    cursor = connection.cursor()
    cursor.execute("INSERT INTO informations ('FirstName', 'Name', 'Adress', 'Phone') VALUES (?,?,?,?)", (firstname, name, adress, phone))
    connection.commit()
    rows = cursor.execute("SELECT * FROM informations")
    for row in rows:
        list_1.insert(END, row) #TODO insertion en double

ALL CODE :

from tkinter import *
import sqlite3 as sql3

#FONCTIONS
def create_table_and_database():
    connection = sql3.connect("gestionnaire.db")
    cursor = connection.cursor()
    cursor.execute("""CREATE TABLE IF NOT EXISTS informations(
                      id INTEGER PRIMARY KEY AUTOINCREMENT, 
                      FirstName TEXT NOT NULL,
                      Name TEXT NOT NULL,
                      Adress TEXT NOT NULL,
                      Phone TEXT NOT NULL)             
    """)

def save_register():
    firstname = entry_firstname.get()
    name = entry_name.get()
    adress = entry_adress.get()
    phone = entry_phone.get()
    connection = sql3.connect("gestionnaire.db")
    cursor = connection.cursor()
    cursor.execute("INSERT INTO informations ('FirstName', 'Name', 'Adress', 'Phone') VALUES (?,?,?,?)", (firstname, name, adress, phone))
    connection.commit()
    rows = cursor.execute("SELECT * FROM informations")
    for row in rows:
        list_1.insert(END, row) #TODO insertion en double

def delete_register():
    index = list_1.curselection()
    connection = sql3.connect("gestionnaire.db")
    cursor = connection.cursor()
    for row in index:
        cursor.execute("DELETE FROM informations")
        list_1.delete(row)
    connection.commit()

def refresh_insert():
    connection = sql3.connect("gestionnaire.db")
    cursor = connection.cursor()
    rows = cursor.execute("SELECT * FROM informations")
    for row in rows:
        list_1.insert(END, row)  # TODO insertion en double
    connection.commit()

def new_registers():
    entry_name.delete(0, END)
    entry_firstname.delete(0, END)
    entry_adress.delete(0, END)
    entry_phone.delete(0, END)

def get_list(event):
    index = list_1.curselection()
    seltext = list_1.get(index)[1]
    seltext_2 = list_1.get(index)[2]
    seltext_3 = list_1.get(index)[3]
    seltext_4 = list_1.get(index)[4]
    entry_firstname.delete(0, 50)
    entry_name.delete(0, 50)
    entry_adress.delete(0, 50)
    entry_phone.delete(0, 50)
    entry_firstname.insert(0, seltext)
    entry_name.insert(0, seltext_2)
    entry_adress.insert(0, seltext_3)
    entry_phone.insert(0, seltext_4)



if __name__ == "__main__":
    create_table_and_database()


# BASE WINDOW

root = Tk()
root.title("Gestionnaire d'adresses")
root.geometry("700x300")
root.resizable(width=0, height=0)

# LISTBOX
list_1 = Listbox(root, height = 100, width = 23)
list_1.pack(side = LEFT, fill = BOTH)
list_1.bind('<ButtonRelease-1>', get_list)

# LABEL, ENTRY
# FIRST NAME
lbl_firstname = Label(root,text = "Nom :")
lbl_firstname.place(x =200, y = 50)
entry_firstname = Entry(root)
entry_firstname.place(x = 300, y = 50, width = 390)

# NAME
lbl_name = Label(root,text = "Prénom :")
lbl_name.place(x =200, y = 100)
entry_name = Entry(root)
entry_name.place(x = 300, y = 100, width = 390)

# ADRESS
lbl_adress = Label(root,text = "Adresse :")
lbl_adress.place(x =200, y = 150)
entry_adress = Entry(root)
entry_adress.place(x = 300, y = 150, width = 390)

# PHONE
lbl_phone = Label(root,text = "Téléphone :")
lbl_phone.place(x =200, y = 200)
entry_phone = Entry(root)
entry_phone.place(x = 300, y = 200, width = 390)


# BUTTON

# NEW
button_new = Button(root, text="Nouveau", command = new_registers)
button_new.pack()
button_new.place(x = 300, y = 250)

# SAVE
button_save = Button(root, text="Enregistrer", command = save_register)
button_save.pack()
button_save.place(x = 400, y = 250)

# DELETE
button_delete = Button(root, text="Supprimer", command = delete_register)
button_delete.pack()
button_delete.place(x = 500, y = 250)

# QUIT
button_quit = Button(root, text="Quitter", command = quit)
button_quit.pack()
button_quit.place(x = 600, y = 250)

refresh_insert()
root.mainloop()

Answer 1

It is because you forgot to clear the listbox list_1 before propagating data from the table:

def save_register():
    firstname = entry_firstname.get()
    name = entry_name.get()
    adress = entry_adress.get()
    phone = entry_phone.get()
    connection = sql3.connect("gestionnaire.db")
    cursor = connection.cursor()
    cursor.execute("INSERT INTO informations ('FirstName', 'Name', 'Adress', 'Phone') VALUES (?,?,?,?)", (firstname, name, adress, phone))
    connection.commit()
    list_1.delete(0, "end") # clear list_1
    rows = cursor.execute("SELECT * FROM informations")
    for row in rows:
        list_1.insert(END, row)

Or just append the new record to list_1:

def save_register():
    firstname = entry_firstname.get()
    name = entry_name.get()
    adress = entry_adress.get()
    phone = entry_phone.get()
    connection = sql3.connect("gestionnaire.db")
    cursor = connection.cursor()
    cursor.execute("INSERT INTO informations ('FirstName', 'Name', 'Adress', 'Phone') VALUES (?,?,?,?)", (firstname, name, adress, phone))
    connection.commit()
    # insert new record into list_1
    list_1.insert(END, (cursor.lastrowid, firstname, name, adress, phone))

There is another issue in delete_register(): it will remove all records in informations table other than the selected record in list_1. You need to add a WHERE clause in the DELETE SQL statement:

def delete_register():
    index = list_1.curselection()
    if index:
        recid = list_1.get(index)[0]  # get the unique ID of the selected record
        connection = sql3.connect("gestionnaire.db")
        cursor = connection.cursor()
        for row in index:
            cursor.execute("DELETE FROM informations WHERE id = ?", (recid,)) # delete selected record
            list_1.delete(row)
        connection.commit()

Answer 2

In accordance with https://pynative.com/python-sqlite-insert-into-table/, you can also use following:

rows = cursor.fetchall() instead of rows = cursor.execute("SELECT * FROM informations") ?

Then also close the cursor object afterwards with cursor.close().

Generally it,s also good to use a try-catch block around your CUD-operations , so you will be able to handle checked exceptions.

Hopefully, this will help!