如何使用Tkinter Listbox小部件在Sqlite3表中获得一行? [英] How can I get a row in Sqlite3 table with Tkinter Listbox widget?

问题描述

我有前端连接到Tkinter的Python程序连接到Sqlite3数据库.我的数据库表(主题列表)由四列组成:[id(唯一整数)，主题(文本)，序列(唯一整数)，is_active(布尔整数)].这是我的程序:

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()

当前，在运行时，当我单击列表框上的项目(基本上显示同一行上具有is_active = 1的所有主题列值)，然后按Tkinter按钮时，我得到了单击的主题.相反，我想获得单击的整个行.

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:

1. 主题列在两个或更多不同的行上可能具有相同的值.
2. 列表框上的项目按序列顺序排列
3. 如果行is_active的值为0(否)，则它将不会显示在列表框中.列表框上将没有空行，而下一个is_active = 1(真)行将取代它.

考虑此表(左)及其在GUI上的表示(右):

Consider this table (left) and its representation on GUI (right):

我希望GUI首先在列表框中显示所有is_active = 1主题.然后，我点击狗"(列表框显示的第三项)，然后单击按钮，我希望程序将整行打印给我(id = 1，subject = Dogs，serial = 5，is_active = 1).

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?

推荐答案

我想到使用 Listbox 并获取整个行值的唯一方法是保持将值插入到列表框中，与参考书一起保存在字典中，以及通常增加的数字.该字典采用 {idx:[id，subject]} 的形式，实际上您不需要在列表中包含主题，您也可以仅使用id进行操作，但是它可以使您更容易理解主题的选择.

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.

看看:

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.

输出:

(1,'Dogs', 5, 1) #(Id, subject, serial, is_active) 

尽管如果要使用 ttk.Treeview ，则实际上可以创建两列，一列带有id，另一列带有主题，因此从中提取数据要容易得多，而不是使用一个列表框，然后将信息保存在字典中，以后再用它来搜索数据库.

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.

这篇关于如何使用Tkinter Listbox小部件在Sqlite3表中获得一行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！