Skip to content
Advertisement

How can I get the value of a row in a column in SQLite 3 table with Tkinter Listbox widget?

I have Python program connected to SQLite 3 database with Tkinter on the frontend. My database table (subjectlist) consists of three columns: [id (unique interger), subject (text), serial (unique integer)]. Here is my program:

import sqlite3
from tkinter import *


conn = sqlite3.connect('database.db')
c = conn.cursor()
c.execute('SELECT COUNT() FROM subjectlist')
number = (c.fetchone()[0])

c.execute('SELECT * FROM subjectlist 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 serial", command=lambda: get_serial())
btn.pack()

mainloop()

Currently at runtime when I click item on listbox (witch basically shows all subject column values) and then press Tkinter button I get the subject I clicked. Instead I want to get serial corresponding to the subject. Notice that subject column may have same value on two or more different rows. How would I go about achieving this?

Here is just one more example; If I have this table:

Enter image description here

I want the GUI to show first all of the subjects in the listbox. Then I click “Cats” (on the the id row 3) and then I click the button, I want the program to print me serial 4.

Advertisement

Answer

Well, this should also be easy. But since there is a database in your code that I can’t currently test this with it, I made some assumptions. Try this out:

def get_letter():
    conn = sqlite3.connect('database.db')
    c = conn.cursor()
    ids = listbox.curselection()[0]+1 # Getting the index number starting from 1
    c.execute('SELECT * FROM subjectlist WHERE `id`=?;',(ids,)) # Searching the database for items with selected items index
    rows = c.fetchall()
    print(rows[0]) # Print the first and hopefully the only item in the list
    conn.close() # Close the connection

This should print the rows which corresponds to the id, and since usually id is a unique number, it will only print out one row. I’m also assuming that you have both the database and the listbox in the same order or this might not work.

Advertisement