Tkinter Admin Panel: Manage Products with SQLite Database
from tkinter import *
from tkinter import messagebox
from tkinter.font import Font
from tkinter import ttk
# import mysql.connector as mysql
import sqlite3
# Creating Tkinter Window
Admin = Tk()
Admin.geometry('1330x750')
Admin.resizable(0, 0)
Admin.iconbitmap('./images/Logo.ico')
Admin.title('Admin')
# Creating Mysql connection
dbconn = sqlite3.connect('./Database/RSgroceries.db')
# Create a cursor to give commands
cursor = dbconn.cursor()
# Create Tables
# category Table
cursor.execute('''CREATE TABLE if not exists category(
category varchar(100) NOT NULL primary key
)
''')
dbconn.commit()
cursor.execute('''CREATE TABLE if not exists products(
product_id int not null primary key,
product_name varchar(100) not null,
product_rate int not null,
category varchar(100) not null references category(category)
)
''')
dbconn.commit()
cursor.execute('SELECT * FROM products')
prod_1 = cursor.fetchall()
# print(prod_1)
dbconn.commit()
# Fixing GUI Background
Background = PhotoImage(file='./images/Admin_bg.png')
Bg_label = Label(Admin, image=Background)
Bg_label.place(x=0, y=0, relwidth=1, relheight=1)
# Creating invoice
table = ttk.Treeview(Admin)
table['columns'] = ('ID','Product Name','Category', 'Rate')
table.column('#0', width=0,stretch=NO)
table.column('#1', width=50,anchor='center')
table.column('#2', width=230,anchor='center')
table.column('#3',width=230,anchor='center')
table.column('#4',width=120,anchor='center')
table.heading('#0',text='')
table.heading('#1',text='ID')
table.heading('#2',text='Product Name')
table.heading('#3',text='Category')
table.heading('#4',text='Rate')
table.place(relx=0.50,rely=0.1139,height=528.8, width=630)
Scroll_invoice = Scrollbar(orient='vertical',command=table.yview)
table.configure(yscroll=Scroll_invoice.set)
Scroll_invoice.place(relx=0.961,rely=0.1140, height=527.3)
for row in prod_1:
table.insert('',index='end',values=(row[0],row[1],row[3],row[2]))
# Defining Exit function
def Exit():
sure = messagebox.askyesno('Exit','Are you sure you want to exit?', parent=Admin)
if sure == True:
Admin.destroy()
# adm.destroy()
# Creating logout button
logout_img = PhotoImage(file='./images/logout.png')
logout_button = Button(Admin, image=logout_img, borderwidth=0,relief='flat',overrelief='flat', command=Exit)
logout_button.place(relx=0.0155, rely=0.038,width=39,height=31)
# Creating all the required widgets
# Creating text variables
cat = StringVar()
pro_name = StringVar()
pro_rate = StringVar()
font_1 = Font(family='Calibri',size=15,weight='bold')
# All Entry widgets
# Product Category Widget
Entry_1 = Entry(Admin,font=font_1,relief='flat',bg='#fefffe')
Entry_1.place(relx=0.043,rely=0.622,width=423,height=50)
# Product Rate Widget
Entry_2 = Entry(Admin, font=font_1,relief='flat',bg='#fefffe')
Entry_2.place(relx=0.043,rely=0.780,width=423,height=50)
# Product Name Widget
Entry_3 = Entry(Admin,font=font_1,relief='flat',bg='#fefffe')
Entry_3.place(relx=0.043,rely=0.463,width=423,height=50)
# Product Id Widget
Entry_4 = Entry(Admin,font=font_1,relief='flat',bg='#fefffe')
Entry_4.place(relx=0.043,rely=0.3205,width=423,height=50)
# Search code Entry Widget
Entry_5 = Entry(Admin, font=font_1,relief='flat',bg='#fefafa')
Entry_5.place(relx=0.161,rely=0.115,width=255,height=40)
# Defining all the required functions
# CREATING FUNCTION TO REMOVE UNWANTED CATEGORY
def unwanted_cat():
category_delete_1 = table.get_children()
categories_avail = []
for rec in category_delete_1:
values = table.item(rec).get('values')[2]
categories_avail.append(values)
cursor.execute('SELECT category FROM category')
cat_t = cursor.fetchall()
all_cat = []
for i in cat_t:
all_cat.append(i[0])
available_category = []
for fin in all_cat:
if fin in categories_avail:
available_category.append(fin)
else:
pass
cursor.execute('DROP TABLE category')
dbconn.commit()
# Creating table product if not exist
cursor.execute('''CREATE TABLE if not exists category(
category varchar(100) NOT NULL primary key
)
''')
dbconn.commit()
for last in available_category:
try:
cursor.execute('INSERT INTO category VALUES('{}')'.format(last))
dbconn.commit()
except sqlite3.IntegrityError:
pass
# Add to cart
def add_to_cart():
# Creating table product if not exist
cursor.execute('''CREATE TABLE if not exists category(
category varchar(100) NOT NULL primary key
)
''')
cursor.execute('''CREATE TABLE if not exists products(
product_id int not null primary key,
product_name varchar(100) not null,
product_rate int not null,
category varchar(100) not null references category(category)
)
''')
dbconn.commit()
all_rec = table.get_children()
ids = []
for rec in all_rec:
values = table.item(rec).get('values')[0]
ids.append(values)
if (Entry_2.get().isdigit() or Entry_2.get()=='') :
try:
if Entry_1.get() != '' and Entry_2.get() != '' and Entry_3.get() != '' and Entry_4.get() != '':
n = messagebox.askyesno('Add to Market', 'Are you sure you want to add it to the Market?')
if n == 1:
cursor.execute('SELECT product_id FROM products')
id_check = cursor.fetchall()
id_check_fin = []
dbconn.commit()
if (int(Entry_4.get()),) in id_check:
messagebox.showerror('Error', 'Product id already in the market')
else:
table.insert('', index='end', values=(Entry_4.get(), Entry_3.get(), Entry_1.get(), Entry_2.get()))
cursor.execute('INSERT INTO products VALUES(:product_id, :product_name, :product_rate, :category)',
{
'product_id': Entry_4.get(),
'product_name': Entry_3.get(),
'product_rate': Entry_2.get(),
'category': Entry_1.get()
}
)
cursor.execute('SELECT category FROM category')
categories_db = cursor.fetchall()
categories = []
for i in categories_db:
categories.append(i[0])
if Entry_1.get() not in categories:
cursor.execute('INSERT INTO category VALUES(:category)',
{'category': Entry_1.get()})
dbconn.commit()
else:
pass
dbconn.commit()
Entry_1.delete(0, END)
Entry_2.delete(0, END)
Entry_3.delete(0, END)
Entry_4.delete(0, END)
unwanted_cat()
else:
pass
else:
messagebox.showerror('Error', 'Please fill the details')
except ValueError:
messagebox.showerror('Error', 'Please enter correct product ID!')
else:
Entry_2.delete(0, END)
messagebox.showerror('Error', 'Please enter correct quantity!')
# Update
def update():
# Creating table product if not exist
cursor.execute('''CREATE TABLE if not exists category(
category varchar(100) NOT NULL primary key
)
''')
cursor.execute('''CREATE TABLE if not exists products(
product_id int not null primary key,
product_name varchar(100) not null,
product_rate int not null,
category varchar(100) not null references category(category)
)
''')
dbconn.commit()
Button_1.configure(state='active')
if Entry_1.get() != '' and Entry_2.get() != '' and Entry_3.get() != '' and Entry_4.get() != '':
cursor.execute('SELECT product_id FROM products')
id_check = cursor.fetchall()
dbconn.commit()
if (int(Entry_4.get()),) in id_check:
all_rows = table.get_children()
k = []
for i in all_rows:
if table.item(i).get('values')[0] == int(Entry_4.get()):
k.append(i)
else:
pass
table.item(k[0], text='', values=(int(Entry_4.get()) ,Entry_3.get(), Entry_1.get(), Entry_2.get()))
cursor.execute('''
UPDATE products SET product_name = '{}', category = '{}', product_rate = {} WHERE product_id = {}'''
.format(Entry_3.get(), Entry_1.get(), Entry_2.get(), int(Entry_4.get())))
dbconn.commit()
cursor.execute('SELECT category FROM category')
categories_db = cursor.fetchall()
categories = []
for i in categories_db:
categories.append(i[0])
if Entry_1.get() not in categories:
cursor.execute('INSERT INTO category VALUES(:category)',
{'category': Entry_1.get()})
dbconn.commit()
Entry_1.delete(0, END)
Entry_2.delete(0, END)
Entry_3.delete(0, END)
Entry_4.delete(0, END)
unwanted_cat()
else:
messagebox.showerror('Error', 'Product ID not in the market')
else:
messagebox.showerror('Error', 'Fill all the details')
# Clear
def clear():
# Creating table product if not exist
cursor.execute('''CREATE TABLE if not exists category(
category varchar(100) NOT NULL primary key
)
''')
cursor.execute('''CREATE TABLE if not exists products(
product_id int not null primary key,
product_name varchar(100) not null,
product_rate int not null,
category varchar(100) not null references category(category)
)
''')
dbconn.commit()
Entry_1.delete(0, END)
Entry_2.delete(0, END)
Entry_3.delete(0, END)
Entry_4.delete(0, END)
Button_1.configure(state='active')
unwanted_cat()
# Select Item
def select_item():
# Creating table product if not exist
cursor.execute('''CREATE TABLE if not exists category(
category varchar(100) NOT NULL primary key
)
''')
cursor.execute('''CREATE TABLE if not exists products(
product_id int not null primary key,
product_name varchar(100) not null,
product_rate int not null,
category varchar(100) not null references category(category)
)
''')
dbconn.commit()
items_n = table.selection()
if len(items_n)>1:
messagebox.showerror('Error', 'Two or more items are selected')
else:
if items_n == ():
messagebox.showerror('Error', 'No Item(s) selected')
else:
Entry_1.delete(0, END)
Entry_2.delete(0, END)
Entry_3.delete(0, END)
Entry_4.delete(0, END)
sel_item = []
for i in items_n:
k = table.item(i, 'values')
for j in k:
sel_item.append(j)
Entry_4.insert(0, sel_item[0])
Entry_3.insert(0, sel_item[1])
Entry_2.insert(0, sel_item[3])
Entry_1.insert(0, sel_item[2])
unwanted_cat()
Button_1.configure(state='disabled')
# Delete item(s)
def delete_many():
# Creating table product if not exist
cursor.execute('''CREATE TABLE if not exists category(
category varchar(100) NOT NULL primary key
)
''')
cursor.execute('''CREATE TABLE if not exists products(
product_id int not null primary key,
product_name varchar(100) not null,
product_rate int not null,
category varchar(100) not null references category(category)
)
''')
dbconn.commit()
items_n = table.selection()
if items_n == ():
messagebox.showerror('Error', 'No Item(s) selected')
else:
n = messagebox.askyesno('Delete item(s)','Are you sure you want to delete the selected item(s)?')
if n == 1:
pro_id = []
for i in items_n:
k = table.item(i, 'values')
pro_id.append(k[0])
for rows_n in items_n:
table.delete(rows_n)
for row in pro_id:
cursor.execute('DELETE FROM products WHERE product_id={}'.format(row))
dbconn.commit()
unwanted_cat()
else:
pass
# Clear All
def clear_all():
# Creating table product if not exist
cursor.execute('''CREATE TABLE if not exists category(
category varchar(100) NOT NULL primary key
)
''')
cursor.execute('''CREATE TABLE if not exists products(
product_id int not null primary key,
product_name varchar(100) not null,
product_rate int not null,
category varchar(100) not null references category(category)
)
''')
dbconn.commit()
if table.get_children() == ():
messagebox.showerror('Error','No Items in the Market')
else:
n = messagebox.askyesno('Clear All', 'Are you sure you want to clear all the items?')
if n == 1:
for rows in table.get_children():
table.delete(rows)
cursor.execute('DROP TABLE products')
dbconn.commit()
unwanted_cat()
else:
pass
def search_id():
if Entry_5.get() == '':
messagebox.showerror('Error', 'Enter ID to search')
else:
id = int(Entry_5.get())
cursor.execute('SELECT product_id FROM products')
id_check = cursor.fetchall()
dbconn.commit()
all_rows = table.get_children()
row = []
for i in all_rows:
if table.item(i).get('values')[0] == id:
row.append(i)
if row == []:
messagebox.showerror('Error', 'No product with ID {}'.format(id))
else:
Button_1.configure(state='disabled')
for j in row:
Entry_1.delete(0, END)
Entry_2.delete(0, END)
Entry_3.delete(0, END)
Entry_4.delete(0, END)
values = table.item(j).get('values')
Entry_4.insert(0, values[0])
Entry_3.insert(0, values[1])
Entry_2.insert(0, values[3])
Entry_1.insert(0, values[2])
Entry_5.delete(0, END)
unwanted_cat()
# All Button Widgets
# Non-Table widgets
# Add to Market
Button_1 = Button(Admin, text='Add to market', relief='flat', bg='#fe1716',fg='black',borderwidth=0,font=font_1,command=add_to_cart)
Button_1.configure(activebackground='#fe1716')
Button_1.place(relx=0.04325,rely=0.878,width=135,height=43)
# Modify
Button_2 = Button(Admin, text='Update', relief='flat', bg='#fe1716', fg='black', borderwidth=0, font=font_1, command=update)
Button_2.configure(activebackground='#fe1716')
Button_2.place(relx=0.161, rely=0.878, width=135, height=43)
# Clear
Button_3 = Button(Admin, text='Clear', relief='flat', bg='#fe1716', fg='black', borderwidth=0, font=font_1,command=clear)
Button_3.configure(activebackground='#fe1716')
Button_3.place(relx=0.278, rely=0.878, width=135, height=43)
# Search
search_img = PhotoImage(file='./images/search.png')
search_button = Button(Admin, image=search_img, borderwidth=0,relief='flat',overrelief='flat', command=search_id)
search_button.place(relx=0.3713, rely=0.1175)
# Table widgets
# Select
Button_4 = Button(Admin, text='Select', relief='flat', bg='#fe1716', fg='black', borderwidth=0, font=font_1,command=select_item)
Button_4.configure(activebackground='#fe1716')
Button_4.place(relx=0.512, rely=0.8855, width=135, height=43)
# Delete item(s)
Button_5 = Button(Admin, text='Delete item(s)', relief='flat', bg='#fe1716',fg='black',borderwidth=0,font=font_1, command=delete_many)
Button_5.configure(activebackground='#fe1716')
Button_5.place(relx=0.686,rely=0.8855,width=135,height=43)
# Clear All
Button_6 = Button(Admin, text='Clear All', relief='flat', bg='#fe1716', fg='black', borderwidth=0, font=font_1, command=clear_all)
Button_6.configure(activebackground='#fe1716')
Button_6.place(relx=0.862, rely=0.8855, width=135, height=43)
Admin.protocol('WM_DELETE_WINDOW', Exit)
Admin.mainloop()
代码解释：
-
导入库:
from tkinter import *: 导入 Tkinter 库所有模块和函数，方便使用 Tkinter 的所有功能。from tkinter import messagebox: 导入messagebox模块，用于显示消息框，例如警告、确认等。from tkinter.font import Font: 导入Font模块，用于自定义字体样式。from tkinter import ttk: 导入ttk模块，用于创建主题化的窗口小部件，例如表格。import sqlite3: 导入sqlite3库，用于连接 SQLite 数据库。
-
创建主窗口:
Admin = Tk(): 创建一个名为Admin的主窗口，这是应用程序的根窗口。Admin.geometry('1330x750'): 设置窗口大小为 1330x750 像素。Admin.resizable(0, 0): 禁止窗口大小调整。Admin.iconbitmap('./images/Logo.ico'): 设置窗口图标，从./images/Logo.ico加载。Admin.title('Admin'): 设置窗口标题为 'Admin'。
-
连接数据库:
dbconn = sqlite3.connect('./Database/RSgroceries.db'): 连接到名为RSgroceries.db的 SQLite 数据库，数据库文件位于./Database文件夹内。cursor = dbconn.cursor(): 创建一个游标对象，用于执行 SQL 语句。
-
创建数据表:
cursor.execute('''CREATE TABLE if not exists category(...)'''): 创建名为category的数据表，如果表已经存在则不创建。该表用于存储产品类别，包含一个category列作为主键，类型为varchar(100)，表示最大长度为 100 个字符的字符串。cursor.execute('''CREATE TABLE if not exists products(...)'''): 创建名为products的数据表，如果表已经存在则不创建。该表用于存储产品信息，包含以下列：product_id: 产品 ID，类型为int，为主键。product_name: 产品名称，类型为varchar(100)。product_rate: 产品价格，类型为int。category: 产品类别，类型为varchar(100)，引用category表中的category列，表示产品属于哪个类别。
dbconn.commit(): 提交数据库更改，将 SQL 语句执行结果保存到数据库。
-
读取初始产品数据:
cursor.execute('SELECT * FROM products'): 执行 SQL 查询，获取products表中的所有数据。prod_1 = cursor.fetchall(): 将查询结果存储到名为prod_1的列表中，每个元素是一个元组，包含产品信息。
-
设置窗口背景图片:
Background = PhotoImage(file='./images/Admin_bg.png'): 加载名为Admin_bg.png的图片，位于./images文件夹内。Bg_label = Label(Admin, image=Background): 创建一个Label小部件，显示背景图片。Bg_label.place(x=0, y=0, relwidth=1, relheight=1): 将Label放置在窗口的左上角，并将其大小设置为窗口大小。
-
创建产品表格:
table = ttk.Treeview(Admin): 创建一个Treeview小部件，用于显示产品列表，这是一个可滚动的表格。table['columns'] = ('ID','Product Name','Category', 'Rate'): 定义表格列名，分别为 'ID'、'Product Name'、'Category'、'Rate'。table.column(...): 设置每列的宽度和对齐方式。table.heading(...): 设置每列的标题文本。table.place(...): 将表格放置在窗口的中心，并设置其大小。Scroll_invoice = Scrollbar(orient='vertical',command=table.yview): 创建一个垂直滚动条，与表格联动。table.configure(yscroll=Scroll_invoice.set): 将滚动条与表格关联。Scroll_invoice.place(...): 将滚动条放置在表格的右侧。for row in prod_1: table.insert('',index='end',values=(row[0],row[1],row[3],row[2])): 将初始产品数据添加到表格中，每行代表一个产品。
-
定义退出函数:
def Exit(): ...: 定义名为Exit的函数，用于处理窗口关闭事件。sure = messagebox.askyesno('Exit','Are you sure you want to exit?', parent=Admin): 显示一个确认框，询问用户是否确认退出。if sure == True: Admin.destroy(): 如果用户确认退出，则关闭窗口。
-
创建退出按钮:
logout_img = PhotoImage(file='./images/logout.png'): 加载名为logout.png的图片，用于退出按钮。logout_button = Button(Admin, image=logout_img, ... command=Exit): 创建一个退出按钮，使用图片作为按钮图标，并将Exit函数设置为按钮的点击事件处理函数。logout_button.place(...): 将退出按钮放置在窗口的左上角。
-
创建输入框和文本变量:
cat = StringVar(): 创建一个StringVar对象，用于存储产品类别。pro_name = StringVar(): 创建一个StringVar对象，用于存储产品名称。pro_rate = StringVar(): 创建一个StringVar对象，用于存储产品价格。font_1 = Font(family='Calibri',size=15,weight='bold'): 定义一个名为font_1的字体样式，用于设置文本的字体、大小和粗细。Entry_1 = Entry(Admin,font=font_1, ... ): 创建一个Entry小部件，用于输入产品类别，设置字体样式、背景色等属性。Entry_1.place(...): 将Entry放置在窗口的指定位置。- 创建其他
Entry小部件，用于输入产品名称、价格和 ID，并设置相应的属性。
- 定义函数:
unwanted_cat(): 用于处理类别更新。add_to_cart(): 用于将新产品添加到表格和数据库中。update(): 用于更新表格和数据库中已存在的产品信息。clear(): 用于清除输入框中的内容。select_item(): 用于将表格中选中的产品信息填充到输入框中。delete_many(): 用于删除表格和数据库中选中的产品。clear_all(): 用于清空整个表格和数据库。search_id(): 用于根据产品 ID 搜索产品。
- 创建按钮:
- 创建多个
Button小部件，分别用于添加、更新、清除、选择、删除、清空和搜索产品，并设置每个按钮的文本、背景色、字体样式等属性。 - 将相应的函数设置为每个按钮的点击事件处理函数。
Button.configure(state='active')或Button.configure(state='disabled')：用于启用或禁用按钮。
- 设置窗口关闭事件处理函数:
Admin.protocol('WM_DELETE_WINDOW', Exit): 将Exit函数设置为窗口关闭事件的处理函数，确保在用户关闭窗口时执行退出操作。
- 启动主循环:
Admin.mainloop(): 启动 Tkinter 事件循环，让窗口保持运行状态，并处理用户交互事件。
代码功能概述：
这个代码创建了一个简单的产品管理 Admin Panel，使用 Tkinter 构建用户界面，并使用 SQLite 数据库存储产品信息。主要功能包括：
- 添加产品：用户可以在输入框中填写产品信息，然后点击 “Add to market” 按钮将新产品添加到表格和数据库中。
- 更新产品：用户可以选择表格中已有的产品，修改其信息，然后点击 “Update” 按钮保存更改。
- 删除产品：用户可以选择表格中需要删除的产品，然后点击 “Delete item(s)” 按钮将其从表格和数据库中移除。
- 选择产品：用户可以选择表格中需要查看的产品，点击 “Select” 按钮将产品信息填充到输入框中。
- 清除输入框：用户可以点击 “Clear” 按钮清空输入框。
- 清空所有产品：用户可以点击 “Clear All” 按钮清空整个表格和数据库。
- 搜索产品：用户可以在输入框中输入产品 ID，然后点击 “Search” 按钮进行搜索。
进一步改进：
- 使用更复杂的用户界面元素，例如下拉菜单，使界面更直观。
- 增加更多的功能，例如批量导入/导出产品数据、产品图片管理等。
- 使用更强大的数据库，例如 MySQL 或 PostgreSQL，以满足更大规模的数据存储需求。
- 优化代码，使其更易读、易维护。
- 添加错误处理和异常捕获机制，以确保程序的稳定运行。
原文地址: https://www.cveoy.top/t/topic/bfJN 著作权归作者所有。请勿转载和采集!