文章目录

• 一、系统需求分析
• 二、系统设计
• • 1. 功能结构设计
  • 2、概念设计
  • • 2.2.1 bill_food表E-R图
    • 2.2.2 bills表E-R图
    • 2.2.3 categories E-R图
    • 2.2.4 discounts表 E-R图
    • 2.2.5 emp表E-R图
    • 2.2.6 food 表E-R图
    • 2.2.7 member表E-R图
    • 2.2.8 member_point_bill表E-R图
    • 2.2.9 servers表E-R图
    • 2.2.10 tables表E-R图
    • 2.2.11 user表E-R图
  • 3. 逻辑设计（表的设计）
  • • ①bill_food表
    • ②bills表
    • ③categories表
    • ④discounts表
    • ⑤emp表
    • ⑥food表
    • ⑦member表
    • ⑧member_point_bills表
    • ⑨servers表
    • ⑩tables表
    • 11.user表
• 三、 系统实现（主要体现以下几部分）
• • 1、系统采用的技术、方法、工具
  • 2、效果图
  • 3、实现代码
  • • 2.1 main方法
    • 2.2 后台登录页面
    • 2.3 统计页面
    • 2.4 点餐系统
    • 2.5 后台页面
    • 2.6 统计系统
    • 2.7 登录页面连接数据库
    • 2.8 其他页面连接数据库
• 四、源码获取

一、系统需求分析

需求分析首先要确定研究分析的对象，这里的需求分析对象有两方：买家和卖家。

对于买家，需求是能够进行线上点餐，具体可以细化为：能够在线上获得餐品信息和进行点餐行为。买家作为消费者，都想得到更为优质的服务体验，希望能够通过较为简单顺利的操作就可以吃到满足自己口味的菜肴。这就要求系统界面能够生动形象地有效呈现出点餐的各项信息，菜品的价格，可以选择就坐的餐桌的数目等情况以及加餐时简洁的操作界面。

对于卖家，需求是能够对菜品进行增查删改以及增加会员，具体可以细化为，能够线上获得全部的菜品信息，添加菜品，修改菜品价格，删除菜品，增加会员。卖家即为商家，商家要能够高效地获取这些的信息，当然是图形化界面和几何形式的信息呈现最为直接明了。

二、系统设计

1. 功能结构设计

2、概念设计

2.2.1 bill_food表E-R图

2.2.2 bills表E-R图

2.2.3 categories E-R图

2.2.4 discounts表 E-R图

2.2.5 emp表E-R图

2.2.6 food 表E-R图

2.2.7 member表E-R图

2.2.8 member_point_bill表E-R图

2.2.9 servers表E-R图

2.2.10 tables表E-R图

2.2.11 user表E-R图

3. 逻辑设计（表的设计）

①bill_food表

CREATE TABLE `bill_food` (`id_food` int NOT NULL,`id_bill` int NOT NULL,`num` int NOT NULL,PRIMARY KEY (`id_food`,`id_bill`),KEY `FK_bill_food2` (`id_bill`),CONSTRAINT `FK_bill_food` FOREIGN KEY (`id_food`) REFERENCES `food` (`id_food`) ON DELETE RESTRICT ON UPDATE RESTRICT,CONSTRAINT `FK_bill_food2` FOREIGN KEY (`id_bill`) REFERENCES `bills` (`id_bill`) ON DELETE RESTRICT ON UPDATE RESTRICT) ;

②bills表

CREATE TABLE `bills` (`id_bill` int NOT NULL,`id_table` int NOT NULL,`id_member` int DEFAULT NULL,`time_order` datetime NOT NULL,`time_pay` datetime DEFAULT NULL,`money` int NOT NULL,PRIMARY KEY (`id_bill`),KEY `FK_bill_member` (`id_member`),KEY `FK_table_bill` (`id_table`),KEY `time_order` (`time_order`),CONSTRAINT `FK_bill_member` FOREIGN KEY (`id_member`) REFERENCES `member` (`id_member`) ON DELETE RESTRICT ON UPDATE RESTRICT,CONSTRAINT `FK_table_bill` FOREIGN KEY (`id_table`) REFERENCES `tables` (`id_table`) ON DELETE RESTRICT ON UPDATE RESTRICT) ;

③categories表

CREATE TABLE `categories` (`category` char(20) NOT NULL,PRIMARY KEY (`category`));

④discounts表

