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:

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.

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.