Python连接mysql制作的客户管理系统

客户管理系统"/>

Python连接mysql制作的客户管理系统

具备增删改查和登录功能
import pymysql.cursors
from tkinter import ttk
import tkinter as tk
import tkinter.font as tkFont
from tkinter import *
import tkinter.messagebox as messagebox
import time
connect=pymysql.Connect(host='localhost',port=3306,user='root',passwd='pananjun',db='cus',charset='utf8'
)class logincheck:def __init__(self,parent_window):parent_window.update()parent_window.destroy()self.window=tk.Tk()self.window.title("登录界面")self.window.geometry('700x600+70+50')def getTime():timeStr=time.strftime('%H:%M:%S')Rtime.configure(text=timeStr)self.window.after(1000, getTime)Rtime= Label(self.window,text='')Rtime.pack(pady=25)getTime()label = Label(self.window, text="客户管理系统登录", font=("楷体", 30))label.pack(pady=10)self.var_username=StringVar()self.var_pwd=StringVar()self.right_top_username_label = Label(text="用户名：", font=('楷体', 15)).pack(pady=15)self.right_top_username_entry = Entry(textvariable=self.var_username, font=('楷体', 15)).pack()self.right_top_pwd_label = Label(text="密码：", font=('楷体', 15)).pack(pady=15)self.right_top_pwd_entry = Entry(textvariable=self.var_pwd, font=('楷体', 15)).pack()self.right_top_button1 = ttk.Button(text='确定', width=20, command=self.new_row).pack(pady=30)self.right_top_button2 = ttk.Button(text='返回', width=20, command=self.back).pack()self.window.protocol("WM_DELETE_WINDOW", self.back)self.username=[]self.pwd=[]self.window.mainloop()db = pymysql.connect(host="localhost", user="root", passwd="pananjun", db="cus")cursor = db.cursor()sql = "SELECT * FROM 客户登录 WHERE 用户名='%s'"%(self.var_username.get())try:cursor.execute(sql)results = cursor.fetchall()for row in results:self.username.append(row[0])self.pwd.append(row[1])except:print("Error: unable to fetch data")messagebox.showinfo('警告！', '数据库连接失败！')db.close()def back(self):self.window.destroy()def new_row(self):if self.var_username.get() != '' and self.var_pwd.get() != '' and self.var_username.get()=='test' and self.var_pwd.get()=='123':db = pymysql.connect(host="localhost", user="root", passwd="pananjun", db="cus")cursor= db.cursor()sql = "SELECT * FROM 客户登录 WHERE 用户名='%s'"%(self.var_username.get())try:cursor.execute(sql)startpage(self.window)except:db.rollback()messagebox.showinfo('警告！', '数据库连接失败！')db.close()else:messagebox.showinfo('提示！', '请填写正确的用户信息')class startpage:def __init__(self,parent_window):parent_window.update()parent_window.destroy()self.window=tk.Tk()self.window.title("客户管理系统")self.window.geometry('1200x600+70+50')def getTime():timeStr=time.strftime('%H:%M:%S')Rtime.configure(text=timeStr)self.window.after(1000, getTime)Rtime= Label(self.window,text='')Rtime.pack(pady=25)getTime()label= Label(self.window,text="客户管理系统",font=("楷体",30))label.pack(pady=10)Button(self.window,text="添加客户",font=tkFont.Font(size=16),command=lambda: xinjian(self.window),width=20,height=2,fg='white',bg='gray').place(x=100,y=300)Button(self.window,text="查询",font=tkFont.Font(size=16), command=lambda: cangkucha(self.window), width=20,height=2, fg='white', bg='gray').place(x=400, y=300)Button(self.window, text="删除", font=tkFont.Font(size=16), command=lambda:shanchu(self.window), width=20,height = 2, fg = 'white', bg = 'gray').place(x=100, y=400)Button(self.window, text="退出系统", font=tkFont.Font(size=16), command=self.window.destroy, width=20,height=2, fg='white', bg='gray').place(x=400, y=400)Button(self.window, text="修改信息", font=tkFont.Font(size=16), command=lambda:xiugai(self.window),width=20,height=2,fg='white',bg='gray').place(x=700,y=300)self.window.mainloop()class xinjian:def __init__(self,parent_window):parent_window.destroy()self.window=tk.Tk()self.window.title("添加客户")self.window.geometry('700x600+70+50')self.top_title=Label(self.window,text="添加客户",bg='SkyBlue', font=('楷体', 20), width=70, height=2)self.top_title.pack()self.var_id = StringVar()self.var_name = StringVar()self.var_gender = StringVar()self.var_age = StringVar()self.var_num = StringVar()self.right_top_id_label = Label(text="客户ID号：", font=('楷体', 15)).pack(pady=15)self.right_top_id_entry = Entry(textvariable=self.var_id, font=('楷体', 15)).pack()self.right_top_name_label =Label(text="客户姓名：", font=('楷体', 15)).pack(pady=15)self.right_top_name_entry = Entry(textvariable=self.var_name, font=('楷体', 15)).pack()self.right_top_gender_label = Label(text="客户性别:", font=('楷体', 15)).pack(pady=15)self.right_top_gender_entry = Entry(textvariable=self.var_gender, font=('楷体', 15)).pack()self.right_top_age_label = Label(text="客户年龄:", font=('楷体', 15)).pack(pady=15)self.right_top_age_entry = Entry(textvariable=self.var_age, font=('楷体', 15)).pack()self.right_top_num_label = Label(text="客户电话：",font=('楷体',15)).pack(pady=15)self.right_top_num_entry = Entry(textvariable=self.var_num, font=('楷体', 15)).pack()self.right_top_button1 = ttk.Button(text='确定', width=20, command=self.new_row).pack(pady=30)self.right_top_button2 = ttk.Button(text='返回', width=20, command=self.back).pack()self.window.protocol("WM_DELETE_WINDOW", self.back)  # 捕捉右上角关闭点击self.id=[]self.name=[]self.gender=[]self.age=[]self.num=[]db = pymysql.connect(host="localhost", user="root", passwd="pananjun", db="cus")cursor = db.cursor()sql = "SELECT * FROM 客户表"try:cursor.execute(sql)results=cursor.fetchall()for row in results:self.id.append(row[0])self.name.append(row[1])self.gender.append(row[2])self.age.append(row[3])self.num.append(row[4])except:print("Error: unable to fetch data")messagebox.showinfo('警告！', '数据库连接失败！')db.close()def back(self):startpage(self.window)def new_row(self):if self.var_id.get() != '' and self.var_name.get() != '' and self.var_gender.get() != '' and self.var_age.get() != '':db=pymysql.connect(host="localhost", user="root", passwd="pananjun", db="cus")cursor = db.cursor()  # 使用cursor()方法获取操作游标sql= "INSERT INTO 客户表(客户ID号,客户姓名,客户性别,客户年龄,客户电话) VALUES('%s','%s','%s','%s','%s')"%(self.var_id.get(), self.var_name.get(),self.var_gender.get(), self.var_age.get(), self.var_num.get())try:cursor.execute(sql)dbmit()messagebox.showinfo('提示！', '添加成功！')except:db.rollback()messagebox.showinfo('警告！', '数据库连接失败！')db.close()else:messagebox.showinfo('提示！', '请填写客户信息')
class kehudan:def __init__(self,parent_window):parent_window.destroy()  # 销毁子界面self.window = tk.Tk()self.window.title('客户清单')self.window.geometry('1200x600+70+50')db = pymysql.connect(host="localhost", user="root", passwd="pananjun", db="cus")cursor = db.cursor()  # 使用cursor()方法获取操作游标sql = "SELECT * FROM 客户表"try:cursor.execute(sql)  # 执行sql语句results = cursor.fetchall()for row in results:self.id = '客户ID号: ' + row[0]self.name = '客户名称: ' + row[1]self.gender = '客户性别: ' + row[2]self.age = '客户年龄: ' + row[3]self.num = '客户电话：' + row[4]dbmit()Label(self.window, text=self.id + "\t" + self.name + "\t" + self.gender + "\t" +self.age + "\t" +self.num, font=('楷体', 18)).pack(pady=5)except:db.rollback()  # 发生错误时回滚messagebox.showinfo('警告！', '数据库连接失败！')db.close()  # 关闭数据库连接self.right_top_button4 = ttk.Button(text='返回', width=20, command=self.back).pack()self.window.protocol("WM_DELETE_WINDOW", self.back)def back(self):cangkucha(self.window)class cangkucha:def __init__(self, parent_window):parent_window.destroy()self.window = tk.Tk()self.window.title('客户查询')self.window.geometry('700x600+70+50')self.student_id = StringVar()self.id = '客户ID号:' + ''self.name = '客户姓名:' + ''self.gender = '客户性别:' + ''self.age = '客户年龄:' + ''self.num = '客户电话：' + ''Button(self.window, text="客户清单", font=tkFont.Font(size=12), command=lambda: kehudan(self.window), width=20,height=2, fg='white', bg='gray').place(x=20, y=70)self.right_top_name_label = Label(text="客户查询", font=('楷体', 15)).pack(pady=15)self.right_top_name_entry = Entry(textvariable=self.student_id, font=('楷体', 15)).pack(pady=30)self.right_top_button3 = ttk.Button(text='确定', width=20, command=self.new_row).pack(pady=30)self.right_top_button4 = ttk.Button(text='返回', width=20, command=self.back).pack()self.window.protocol("WM_DELETE_WINDOW", self.back)# 打开数据库连接db = pymysql.connect(host="localhost", user="root", passwd="pananjun", db="cus")cursor = db.cursor()sql = "SELECT * FROM 客户表 WHERE 客户ID号='%s'" % (self.student_id.get()) # SQL 查询语句try:# 执行SQL语句cursor.execute(sql)# 获取所有记录列表results = cursor.fetchall()for row in results:self.id = '客户ID号:' + row[0]self.name = '客户姓名:' + row[1]self.gender = '客户性别:' + row[2]self.age = '客户年龄:' + row[3]self.num ='客户电话:' + row[4]except:print("Error: unable to fetch data")db.close()def back(self):startpage(self.window)def new_row(self):if self.student_id.get() != '':db = pymysql.connect(host="localhost", user="root", passwd="pananjun", db="cus")cursor = db.cursor()sql = "SELECT * FROM 客户表 where 客户ID号 = '%s'" % (self.student_id.get())  # SQL 插入语句try:cursor.execute(sql)results = cursor.fetchall()for row in results:self.id = '客户ID号:' + row[0]self.name = '客户姓名:' + row[1]self.gender = '客户性别:' + row[2]self.age = '客户年龄:' + row[3]self.num ='客户电话:' + row[4]dbmit()label = tk.Label(self.window, text='客户信息查看', bg='SkyBlue', font=('楷体', 20), width=70, height=2)label.pack(pady=20)Label(self.window, text=self.id, font=('楷体', 18)).pack(pady=5)Label(self.window, text=self.name, font=('楷体', 18)).pack(pady=5)Label(self.window, text=self.gender, font=('楷体', 18)).pack(pady=5)Label(self.window, text=self.age, font=('楷体', 18)).pack(pady=5)Label(self.window, text=self.num, font=('楷体', 18)).pack(pady=5)Button(self.window, text="返回首页", width=8, font=tkFont.Font(size=12), command=self.back_1).pack(pady=150)self.window.protocol("WM_DELETE_WINDOW", self.back_1)self.window.mainloop()except:db.rollback()messagebox.showinfo('提示', '数据库连接失败！')db.close()else:messagebox.showinfo('提示', '请填写客户信息！')def back_1(self):cangkucha(self.window)
class shanchu:def __init__(self,parent_window):parent_window.destroy()self.window = tk.Tk()self.window.title('删除表')self.window.geometry('700x600+70+50')self.top_title = Label(self.window, text='删除', bg='SkyBlue', font=('楷体', 20), width=70, height=2)self.top_title.pack()self.var_id = StringVar()self.var_name = StringVar()self.var_gender = StringVar()self.var_age = StringVar()self.var_num= StringVar()self.right_top_id_label = Label(text="客户ID号", font=('楷体', 15)).pack(pady=15)self.right_top_id_entry = Entry(textvariable=self.var_id, font=('楷体', 15)).pack()self.right_top_name_label = Label(text="客户姓名", font=('楷体', 15)).pack(pady=15)self.right_top_name_entry = Entry(textvariable=self.var_name, font=('楷体', 15)).pack()self.right_top_gender_label = Label(text="客户性别", font=('楷体', 15)).pack(pady=15)self.right_top_gender_entry = Entry(textvariable=self.var_gender, font=('楷体', 15)).pack()self.right_top_age_label = Label(text="客户年龄", font=('楷体', 15)).pack(pady=15)self.right_top_age_entry = Entry(textvariable=self.var_age, font=('楷体', 15)).pack()self.right_top_num_label= Label(text="客户电话", font=('楷体', 15)).pack(pady=15)self.right_top_num_entry = Entry(textvariable=self.var_num, font=('楷体', 15)).pack()self.right_top_button1 = ttk.Button(text='确定', width=20, command=self.new_row).pack(pady=30)self.right_top_button2 = ttk.Button(text='返回', width=20, command=self.back).pack()self.window.protocol("WM_DELETE_WINDOW", self.back)self.id = []self.name = []self.gender = []self.age = []self.num= []# 打开数据库连接db = pymysql.connect(host="localhost", user="root", passwd="pananjun", db="cus")cursor = db.cursor()  # 使用cursor()方法获取操作游标sql = "SELECT * FROM 客户表"  # SQL 查询语句try:# 执行SQL语句cursor.execute(sql)# 获取所有记录列表results = cursor.fetchall()for row in results:self.id.append(row[0])self.name.append(row[1])self.gender.append(row[2])self.age.append(row[3])self.num.append(row[4])except:print("Error: unable to fetch data")messagebox.showinfo('提示', '数据库连接失败！')db.close()def back(self):startpage(self.window)def new_row(self):if self.var_id.get() != '' and self.var_name.get() != '':db = pymysql.connect(host="localhost", user="root", passwd="pananjun", db="cus")cursor = db.cursor()sql = "DELETE FROM 客户表 WHERE 客户ID号 = '%s'" % (self.var_id.get())try:cursor.execute(sql)dbmit()messagebox.showinfo('提示！', '删除成功！')except:db.rollback()messagebox.showinfo('警告！', '数据库连接失败！')db.close()else:messagebox.showinfo('警告！', '填写删除信息')
class xiugai:def __init__(self,parent_window):parent_window.destroy()self.window = tk.Tk()self.window.title('修改信息')self.window.geometry('700x600+70+50')self.top_title = Label(self.window, text='修改信息', bg='SkyBlue', font=('楷体', 20), width=70, height=2)self.top_title.pack()self.var_id = StringVar()self.var_name = StringVar()self.var_gender = StringVar()self.var_age = StringVar()self.var_num = StringVar()self.right_top_id_label = Label(text="客户ID号", font=('楷体', 15)).pack(pady=15)self.right_top_id_entry = Entry(textvariable=self.var_id, font=('楷体', 15)).pack()self.right_top_name_label = Label(text="客户姓名", font=('楷体', 15)).pack(pady=15)self.right_top_name_entry = Entry(textvariable=self.var_name, font=('楷体', 15)).pack()self.right_top_gender_label = Label(text="客户性别", font=('楷体', 15)).pack(pady=15)self.right_top_gender_entry = Entry(textvariable=self.var_gender, font=('楷体', 15)).pack()self.right_top_age_label = Label(text="客户年龄", font=('楷体', 15)).pack(pady=15)self.right_top_age_entry = Entry(textvariable=self.var_age, font=('楷体', 15)).pack()self.right_top_num_label = Label(text="客户电话", font=('楷体', 15)).pack(pady=15)self.right_top_num_entry = Entry(textvariable=self.var_num, font=('楷体', 15)).pack()self.right_top_button1 = ttk.Button(text='确定', width=20, command=self.new_row).pack(pady=30)self.right_top_button2 = ttk.Button(text='返回', width=20, command=self.back).pack()self.window.protocol("WM_DELETE_WINDOW", self.back)self.id = []self.name = []self.gender = []self.age = []self.num = []# 打开数据库连接db = pymysql.connect(host="localhost", user="root", passwd="pananjun", db="cus")cursor = db.cursor()  # 使用cursor()方法获取操作游标sql = "SELECT * FROM 客户表"  # SQL 查询语句try:# 执行SQL语句cursor.execute(sql)# 获取所有记录列表results = cursor.fetchall()for row in results:self.id.append(row[0])self.name.append(row[1])self.gender.append(row[2])self.age.append(row[3])self.num.append(row[4])except:print("Error: unable to fetch data")messagebox.showinfo('提示', '数据库连接失败！')db.close()def back(self):startpage(self.window)def new_row(self):if self.var_id.get() != '':db = pymysql.connect(host="localhost", user="root", passwd="pananjun", db="cus")cursor = db.cursor()sql = "UPDATE  客户表 SET 客户ID号='%s',客户姓名='%s',客户性别='%s',客户年龄='%s',客户电话='%s' WHERE 客户ID号='%s'" % (self.var_id.get(),self.var_name.get(),self.var_gender.get(),self.var_age.get(),self.var_num.get(),self.var_id.get())try:cursor.execute(sql)dbmit()messagebox.showinfo('提示！', '修改成功！')except:db.rollback()messagebox.showinfo('警告！', '数据库连接失败！')db.close()else:messagebox.showinfo('警告！', '填写修改信息')if __name__ == '__main__':window = tk.Tk()logincheck(window)