CREATE TABLE `discounts` (`id_discount` int NOT NULL,`off_price` int NOT NULL,`require_points` int NOT NULL,PRIMARY KEY (`id_discount`)) ;

⑤emp表

CREATE TABLE `emp` (`id_emp` int NOT NULL,`id_server` int DEFAULT NULL,`name_emp` char(20) NOT NULL,`sex_emp` char(1) DEFAULT NULL,`phone_num` char(11) DEFAULT NULL,`position` char(20) DEFAULT NULL,PRIMARY KEY (`id_emp`));

⑥food表

CREATE TABLE `food` (`id_food` int NOT NULL,`category` char(20) NOT NULL,`name_food` char(20) NOT NULL,`introduction` char(100) DEFAULT NULL,`price` int NOT NULL,`url` char(100) DEFAULT NULL,PRIMARY KEY (`id_food`),KEY `FK_food_category` (`category`),CONSTRAINT `FK_food_category` FOREIGN KEY (`category`) REFERENCES `categories` (`category`) ON DELETE RESTRICT ON UPDATE RESTRICT) ;

⑦member表

CREATE TABLE `member` (`id_member` int NOT NULL,`name_member` char(20) DEFAULT NULL,`points` int NOT NULL,`sex` char(1) DEFAULT NULL,`phone_num` char(11) DEFAULT NULL,PRIMARY KEY (`id_member`)) ;

⑧member_point_bills表

CREATE TABLE `member_point_bill` (`id_point_bill` int NOT NULL,`id_member` int NOT NULL,`time_point` datetime NOT NULL,`point` int NOT NULL,`note` char(20) DEFAULT NULL,PRIMARY KEY (`id_point_bill`),KEY `FK_member_point_bill` (`id_member`),CONSTRAINT `FK_member_point_bill` FOREIGN KEY (`id_member`) REFERENCES `member` (`id_member`) ON DELETE RESTRICT ON UPDATE RESTRICT) ;

⑨servers表

CREATE TABLE `servers` (`id_server` int NOT NULL,`id_emp` int NOT NULL,PRIMARY KEY (`id_server`),KEY `FK_to_server` (`id_emp`),CONSTRAINT `FK_to_server` FOREIGN KEY (`id_emp`) REFERENCES `emp` (`id_emp`) ON DELETE RESTRICT ON UPDATE RESTRICT) ;

⑩tables表

CREATE TABLE `tables` (`id_table` int NOT NULL,`id_server` int NOT NULL,`num_people` int NOT NULL,`id_bill` int DEFAULT NULL,`id_member` int DEFAULT NULL,PRIMARY KEY (`id_table`),KEY `FK_server_table` (`id_server`),CONSTRAINT `FK_server_table` FOREIGN KEY (`id_server`) REFERENCES `servers` (`id_server`) ON DELETE RESTRICT ON UPDATE RESTRICT) ;

11.user表

CREATE TABLE `user` (`user_id` varchar(4) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,`user_password` varchar(255) NOT NULL,`user_name` varchar(255) DEFAULT NULL,`user_position` varchar(255) DEFAULT NULL,PRIMARY KEY (`user_id`));

三、 系统实现（主要体现以下几部分）

1、系统采用的技术、方法、工具

餐厅点餐系统采用pycharm tkinter库实现可视化，数据库管理用MySQL

2、效果图

其他图就不展示了

3、实现代码

2.1 main方法

