创建<mydb.db>数据库,并创建<mytable>数据表内含_id、no、name字段,其中_id为自动编号,no为数值,name为字符串,创建后自动新增5条记录,并使用ListView由小至大排序显示全部数据。在edtID字段输入编号后点击查询按钮可以查询指定编号的数据,点击查询全部按钮显示全部数据。
时间: 2023-12-18 16:02:15 浏览: 178
查询数据库名,表名,表字段等
5星 · 资源好评率100%
以下是使用Python的SQLite3模块实现你所需的功能:
```python
import sqlite3
from tkinter import *
from tkinter import messagebox
class App:
def __init__(self, master):
self.master = master
self.master.title("My Database")
# 创建数据库连接并创建数据表
self.conn = sqlite3.connect("mydb.db")
self.c = self.conn.cursor()
self.c.execute("CREATE TABLE IF NOT EXISTS mytable (_id INTEGER PRIMARY KEY AUTOINCREMENT, no INTEGER, name TEXT)")
# 添加5条初始记录
self.c.execute("INSERT INTO mytable (no, name) VALUES (?, ?)", (1, "John Doe"))
self.c.execute("INSERT INTO mytable (no, name) VALUES (?, ?)", (2, "Jane Smith"))
self.c.execute("INSERT INTO mytable (no, name) VALUES (?, ?)", (3, "Bob Johnson"))
self.c.execute("INSERT INTO mytable (no, name) VALUES (?, ?)", (4, "Alice Brown"))
self.c.execute("INSERT INTO mytable (no, name) VALUES (?, ?)", (5, "Tom Wilson"))
self.conn.commit()
# 创建查询区域
self.frame_query = Frame(self.master)
self.frame_query.pack(side=TOP, padx=10, pady=10)
Label(self.frame_query, text="ID:").grid(row=0, column=0, padx=5, pady=5)
self.edt_id = Entry(self.frame_query)
self.edt_id.grid(row=0, column=1, padx=5, pady=5)
btn_query = Button(self.frame_query, text="查询", command=self.query)
btn_query.grid(row=0, column=2, padx=5, pady=5)
btn_query_all = Button(self.frame_query, text="查询全部", command=self.query_all)
btn_query_all.grid(row=0, column=3, padx=5, pady=5)
# 创建显示区域
self.frame_display = Frame(self.master)
self.frame_display.pack(side=TOP, padx=10, pady=10)
self.listbox = Listbox(self.frame_display, width=50, height=10)
self.listbox.pack()
# 显示全部数据
self.query_all()
def query(self):
# 查询指定ID的数据并显示
try:
_id = int(self.edt_id.get())
self.c.execute("SELECT * FROM mytable WHERE _id=?", (_id,))
data = self.c.fetchone()
if data:
self.listbox.delete(0, END)
self.listbox.insert(END, f"ID: {data[0]}")
self.listbox.insert(END, f"No: {data[1]}")
self.listbox.insert(END, f"Name: {data[2]}")
else:
messagebox.showinfo("提示", "未找到指定编号的数据")
except ValueError:
messagebox.showwarning("警告", "请输入合法的编号")
def query_all(self):
# 查询全部数据并按照no升序排列后显示
self.c.execute("SELECT * FROM mytable ORDER BY no ASC")
data = self.c.fetchall()
self.listbox.delete(0, END)
for item in data:
self.listbox.insert(END, f"ID: {item[0]}")
self.listbox.insert(END, f"No: {item[1]}")
self.listbox.insert(END, f"Name: {item[2]}")
self.listbox.insert(END, "")
def __del__(self):
self.conn.close()
root = Tk()
app = App(root)
root.mainloop()
```
运行程序后,你将看到一个简单的GUI界面。在查询区域输入指定的ID后点击查询按钮,程序将会查询该ID对应的数据并在显示区域显示;点击查询全部按钮,程序将会查询全部数据并按照no升序排列后在显示区域显示。
需要注意的是,SQLite3模块会自动创建一个带有“_id”字段的ROWID作为主键,因此我们不需要手动创建“_id”字段。
阅读全文