How can I get a row in Sqlite3 table with Tkinter Listbox widget?

Question

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?

maybe you should get data[listbox.curselection()] instead of listbox.get(listbox.curselection()) — furas
– furas, Commented Dec 9, 2020 at 21:19
This seems to give me following error: TypeError: list indices must be integers or slices, not tuple — Heikki
– Heikki, Commented Dec 9, 2020 at 21:46
use print( listbox.curselection() ) to see what you get - it seems it doesn't return single value but tuple (value,) and you may need [0] to get value - data[listbox.curselection()[0]] — furas
– furas, Commented Dec 9, 2020 at 21:54

Delrius Euphoria · Accepted Answer · 2020-12-30 23:12:14Z

1

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.

edited Dec 30, 2020 at 23:12

answered Dec 9, 2020 at 23:25

Delrius Euphoria

15.1k3 gold badges20 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Heikki Over a year ago

Where ifs the variable data defined in the this code?

Delrius Euphoria Over a year ago

@Heikki Oh, data was taken from your code for the database part Anyway i've update the answer.

Heikki Over a year ago

This works! I've not had any experience with dictionaries yet (I am that new to this stuff), I've seen tutorials, but without usefull context it's hard to imagine what to use it for and therefore easy to not use it all. Good thing to see it applied usefully and purposfully to something. It makes it that much easier to understand. Sorry for long response time. Also on c = conn.cursor( you are missing closing ), but couldn't edit it because it was less that 6 characters.

Delrius Euphoria Over a year ago

@heikki, sure I'll put that in.

acw1668 · Accepted Answer · 2020-12-10 04:34:06Z

You can use row_factory of sqlite3 to create a dictionary cursor and get similar output you want:

import sqlite3
from tkinter import *

def dict_factory(cursor, row):
    return {col[0]:row[idx] for idx, col in enumerate(cursor.description)}

conn = sqlite3.connect('database.db')
conn.row_factory = dict_factory
c = conn.cursor()

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 rec in data:
    listbox.insert(END, rec["subject"])

def get_serial():
    selected = listbox.curselection()
    if selected:
        print(data[selected[0]])
    else:
        print("No item selected")

btn = Button(root, text="Show row", command=get_serial)
btn.pack()

mainloop()

And the output:

{'id': 1, 'subject': 'Dogs', 'serial': 5, 'is_active': 1}

Collectives™ on Stack Overflow

How can I get a row in Sqlite3 table with Tkinter Listbox widget?

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related