def is_number(s):try:float(s)return Trueexcept ValueError:passtry:import unicodedataunicodedata.numeric(s)return Trueexcept (TypeError, ValueError):passreturn False# windowimport tkinter.messageboximport tkinter as tk# 使用Tkinter前需要先导入import orderimport statisticfrom database import *import reig_managetable = db_get_table()#实例化window_table = tk.Tk()window_table.title('选择餐桌')window_table.geometry('300x400')window_table['bg']='#d0c0c0'listbox_table = tk.Listbox(window_table, listvariable = table)#listbox_table['bg']='#9ea4b8'for table_item in table:listbox_table.insert("end", table_item+" "+table[table_item][0])listbox_table.pack()#e_member = tk.Entry(window_table, show=None, font=('Arial', 14))e_member.insert(0,"输入会员号")e_member['bg']='#f8f0e0'e_member.pack()def submit_table():if listbox_table.curselection() == ():tkinter.messagebox.showinfo(title='警告', message='请点击框内餐桌再选择')returntable_item = listbox_table.get(listbox_table.curselection())occupied = table[table_item[0:3]][0]if occupied=="占用":tkinter.messagebox.showinfo(title='警告', message='当前餐桌有人')returntable_num = int(table_item[2:3])next_bill = db_sit(table_num)window_table.withdraw()member_id = e_member.get()if is_number(member_id):member_id = int(member_id)else:member_id = Noneorder.open_order_table(table_num,next_bill,member_id)def enter_statistic():#window_table.withdraw()statistic.open()#登录def enter_manage(): # window_table.withdraw()reig_manage.register_manage()#打样def close_shop():db_clear_table()table = db_get_table()listbox_table.delete(0,"end")for table_item in table:listbox_table.insert("end", table_item+" "+table[table_item][0])button_select_table = tk.Button(window_table, text='选择', width=15, height=2, command=submit_table)button_select_table.pack()button_statistic = tk.Button(window_table, text='进入后台统计界面', width=15, height=2, command=enter_statistic)button_statistic.pack()button_statistic = tk.Button(window_table, text='管理人员登录', width=15, height=2, command=enter_manage)button_statistic.pack()button_statistic = tk.Button(window_table, text='打烊', width=15, height=2, command=close_shop)button_statistic.pack()# 第7步，主窗口循环显示window_table.mainloop()

2.2 后台登录页面

import tkinter as tk# 使用Tkinter前需要先导入from tkinter import messageboxfrom database_manage import *import managefrom tkinter import *def register_manage():window = tk.Toplevel()window.title('后台登录页面')window['bg'] = '#d0c0c0'window.geometry('300x300')Label(window, text='管理人员登录').grid(row=0, column=0, columnspan=2)Label(window, text='用户名：').grid(row=1, column=0)name = Entry(window)name.grid(row=1, column=1)Label(window, text='密码：').grid(row=2, column=0, sticky=E)passwd = Entry(window, show='*')passwd.grid(row=2, column=1)def successful():falg=db_get_user111(name,passwd)if falg==1:window.destroy()manage.show()else:messagebox.showerror(title='wrong', message='登录失败，用户名或密码错误')Button(window, text='登录', command=successful).grid(row=3, column=0, columnspan=2)

2.3 统计页面

import tkinter.messageboximport tkinter as tk# 使用Tkinter前需要先导入from database import *def open():# 实例化object，建立窗口windowwindow = tk.Toplevel()window.title('统计页面')window['bg']='#d0c0c0'window.geometry('300x500')data = []# 存放统计结果listbox = tk.Listbox(window, listvariable=data)# 日期输入框e_start = tk.Entry(window, show=None, font=('Arial', 14))e_start.insert(0,"起始日期")e_end = tk.Entry(window, show=None, font=('Arial', 14))# 显示成明文形式e_end.insert(0,"终止日期")label_money = tk.Label(window, width=40, text="时间段内销售总额：空（请点击查询）")def get_sales():sales = db_get_sales()listbox.delete(0,"end")for sale in sales:listbox.insert("end",sale[0]+" 销售量"+str(sale[1]))def get_sales_time():start_date = e_start.get()end_date = e_end.get()try:datetime.datetime.strptime(start_date, '%Y-%m-%d')datetime.datetime.strptime(end_date, '%Y-%m-%d')except ValueError:tkinter.messagebox.showinfo(title='警告', message='日期不合法，范例2021-02-03')returnsales = db_get_sales_time(start_date, end_date)listbox.delete(0,"end")if sales == ():tkinter.messagebox.showinfo(title='提示', message='该时间段没有销售')for sale in sales:listbox.insert("end",sale[0]+" 销售量"+str(sale[1]))def get_money_time():start_date = e_start.get()end_date = e_end.get()try:datetime.datetime.strptime(start_date, '%Y-%m-%d')datetime.datetime.strptime(end_date, '%Y-%m-%d')except ValueError:tkinter.messagebox.showinfo(title='警告', message='日期不合法，范例2021-02-03')returnmoney = db_get_money_time(start_date, end_date)if money == None:tkinter.messagebox.showinfo(title='提示', message='该时间段没有销售')returnlabel_money.config(text = "时间段内销售总额："+str(money)+"元（点击第三个按钮刷新）")button_sales = tk.Button(window, text='按菜品销量排序（总）', width=20, height=2, command=get_sales)button_sales_time = tk.Button(window, text='按时间段销量排序', width=20, height=2, command=get_sales_time)button_sales_total = tk.Button(window, text='查询某时间段内销售总额', width=20, height=2, command=get_money_time)button_sales.pack()button_sales_time.pack()button_sales_total.pack()e_start.pack()e_end.pack()listbox.pack()label_money.pack()window.mainloop()

