I have Python program connected to Sqlite3 database with Tkinter on the frontend. My database table (subjectlist) consists of four columns: [id (unique interger), subject (text), serial (unique interger), is_active (boolean interger)]. Here is my program:
import sqlite3 from tkinter import * conn = sqlite3.connect('database.db') c = conn.cursor() c.execute('SELECT COUNT() FROM subjectlist WHERE is_active = 1') number = c.fetchone()[0] c.execute('SELECT * FROM subjectlist WHERE is_active = 1 ORDER BY serial') data = c.fetchall() c.close conn.close() root = Tk() listbox = Listbox(root) listbox.pack() for i in range(number): listbox.insert(END, data[i][1]) def get_serial(): print(listbox.get(listbox.curselection())) btn = Button(root, text="Show row", command=lambda: get_serial()) btn.pack() mainloop()
Currently at runtime when I click item on listbox (which basically shows all subject column values that have is_active=1 on the same row) and then press Tkinter button I get the subject I clicked. Instead I want to get the whole row I clicked.
There are few things to consider about the table:
- Subject column may have same value on two or more different rows.
- Items on the listbox are aranged in order by the serial
- If rows is_active value is 0 (False) it will not be displayed on the listbox. There will be no empty rows on the listbox and the next is_active=1 (True) row will take its place.
Consider this table (left) and its representation on GUI (right):
I want GUI to first show all of is_active=1 the subjects in the listbox. Then I click “Dogs” (third item on what the listbox shows) and then I click the button, I want the program to print me the whole row (id=1, subject=Dogs, serial=5, is_active=1).
How would I go about achieving this?
Advertisement
Answer
The only way I could think of using Listbox
and getting the entire row value pulled out is keeping the value inserted into the listbox, kept with a reference inside a dictionary, along with a normally increasing number. The dictionary is in the form of {idx:[id,subject]}
, indeed you dont need to include subject in the list, you can do it with just the id too, but it can make you easier to understand selection with subject.
Take a look:
from tkinter import * import sqlite3 conn = sqlite3.connect('database.db') c = conn.cursor() c.execute('SELECT * FROM subjectlist ORDER BY serial') data = c.fetchall() conn.close() root = Tk() dictio = {} # Empty dictionary to keep reference of appended values listbox = Listbox(root) listbox.pack() a = 0 # A normally increasing number as the key of the dict tot_rows = len(data) # Total number of rows for i in range(tot_rows): if data[i][3]: # If is_active == 1 dictio[a] = [data[i][0],data[i][1]] # Append the subject as the a-th item to dict, along with its id number listbox.insert(END, data[i][1]) # Inseert the data #add the subject to listbox a += 1 # Increase the number by 1 def get_serial(): conn = sqlite3.connect('database.db') c = conn.cursor() val = listbox.curselection()[0] # Index of our current selection sel = dictio[val] # Get value from the dictionary based on the indexed number ids = sel[0] # Index the first item of the list c.execute('SELECT * FROM subjectlist WHERE `id`=?',(ids,)) # Search the database based on id of item print(c.fetchall()) # Print the item out as a tuple conn.close() # Close the connection btn = Button(root, text="Show row", command=get_serial) btn.pack() listbox.bind('<Double-Button-1>',lambda e=None:get_serial()) # Bonus :p mainloop()
I’ve expanded the code a lot to make it more understandable and I hope this is what you meant by the question too. I’ve commented the code to make it understandable on the go too.
Output:
(1,'Dogs', 5, 1) #(Id, subject, serial, is_active)
Though if you were to use a ttk.Treeview
, you could actually create two columns, one with id and the other with subject, so its much easier to pull out data from, rather than using a listbox and keeping the info inside a dictionary and using it to search the database later on.