-1

I'm Trying to delete an item in SQLite Database from a tree view in TKinter GUI. The tree view returns a value like I002. Tried to convert to string or in a variable also, nothing works. Here is the delete function code

def Database():
    global conn, cursor
    conn = sqlite3.connect('pythontut.db')
    cursor = conn.cursor()
    cursor.execute("CREATE TABLE IF NOT EXISTS `anno` (mem_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, daytype TEXT, time TEXT, audiofile TEXT)")
    
def Create():
    
    TIME = CHOUR.get() +":"+ CMIN.get() + ":"+ CSEC.get() + " "+CPERIOD.get()
    print(TIME)

    if  DAYTYPE.get() == "" or TIME == "":
        txt_result.config(text="Please complete the required field!", fg="red")
    else:
        Database()
        cursor.execute("INSERT INTO `anno` (daytype, time, audiofile) VALUES(?, ?, ?)", (str(DAYTYPE.get()), str(TIME), str(AUDIO_SELECTION.get())) )
        conn.commit()
        DAYTYPE.set("")
        cursor.close()
        conn.close()
        txt_result.config(text="Created a data!", fg="green")

def Read():
    tree.delete(*tree.get_children())
    Database()
    cursor.execute("SELECT * FROM `anno` ORDER BY `daytype` ASC")
    fetch = cursor.fetchall()
    for data in fetch:
        tree.insert('', 'end', values=(data[0], data[1], data[2], data[3]))
    cursor.close()
    conn.close()
    txt_result.config(text="Successfully read the data from database", fg="black")

def Delete():
    
    selected_items = tree.selection()
    Database()  
    for selected_item in selected_items:          
        tree.delete(selected_item)
        print(str(selected_item))
        cur = conn.cursor()
        cur.execute("DELETE FROM anno WHERE mem_id = ?", (selected_items,))
        conn.commit()
    conn.close()

Returning this following error

cur.execute(sql)
sqlite3.OperationalError: no such column: I002

I'm trying to Delete the selected items from the treeview in the TKinter as well as in the SQLite Database.

GUI of ListView

Sarath kumar
  • 11
  • 2
  • Don't use string concatenation for this, use parameter substitution as shown in the linked duplicate. – snakecharmerb Dec 21 '22 at 12:06
  • Tried even with the recommended method, still the same. Why does the selected value always contain values like I002? instead of 1 or 2 as it is in the table? – Sarath kumar Dec 21 '22 at 14:05
  • Can you [edit] the question to include the table defintion (the create table statement) and the value of `selected_item` (note that in the code your are passing `selected_items`, which is probably not what you want). – snakecharmerb Dec 21 '22 at 14:12
  • What is in `selected_items` if you print it? – snakecharmerb Dec 21 '22 at 14:23
  • Judging by [this unanswered question](https://stackoverflow.com/questions/53747066/copy-data-from-one-table-to-another-sqlite3), "I002" is a representation of `tree.selection`, so you are passing `tree.selection` to `cursor.execute` instead of the actual `id`. Print your variables to check. – snakecharmerb Dec 21 '22 at 14:31
  • How do you populate the data to the treeview? – acw1668 Dec 21 '22 at 14:39
  • selected_items contains treeview selections on the GUI. (It returns a tuple I Guess). When multiple values selected it returns = ('I003', 'I004'). But in Table view the value of ID(as on image) is 3, 4 only. There is some additional letters I00 adds with it. That is the reason it is not taking in the SQL Query. May I know why it adds and how to take only the ID. – Sarath kumar Dec 22 '22 at 05:08
  • You need to provide details how you populate the data into the treeview so that we can know how the get the IDs you want. – acw1668 Dec 22 '22 at 06:25

1 Answers1

0

I'm attaching the below code that works. To get values from the selection, we need to use tree.item(i, "values")[0] the 0 represents the column. i holds this strange value I005 is from the tree view location (If the table has 5 items it shows 5th item as I005). Using the above command it takes out the value inside it & that can be stored in a variable (as shown below). That strange value they are calling it as iid (still no idea what it is). Below is the snippet attached that works in treeview as well as Database.

def DeleteRecord(id_num):
    con = sqlite3.connect("pythontut.db")
    cur = con.cursor()
    cur.execute("DELETE FROM anno WHERE mem_id = ?", (id_num,))
    con.commit()
    con.close()

def DeleteData():
    try:
        messageDelete = tkMessageBox.askyesno("Confirmation", "Do you want to permanently delete this record?")
        if messageDelete > 0:
            select_items = tree.selection()
        for i in select_items:
            id_num = tree.item(i, "values")[0] #Returns data inside that treeview selection column 0 (iid)
            print(id_num)
            tree.delete(i)
            DeleteRecord(id_num)
    except Exception as e:
        print(e)
Sarath kumar
  • 11
  • 2