2.4 点餐系统

import tkinter.messageboximport tkinter as tk# 使用Tkinter前需要先导入import discountfrom database import *food = {}bills = []# # bill_id = 0# member_id = 3# # server_id = 7# member_cent = 0# server_id = 0food = db_get_all_food()def open_order_table(table_id,bill_id,member_id):print("member_id:"+str(member_id))member_point = db_get_member_point(member_id)server_id = db_get_server_id(table_id)db_get_server_id(table_id)# 第1步，实例化object，建立窗口windowwindow = tk.Toplevel()window['bg']='#d0c0c0'window.title('点餐系统')window.geometry('300x800')#在图形界面上创建一个标签label用以显示并放置var = tk.StringVar()# 定义一个var用来将radiobutton的值和Label的值联系在一起.var.set("川菜")label_server = tk.Label(window, bg='#b8b0b0', width=20, text=str(server_id)+"号服务员为您服务")label_server.pack()label_food = tk.Label(window, bg='#b8b0b0', width=20, text="川菜")label_food.pack()label_order = tk.Label(window, bg='#b8b0b0', width=20, text="订单 " + "0元")# 对应菜品的显示listbox = tk.Listbox(window, listvariable=food["川菜"])for food_item in food[var.get()]:listbox.insert("end", food_item)listbox_bill = tk.Listbox(window, listvariable=bills)# 定义选项触发函数功能def print_category():#print(var.get())label_food.config(text=var.get())listbox.delete(0, "end")for food_item in food[var.get()]:listbox.insert("end", food_item)def add_bill(food_item):bills.append(food_item)bill_money=db_add_bill(bill_id,food_item.split(' ')[0])listbox_bill.insert("end", food_item)label_order.config(text="订单 " + str(bill_money) + "元")def submit_bill():listbox_bill.delete(0, "end")item_num = 0label_order.config(text="订单 " + str(item_num) + "元")db_submit_bill(member_id,bill_id)window.destroy()discount.open(member_point,member_id)def submit_food():if listbox.curselection() == ():tkinter.messagebox.showinfo(title='警告', message='请点击框内菜品才添加')returnprint(listbox.curselection())food_item = listbox.get(listbox.curselection())add_bill(food_item)# 创建三个radiobutton选项，其中variable=var, value='A'的意思就是，当我们鼠标选中了其中一个选项，把value的值A放到变量var中，然后赋值给variablefor category in food:radiobutton = tk.Radiobutton(window, text=category, variable=var, value=category, command=print_category)radiobutton.pack()listbox.pack()button_submit = tk.Button(window, text='添加菜品', width=15, height=2, command=submit_food)button_submit.pack()label_order.pack()listbox_bill.pack()button_pay = tk.Button(window, text='结账', width=15, height=2, command=submit_bill)button_pay.pack()label_member_id = tk.Label(window, width=20, text="会员号："+str(member_id))label_member_id.pack()label_server_id = tk.Label(window, width=20, text="服务员：" + str(server_id))label_server_id.pack()label_cent = tk.Label(window, width=20, text="积分：" + str(member_point))label_cent.pack()window.mainloop()

2.5 后台页面

