从零开始构建员工管理系统：Python与SQLite3的完美结合

引言

在现代企业中，有效的员工管理是确保组织高效运作的关键。一个简单的员工管理系统可以帮助你轻松地查询、添加、删除和统计员工信息。本文将带你一步步构建一个基于Python和Tkinter的图形界面员工管理系统（EMS, Employment Management System）。我们将涵盖数据库设计、图形用户界面创建以及核心功能的实现。

环境准备

确保你已经安装了Python和相关的库。你可以使用以下命令来安装所需的库：

pip install tkinter sqlite3

数据库设计

我们使用SQLite数据库来存储员工信息。假设每个员工有以下字段：

• id (主键，自增)
• name (姓名)
• gender (性别)
• age (年龄)
• department (部门)
• position (职务)
• salary (工资)

创建数据库和表

首先，我们需要创建一个SQLite数据库并定义表结构。

import sqlite3

def get_db_connection():
    conn = sqlite3.connect('employees.db')
    return conn

def create_db():
    conn = get_db_connection()
    c = conn.cursor()
    c.execute('''CREATE TABLE IF NOT EXISTS employees
                 (id INTEGER PRIMARY KEY AUTOINCREMENT,
                 name TEXT NOT NULL,
                 gender TEXT NOT NULL,
                 age INTEGER NOT NULL,
                 department TEXT NOT NULL,
                 position TEXT NOT NULL,
                 salary REAL NOT NULL)''')
    conn.commit()
    conn.close()

create_db()

核心功能实现

查询所有员工

def query_employees():
    conn = get_db_connection()
    c = conn.cursor()
    c.execute("SELECT * FROM employees")
    rows = c.fetchall()
    conn.close()
    return rows

根据姓名或ID查询员工

def query_employee_by_name_or_id(name_or_id):
    conn = get_db_connection()
    c = conn.cursor()
    if name_or_id.isdigit():
        c.execute("SELECT * FROM employees WHERE id=?", (int(name_or_id),))
    else:
        c.execute("SELECT * FROM employees WHERE name=?", (name_or_id,))
    rows = c.fetchall()
    conn.close()
    return rows

检查是否存在完全重复的员工

def check_duplicate_employee(name, gender, age, department, position, salary):
    conn = get_db_connection()
    c = conn.cursor()
    c.execute("SELECT * FROM employees WHERE name=? AND gender=? AND age=? AND department=? AND position=? AND salary=?", (name, gender, age, department, position, salary))
    rows = c.fetchall()
    conn.close()
    return len(rows) > 0

添加新员工

def add_employee(name, gender, age, department, position, salary):
    if not all([name, gender, age, department, position, salary]):
        messagebox.showwarning("Input Error", "All fields must be filled.")
        return False
    if check_duplicate_employee(name, gender, age, department, position, salary):
        if not messagebox.askyesno("Duplicate Entry", "This employee already exists. Do you want to add it anyway?"):
            return False
    conn = get_db_connection()
    c = conn.cursor()
    c.execute("INSERT INTO employees (name, gender, age, department, position, salary) VALUES (?, ?, ?, ?, ?, ?)",
              (name, gender, age, department, position, round(float(salary), 2)))
    conn.commit()
    conn.close()
    return True

删除员工

def delete_employee(employee_id):
    conn = get_db_connection()
    c = conn.cursor()
    c.execute("DELETE FROM employees WHERE id=?", (employee_id,))
    conn.commit()
    conn.close()

统计员工数量

def count_employees():
    conn = get_db_connection()
    c = conn.cursor()
    c.execute("SELECT COUNT(*) FROM employees")
    count = c.fetchone()[0]
    conn.close()
    return count

图形用户界面

接下来，我们使用Tkinter创建图形用户界面。

import tkinter as tk
from tkinter import messagebox, simpledialog