import database_managefrom tkinter import messagebox#import mainimport tkinter as tk# 使用Tkinter前需要先导入from database_manage import *from tkinter import *def show():window = tk.Toplevel()window['bg'] = '#d0c0c0'window.title('后台页面')window.geometry('250x250')def inquire_menu():window_menu = tk.Toplevel()window_menu['bg'] = '#d0c0c0'window_menu.title('所有菜品页面')window_menu.geometry('220x230')food = {}food=database_manage.db_get_food()var = tk.StringVar()listbox = tk.Listbox(window_menu, listvariable=food)#listbox.Text(window,wigth=100,height=300)listbox.grid(row=0, column=6,ipadx=30,ipady=10,columnspan=5,rowspan=5)listbox.insert("end", "id:"+" 类别： "+" 名称："+" 价格：")for food_item in food:#listbox.insert("end", food[food_item][0])listbox.insert("end", food[food_item][0]+""+food[food_item][1]+""+food[food_item][2]+""+food[food_item][3])def add_menu():window_add = tk.Toplevel()window_add['bg'] = '#d0c0c0'window_add.title('添加菜品页面')window_add.geometry('300x200')Label(window_add, text='id_food').grid(row=1, column=0)id = Entry(window_add)id.grid(row=1, column=1)Label(window_add, text='category').grid(row=2, column=0)category = Entry(window_add)category.grid(row=2, column=1)Label(window_add, text='name').grid(row=3, column=0)name = Entry(window_add)name.grid(row=3, column=1)Label(window_add, text='price').grid(row=4, column=0)price = Entry(window_add)price.grid(row=4, column=1)def add():falg=db_get_all_categories(category)if(falg==1):ret=db_get_add(id,category,name,price)if(ret==1):messagebox.showinfo(title='successful', message='添加成功')else:messagebox.showinfo(title='失败', message='由于food表的外键约束，不能在pycharm里用语句添加')else:messagebox.showinfo(title='失败', message='category错误')Button(window_add, text='添加', command=add).grid(row=6, column=2, columnspan=2)def alter_menu():window_alter = tk.Toplevel()window_alter['bg'] = '#d0c0c0'window_alter.title('修改菜品页面')window_alter.geometry('300x200')Label(window_alter, text='菜品名称').grid(row=1, column=0)name = Entry(window_alter)name.grid(row=1, column=1)Label(window_alter, text='菜品价格').grid(row=2, column=0)price = Entry(window_alter)price.grid(row=2, column=1)def alters():falg = db_alter(name,price)if falg == 1:messagebox.showinfo(title='successful', message='修改成功')else:messagebox.showinfo(title='失败', message='修改失败')Button(window_alter, text='修改', command=alters).grid(row=6, column=2, columnspan=2)def delete_menu():window_delete = tk.Toplevel()window_delete['bg'] = '#d0c0c0'window_delete.title('删除菜品页面')window_delete.geometry('300x200')Label(window_delete, text='菜品名称').grid(row=1, column=0)name = Entry(window_delete)name.grid(row=1, column=1)def deletes():falg = db_delete(name)if falg == 1:messagebox.showinfo(title='successful', message='删除成功')else:messagebox.showinfo(title='失败', message='由于food表的外键约束，不能在pycharm里用语句删除')Button(window_delete, text='删除', command=deletes).grid(row=6, column=2, columnspan=2)def add_member():window_addm = tk.Toplevel()window_addm['bg'] = '#d0c0c0'window_addm.title('增加会员页面')window_addm.geometry('300x200')Label(window_addm, text='id_member').grid(row=0, column=0)member = Entry(window_addm)member.grid(row=0, column=1)Label(window_addm, text='name').grid(row=1, column=0)name = Entry(window_addm)name.grid(row=1, column=1)Label(window_addm, text='sex').grid(row=2, column=0)sex = Entry(window_addm)sex.grid(row=2, column=1)Label(window_addm, text='phone').grid(row=3, column=0)phone = Entry(window_addm)phone.grid(row=3, column=1)def adds():falg = db_add_member(member,name,sex,phone)if falg == 1:messagebox.showinfo(title='successful', message='增加成功')else:messagebox.showinfo(title='失败', message='增加失败')Button(window_addm, text='增加', command=adds).grid(row=6, column=2, columnspan=2)tk.Button(window, text='查询所有菜品', width=15, height=2,command=inquire_menu).grid(row=0, column=1)tk.Button(window, text='添加菜品', width=15, height=2,command=add_menu).grid(row=1, column=1)tk.Button(window, text='修改菜品价格', width=15, height=2,command=alter_menu).grid(row=2, column=1)tk.Button(window, text='删除菜品', width=15, height=2,command=delete_menu).grid(row=3, column=1)tk.Button(window, text='增加会员', width=15, height=2, command=add_member).grid(row=4, column=1)window.mainloop()

2.6 统计系统

import tkinter.messageboximport tkinter as tk# 使用Tkinter前需要先导入from database import *def open(points,member_id):# 第1步，实例化object，建立窗口windowwindow = tk.Toplevel()# 第2步，给窗口的可视化起名字window.title('统计系统')# 第3步，设定窗口的大小(长 * 宽)window.geometry('300x500')window['bg']='#d0c0c0'# 优惠label_discount = tk.Label(window, bg='#b8b0b0', width=30, text ="选择优惠")label_discount.pack()# 存放统计结果discount = db_get_discountlist()print(discount)listbox = tk.Listbox(window, listvariable=discount)for discount_item in discount:if points < discount[discount_item][1]: # 积分不够规则所需continueoff_price = str(discount[discount_item][0])require_points = str(discount[discount_item][1])listbox.insert("end","花费"+require_points+"积分获得"+off_price+"元优惠" )listbox.pack()def commit_discount():if listbox.curselection() == ():tkinter.messagebox.showinfo(title='警告', message='请点击框内优惠才提交')returndb_commit_discount(discount[listbox.curselection()[0]+1][1],member_id)cancle()def cancle():window.destroy()tkinter.messagebox.showinfo(title='结账', message='结账成功，欢迎下次再来！')# 确认优惠button_commit = tk.Button(window, text='使用', width=15, height=2, command=commit_discount)button_commit.pack()button_cancel = tk.Button(window, text='不使用', width=15, height=2, command=cancle)button_cancel.pack()window.mainloop()

2.7 登录页面连接数据库

import pymysqldef db_get_user111(name,passwd):db = pymysql.connect(host="localhost", user="root", password="123456", db="restaurant", charset="utf8")# 使用 cursor() 方法创建一个游标对象 cursorcursor = db.cursor()try:sql = """select user_id,user_password from user"""entry1 = name.get()entry2 = passwd.get()cursor.execute(sql)results = cursor.fetchall()for row in results:uid=row[0]pwd=row[1]if entry1==uid and entry2==pwd:db.close()return 1return 0except:db.rollback()db.close()return 0def db_get_food():db = pymysql.connect(host='localhost', user='root', password='123456', db="restaurant", charset="utf8")cursor = db.cursor()food = {}sql = """select id_food,category,name_food,price from food"""try:# 执行sqlcursor.execute(sql)# 处理结果集results = cursor.fetchall()for row in results:food[str(row[0])]=[str(row[0]),row[1],row[2],str(row[3])]db.close()return foodexcept Exception as e:# print(e)print('查询所有数据失败')db.rollback()db.close()return 0def db_get_all_categories(category):# 打开数据库连接db = pymysql.connect(host="localhost", user="root", password="123456", db="restaurant", charset="utf8")# 使用 cursor() 方法创建一个游标对象 cursorcursor = db.cursor()sql = """select category from categories"""try:category = category.get()cursor.execute(sql)results = cursor.fetchall()for row in results:if category == row[0]:return 1return 0except:print("wrong:db_get_all_categories")db.rollback()db.close()return 0def db_get_add(id,category,name,price):db = pymysql.connect(host='localhost', user='root', password='123456', db="restaurant", charset="utf8")cursor = db.cursor()try:sql = """insert into food(id_food,category,name_food,introduction,price,url) values(%s,%s,%s,%s,%s,%s)"""value = (id, category, name, 'null', price, 'NULL')# 执行sqlcursor.execute(sql,value)db.commit()db.close()return 1except Exception as e:print(e)db.rollback()db.close()return 0def db_alter(name,price):db = pymysql.connect(host='localhost', user='root', password='123456', db="restaurant", charset="utf8")cursor = db.cursor()try:price = price.get()name = name.get()sql = """update food set price = %s where name_food = %s"""value = ( price , name )# 执行sqlcursor.execute(sql,value)db.commit()db.close()return 1except Exception as e:print(e)db.rollback()db.close()return 0def db_delete(name):db = pymysql.connect(host='localhost', user='root', password='123456', db="restaurant", charset="utf8")cursor = db.cursor()try:name = name.get()sql = """delete from food where name_food=%s"""#value = (name)# 执行sqlcursor.execute(sql, name)db.commit()db.close()return 1except Exception as e:print(e)db.rollback()db.close()return 0def db_add_member(member,name,sex,phone):db = pymysql.connect(host='localhost', user='root', password='123456', db="restaurant", charset="utf8")cursor = db.cursor()try:member=member.get()name = name.get()sex=sex.get()phone=phone.get()sql = """insert into member(id_member,name_member,points,sex,phone_num) values(%s,%s,0,%s,%s) """value = (member,name,sex,phone)# 执行sqlcursor.execute(sql, value)db.commit()db.close()return 1except Exception as e:print(e)db.rollback()db.close()return 0

2.8 其他页面连接数据库