class EmployeeManagementSystem:
    def __init__(self, root):
        self.root = root
        self.root.title("员工管理系统-V1.0")

        # 创建标签和输入框
        self.name_label = tk.Label(root, text="姓名:")
        self.name_label.grid(row=0, column=0, padx=5, pady=5)
        self.name_entry = tk.Entry(root)
        self.name_entry.grid(row=0, column=1, padx=5, pady=5)

        self.gender_label = tk.Label(root, text="性别:")
        self.gender_label.grid(row=1, column=0, padx=5, pady=5)
        self.gender_entry = tk.Entry(root)
        self.gender_entry.insert(0, "男")  # 设置默认值为“男”
        self.gender_entry.grid(row=1, column=1, padx=5, pady=5)

        self.age_label = tk.Label(root, text="年龄:")
        self.age_label.grid(row=2, column=0, padx=5, pady=5)
        self.age_entry = tk.Entry(root)
        self.age_entry.grid(row=2, column=1, padx=5, pady=5)

        self.department_label = tk.Label(root, text="部门:")
        self.department_label.grid(row=3, column=0, padx=5, pady=5)
        self.department_entry = tk.Entry(root)
        self.department_entry.grid(row=3, column=1, padx=5, pady=5)

        self.position_label = tk.Label(root, text="职务:")
        self.position_label.grid(row=4, column=0, padx=5, pady=5)
        self.position_entry = tk.Entry(root)
        self.position_entry.grid(row=4, column=1, padx=5, pady=5)

        self.salary_label = tk.Label(root, text="工资:")
        self.salary_label.grid(row=5, column=0, padx=5, pady=5)
        self.salary_entry = tk.Entry(root)
        self.salary_entry.grid(row=5, column=1, padx=5, pady=5)

        # 创建按钮
        self.add_button = tk.Button(root, text="添加员工", command=self.add_employee)
        self.add_button.grid(row=7, column=0, padx=5, pady=5)

        self.query_button = tk.Button(root, text="查询员工（Name/ID）", command=self.query_employee_by_name_or_id)
        self.query_button.grid(row=7, column=1, padx=5, pady=5)

        self.delete_button = tk.Button(root, text="删除员工", command=self.delete_employee)
        self.delete_button.grid(row=7, column=2, padx=5, pady=5)

        self.count_button = tk.Button(root, text="统计员工", command=self.count_employees)
        self.count_button.grid(row=7, column=3, padx=5, pady=5)

        # 创建结果显示区域
        self.result_text = tk.Text(root, height=10, width=90, bg='black', fg='green')
        self.result_text.grid(row=8, column=0, columnspan=4, padx=5, pady=5)

    def add_employee(self):
        name = self.name_entry.get()
        gender = self.gender_entry.get()
        age = self.age_entry.get()
        department = self.department_entry.get()
        position = self.position_entry.get()
        salary = self.salary_entry.get()

        if not all([name, gender, age, department, position, salary]):
            messagebox.showwarning("Input Error", "Data is empty!")
            return

        try:
            age = int(age)
            salary = float(salary)
        except ValueError:
            messagebox.showwarning("Input Error", "Age and Salary must be numeric.")
            return

        if add_employee(name, gender, age, department, position, round(salary, 2)):
            messagebox.showinfo("Success", "Employee added successfully!")
        else:
            # 如果用户取消了添加操作，不显示任何消息框
            pass

    def query_employee_by_name_or_id(self):
        name_or_id = simpledialog.askstring("Input", "Enter the Name or ID to query:")
        if name_or_id:
            employees = query_employee_by_name_or_id(name_or_id)
            self.display_employees(employees)

    def display_employees(self, employees):
        self.result_text.delete(1.0, tk.END)
        if not employees:
            self.result_text.insert(tk.END, "No employees found.\n")
        else:
            for employee in employees:
                self.result_text.insert(tk.END, f"ID: {employee[0]}, Name: {employee[1]}, Gender: {employee[2]}, Age: {employee[3]}, Department: {employee[4]}, Position: {employee[5]}, Salary: {employee[6]:.2f}\n")

    def delete_employee(self):
        employee_id = simpledialog.askinteger("Input", "Enter the ID of the employee to delete:")
        if employee_id is not None:
            delete_employee(employee_id)
            messagebox.showinfo("Success", "Employee deleted successfully!")

    def count_employees(self):
        count = count_employees()
        messagebox.showinfo("Employee Count", f"Total number of employees: {count}")

if __name__ == "__main__":
    root = tk.Tk()
    app = EmployeeManagementSystem(root)
    root.mainloop()

功能说明

• 查询：点击“查询员工（Name/ID）”按钮，会弹出一个对话框让你输入姓名或ID，然后在文本框中显示查询结果。
• 添加：在相应的输入框中输入员工信息，点击“添加员工”按钮，会将员工信息添加到数据库中。如果任何输入字段为空或年龄、工资不是数字，则弹出提示对话框。如果存在完全相同的记录，会弹出提示对话框询问用户是否确定要添加。如果用户选择“取消”，则不进行添加操作，并且不会显示成功添加员工的提示框。
• 删除：点击“删除员工”按钮，会弹出一个对话框让你输入要删除的员工ID。
• 统计：点击“统计员工”按钮，会弹出一个消息框显示当前员工总数。
• 退出：通过关闭窗口退出程序。

运行结果

结语

通过以上步骤，你已经成功构建了一个简单但功能齐全的员工管理系统。这个系统不仅具备基本的数据管理功能，还提供了友好的图形用户界面。希望这篇文章能帮助你理解和掌握如何使用Python和Tkinter创建图形界面应用程序，并结合SQLite数据库进行数据管理。如果你有任何问题或建议，请随时留言交流！

希望这篇技术博文能够为你提供有用的信息，并激发起你对编程及项目开发的兴趣！如果有任何进一步的问题或需要更多的功能，请留言。

阿里云百炼大模型

https://bailian.console.aliyun.com/

通义灵码_智能编码助手面向用户上线个人和企业版产品

https://tongyi.aliyun.com/lingma/pricing?userCode=jl9als0w

云工开物_阿里云高校计划助力高校科研与教育加速。

https://university.aliyun.com/mobile?userCode=jl9als0w

无影云电脑个人版简单易用、安全高效的云上桌面服务

https://www.aliyun.com/product/wuying/gws/personal_edition?userCode=jl9als0w

云服务器ECS省钱攻略五种权益,限时发放,不容错过

https://www.aliyun.com/daily-act/ecs/ecs_trial_benefits?userCode=jl9als0w