在这里插入代码片import datetime#import reig_manageimport pymysqldef db_get_table():# 打开数据库连接，创建一个数据库对象db = pymysql.connect(host="localhost", user="root", password="123456", db="restaurant", charset="utf8")# 使用 cursor() 方法创建一个游标对象 cursorcursor = db.cursor()sql = """select id_table, id_server, id_billfrom tables"""try:tables={}cursor.execute(sql) # 执行sql语句results = cursor.fetchall()#获取所有数据for row in results:print(row)occupied = row[2]if occupied: occupied = "占用"else:occupied = "空闲"server = row[1]tables["餐桌"+str(row[0])]=[occupied,server]db.close()return tablesexcept:print("wrong:get_table")db.rollback()db.close()return {}# 找到下一个bill的id返回，并且将其设置成当前选择的table的bill，表示入座def db_sit(table_num):# 打开数据库连接db = pymysql.connect(host="localhost", user="root", password="123456", db="restaurant", charset="utf8")# 使用 cursor() 方法创建一个游标对象 cursorcursor = db.cursor()sql = """select max(id_bill)from bills"""try:max = 0cursor.execute(sql)results = cursor.fetchall()for row in results:max = row[0]sql2 = """insert into bills(id_bill,id_table,id_member,time_order,money)values(%d,%d,NULL,"%s",0)"""% \ (max+1,table_num,datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'))cursor.execute(sql2)sql3 = """update tablesset id_bill=%dwhere id_table = %d""" % \ (max+1,table_num)cursor.execute(sql3)db.commit() #插入数据db.close()return max+1except:print("wrong:db_sit")db.rollback()db.close()return 0def db_get_server_id(table_id):# 打开数据库连接db = pymysql.connect(host="localhost", user="root", password="123456", db="restaurant", charset="utf8")# 使用 cursor() 方法创建一个游标对象 cursorcursor = db.cursor()sql = """select id_serverfrom tableswhere id_table = %d"""%(table_id)print(sql)try:server_id = 0cursor.execute(sql)results = cursor.fetchall()for row in results:server_id = row[0]db.close()return server_idexcept:print("wrong:db_get_server_id")db.rollback()db.close()return 0def db_get_all_food():# 打开数据库连接db = pymysql.connect(host="localhost", user="root", password="123456", db="restaurant", charset="utf8")# 使用 cursor() 方法创建一个游标对象 cursorcursor = db.cursor()food = {}sql = """select category from categories"""print(sql)try:cursor.execute(sql)results = cursor.fetchall()for row in results:food[row[0]]=[]sql2 = """select category, name_food, pricefrom food"""cursor.execute(sql2)results = cursor.fetchall()for row in results:food[row[0]].append(row[1]+" "+str(row[2])+"元")db.close()return foodexcept:print("wrong:db_get_all_food")db.rollback()db.close()return 0def db_add_bill(bill_id,food_name):# 打开数据库连接db = pymysql.connect(host="localhost", user="root", password="123456", db="restaurant", charset="utf8")# 使用 cursor() 方法创建一个游标对象 cursorcursor = db.cursor()food = {}try:current_money = 0sql_find_food_id = """select id_food, price from foodwhere name_food = "%s" """ %(food_name)cursor.execute(sql_find_food_id)results = cursor.fetchall()for row in results:id = row[0]price = row[1]sql_findexistsfood = """select *from bill_foodwhere id_food = %d and id_bill = %d """%(id,bill_id)cursor.execute(sql_findexistsfood)if cursor.fetchall()==():sql2 = """insert into bill_foodvalues(%d,%d,1)"""%(id,bill_id)else:sql2 = """update bill_foodset num =num +1where id_food = %d and id_bill = %d """ % (id, bill_id)cursor.execute(sql2)sql3 = """update billsset money = money+%d where id_bill = %d""" % (price, bill_id)cursor.execute(sql3)db.commit()sql4 = """select moneyfrom billswhere id_bill = %d""" % (bill_id)cursor.execute(sql4)results = cursor.fetchall()for row in results:current_money = row[0]print(current_money)db.close()return current_moneyexcept:print("wrong:db_add_bill")db.rollback()db.close()return 0def db_submit_bill(member_id,id_bill):# 打开数据库连接db = pymysql.connect(host="localhost", user="root", password="123456", db="restaurant", charset="utf8")# 使用 cursor() 方法创建一个游标对象 cursorcursor = db.cursor()try:sql = """update tablesset id_bill = NULLwhere id_bill = %d"""%(id_bill)cursor.execute(sql)sql2 = """update billsset time_pay = "%s"where id_bill = %d"""%(datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S'),id_bill)cursor.execute(sql2)if member_id != None:sql3 = """select moneyfrom billswhere id_bill = %d""" % (id_bill)cursor.execute(sql3)results = cursor.fetchall()for row in results:current_money = row[0]sql4 = """update memberset points = points+ %swhere id_member = %d""" % (current_money,member_id)cursor.execute(sql4)db.commit()db.close()except:print("wrong:db_submit_bill")db.rollback()db.close()def db_get_sales():# 打开数据库连接db = pymysql.connect(host="localhost", user="root", password="123456", db="restaurant", charset="utf8")# 使用 cursor() 方法创建一个游标对象 cursorcursor = db.cursor()try:sql = """SELECT name_food, sum(num)FROM bill_food natural join foodgroup by id_foodorder by sum(num) desc"""cursor.execute(sql)results = cursor.fetchall()db.close()return resultsexcept:print("wrong:db_get_sales")db.rollback()db.close()return ()def db_get_sales_time(start_time, end_time):# 打开数据库连接db = pymysql.connect(host="localhost", user="root", password="123456", db="restaurant", charset="utf8")# 使用 cursor() 方法创建一个游标对象 cursorcursor = db.cursor()try:sql = """SELECT name_food, sum(num)FROM bill_food natural join bills natural join foodwhere time_pay between "%s 00:00:00" and "%s 00:00:00"group by id_foodorder by sum(num) desc;"""%(start_time,end_time)cursor.execute(sql)results = cursor.fetchall()db.close()return resultsexcept:print("wrong:db_get_sales_time")db.rollback()db.close()return ()def db_get_money_time(start_time, end_time):# 打开数据库连接db = pymysql.connect(host="localhost", user="root", password="123456", db="restaurant", charset="utf8")# 使用 cursor() 方法创建一个游标对象 cursorcursor = db.cursor()try:sql = """SELECT sum(money)FROM billswhere time_pay between "%s 00:00:00" and "%s 00:00:00"""" % (start_time, end_time)cursor.execute(sql)results = cursor.fetchall()for row in results:return row[0]db.close()return 0except:print("wrong:db_get_money_time")db.rollback()db.close()return 0def db_clear_table():# 打开数据库连接db = pymysql.connect(host="localhost", user="root", password="123456", db="restaurant", charset="utf8")# 使用 cursor() 方法创建一个游标对象 cursorcursor = db.cursor()try:sql = """update tablesset id_bill = NULLwhere id_table  100"""cursor.execute(sql)db.commit()db.close()except:print("wrong:db_clear_table")db.rollback()db.close()def db_get_member_point(member_id):# 打开数据库连接db = pymysql.connect(host="localhost", user="root", password="123456", db="restaurant", charset="utf8")# 使用 cursor() 方法创建一个游标对象 cursorcursor = db.cursor()try:if member_id == None:return 0sql = """select pointsfrom memberwhere id_member = %s"""%(member_id)cursor.execute(sql)results = cursor.fetchall()if results == ():sql2 = """insert into membervalues(%s,null,0,null,null)""" % (member_id)cursor.execute(sql2)db.commit()db.close()return 0db.close()for row in results:return row[0]except:print("wrong:db_ensure_member_id")db.rollback()db.close()def db_get_discountlist():# 打开数据库连接db = pymysql.connect(host="localhost", user="root", password="123456", db="restaurant", charset="utf8")# 使用 cursor() 方法创建一个游标对象 cursorcursor = db.cursor()discount = {}sql = """select * from discounts"""print(sql)try:cursor.execute(sql)results = cursor.fetchall()for row in results:discount[row[0]] = [row[1],row[2]]db.close()return discountexcept:print("wrong:db_get_discountlist")db.rollback()db.close()return 0def db_commit_discount(points,member_id):# 打开数据库连接db = pymysql.connect(host="localhost", user="root", password="123456", db="restaurant", charset="utf8")# 使用 cursor() 方法创建一个游标对象 cursorcursor = db.cursor()discount = {}sql = """update memberset points = points - %swhere id_member = %s"""%(points,member_id)try:cursor.execute(sql)db.commit()db.close()except:print("wrong:db_commit_discount")db.rollback()db.close()return 0

四、源码获取

餐厅点餐系统