教职工管理系统

此处为语雀卡片，点击链接查看

在运行这个程序时，需要安装pyodbc这个第三方库，win+r,输入cmd,enter,pip install pyodbc,下载完成，即可运行

还要把程序中的数据库的密码修改为自己的数据库的密码

用户ID,是数据库自增ID，不可自己设置ID

你输入user,密码：123456

数据库文件

-- 创建数据库
CREATE DATABASE test;

USE test;

-- 创建用户表
CREATE TABLE Users (
    UserID INT IDENTITY(1,1) PRIMARY KEY,
    Username NVARCHAR(50) NOT NULL,
    Password NVARCHAR(50) NOT NULL
);

-- 创建部门表
CREATE TABLE Departments (
  DepartmentID INT IDENTITY(1,1) PRIMARY KEY,
  DepartmentName NVARCHAR(255) NOT NULL,
  Description NVARCHAR(MAX)
);

-- 创建教职工表
CREATE TABLE Teachers (
  TeacherID INT IDENTITY(1,1) PRIMARY KEY,
  Name NVARCHAR(255) NOT NULL,
  Gender VARCHAR(10) CHECK (Gender IN ('男', '女')),
  Age INT,
  DepartmentID INT,
  Position NVARCHAR(255),
  ContactInfo NVARCHAR(255),
  CONSTRAINT FK_Teachers_Departments FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

-- 创建签到记录表
CREATE TABLE SigninRecords (
    SigninID INT PRIMARY KEY IDENTITY(1,1),
    TeacherID INT NOT NULL,
    SigninTime DATETIME NOT NULL,
    IsLate BIT,
    Notes NVARCHAR(255),
    CONSTRAINT FK_SigninRecords_Teachers FOREIGN KEY (TeacherID) REFERENCES Teachers(TeacherID)
);

-- 插入测试用户
INSERT INTO Users (Username, Password) VALUES ('user', '123456');

-- 创建触发器
CREATE TRIGGER trg_DeleteTeacher
ON Teachers
FOR DELETE
AS
BEGIN
    DELETE FROM SigninRecords
    WHERE TeacherID IN (SELECT TeacherID FROM DELETED);
END;

python界面：

import pyodbc
import tkinter as tk
from tkinter import messagebox

# 数据库连接设置
conn = pyodbc.connect(
    'DRIVER={SQL Server};'
    'SERVER=127.0.0.1;'  
    'DATABASE=test;'  
    'UID=sa;' 
    'PWD=******'  # 替换为你的密码
)

cursor = conn.cursor()

# 登录功能
def login():
    username = entry_username.get().strip()
    password = entry_password.get().strip()
    
    if not username or not password:
        messagebox.showerror("错误", "用户名和密码为必填项")
        return
    
    try:
        cursor.execute("SELECT * FROM Users WHERE Username = ? AND Password = ?", username, password)
        user = cursor.fetchone()
        
        if user:
            login_window.destroy()
            main_window()
        else:
            messagebox.showerror("错误", "用户名或密码错误")
    except Exception as e:
        messagebox.showerror("错误", str(e))

# 主窗口功能
def main_window():
    root = tk.Tk()
    root.title("教职工管理系统")
    root.geometry("1050x400")  # 设置窗口大小

    # 教职工信息管理功能
    def add_teacher():
        teacher_id = entry_teacher_id.get().strip()
        name = entry_name.get().strip()
        gender = entry_gender.get().strip()
        if gender not in ['男', '女']:
            messagebox.showerror("错误", "性别必须为 '男' 或 '女'")
            return
        try:
            age = int(entry_age.get().strip())
        except ValueError:
            messagebox.showerror("错误", "年龄必须为整数")
            return
        dept_id = entry_teacher_dept_id.get().strip()
        position = entry_position.get().strip()
        contact = entry_contact.get().strip()

        if not all([name, gender, dept_id, position, contact]):
            messagebox.showerror("错误", "所有字段均为必填")
            return

        try:
            cursor.execute("INSERT INTO Teachers (Name, Gender, Age, DepartmentID, Position, ContactInfo) VALUES (?, ?, ?, ?, ?, ?)",
                        name, gender, age, dept_id, position, contact)
            conn.commit()
            messagebox.showinfo("提示", "教职工信息添加成功")
        except Exception as e:
            messagebox.showerror("错误", str(e))

    def delete_teacher():
        teacher_id = entry_teacher_id.get().strip()
        if not teacher_id:
            messagebox.showerror("错误", "教职工ID为必填项")
            return
        try:
            cursor.execute("DELETE FROM Teachers WHERE TeacherID = ?", teacher_id)
            conn.commit()
            messagebox.showinfo("提示", "教职工信息删除成功")
        except Exception as e:
            messagebox.showerror("错误", str(e))

    def update_teacher():
        teacher_id = entry_teacher_id.get().strip()
        name = entry_name.get().strip()
        gender = entry_gender.get().strip()
        if gender not in ['男', '女']:
            messagebox.showerror("错误", "性别必须为 '男' 或 '女'")
            return
        try:
            age = int(entry_age.get().strip())
        except ValueError:
            messagebox.showerror("错误", "年龄必须为整数")
            return
        dept_id = entry_teacher_dept_id.get().strip()
        position = entry_position.get().strip()
        contact = entry_contact.get().strip()

        if not all([teacher_id, name, gender, dept_id, position, contact]):
            messagebox.showerror("错误", "所有字段均为必填")
            return

        try:
            cursor.execute("""
                UPDATE Teachers 
                SET Name = ?, Gender = ?, Age = ?, DepartmentID = ?, Position = ?, ContactInfo = ?
                WHERE TeacherID = ?
            """, name, gender, age, dept_id, position, contact, teacher_id)
            conn.commit()
            messagebox.showinfo("提示", "教职工信息更新成功")
        except Exception as e:
            messagebox.showerror("错误", str(e))

    def query_teacher():
        teacher_id = entry_teacher_id.get().strip()
        try:
            cursor.execute("SELECT * FROM Teachers WHERE TeacherID = ?", teacher_id)
            row = cursor.fetchone()
            
            if row:
                entry_teacher_id.delete(0, tk.END)
                entry_teacher_id.insert(0, str(row.TeacherID))
                entry_name.delete(0, tk.END)
                entry_name.insert(0, row.Name)
                entry_gender.delete(0, tk.END)
                entry_gender.insert(0, row.Gender)
                entry_age.delete(0, tk.END)
                entry_age.insert(0, str(row.Age))
                entry_teacher_dept_id.delete(0, tk.END)
                entry_teacher_dept_id.insert(0, str(row.DepartmentID))
                entry_position.delete(0, tk.END)
                entry_position.insert(0, row.Position)
                entry_contact.delete(0, tk.END)
                entry_contact.insert(0, row.ContactInfo)
            else:
                messagebox.showinfo("提示", "未找到教职工信息")
        except Exception as e:
            messagebox.showerror("错误", str(e))

    # 部门信息管理功能
    def add_department():
        dept_id = entry_dept_id.get().strip()
        dept_name = entry_dept_name.get().strip()
        description = entry_dept_description.get().strip()
        
        if not all([dept_name]):
            messagebox.showerror("错误", "部门名称为必填")
            return

        try:
            cursor.execute("INSERT INTO Departments (DepartmentName, Description) VALUES (?, ?)", dept_name, description)
            conn.commit()
            messagebox.showinfo("提示", "部门信息添加成功")
        except Exception as e:
            messagebox.showerror("错误", str(e))

    def delete_department():
        dept_id = entry_dept_id.get().strip()
        
        if not dept_id:
            messagebox.showerror("错误", "部门ID为必填项")
            return

        try:
            cursor.execute("DELETE FROM Departments WHERE DepartmentID = ?", dept_id)
            conn.commit()
            messagebox.showinfo("提示", "部门信息删除成功")
        except Exception as e:
            messagebox.showerror("错误", str(e))

    def update_department():
        dept_id = entry_dept_id.get().strip()
        dept_name = entry_dept_name.get().strip()
        description = entry_dept_description.get().strip()
        
        if not all([dept_id, dept_name]):
            messagebox.showerror("错误", "部门ID和名称为必填")
            return

        try:
            cursor.execute("""
                UPDATE Departments 
                SET DepartmentName = ?, Description = ?
                WHERE DepartmentID = ?
            """, dept_name, description, dept_id)
            conn.commit()
            messagebox.showinfo("提示", "部门信息更新成功")
        except Exception as e:
            messagebox.showerror("错误", str(e))

    def query_department():
        dept_id = entry_dept_id.get().strip()
        try:
            cursor.execute("SELECT * FROM Departments WHERE DepartmentID = ?", dept_id)
            row = cursor.fetchone()
            
            if row:
                entry_dept_id.delete(0, tk.END)
                entry_dept_id.insert(0, str(row.DepartmentID))
                entry_dept_name.delete(0, tk.END)
                entry_dept_name.insert(0, row.DepartmentName)
                entry_dept_description.delete(0, tk.END)
                entry_dept_description.insert(0, row.Description)
            else:
                messagebox.showinfo("提示", "未找到部门信息")
        except Exception as e:
            messagebox.showerror("错误", str(e))

    # 签到信息管理功能
    def add_signin():
        signin_id = entry_signin_id.get().strip()
        teacher_id = entry_signin_teacher_id.get().strip()
        signin_time = entry_signin_time.get().strip()
        is_late = entry_signin_is_late.get().strip()
        notes = entry_signin_notes.get().strip()
        
        if not all([teacher_id, signin_time]):
            messagebox.showerror("错误", "教职工ID和签到时间为必填")
            return

        try:
            cursor.execute("INSERT INTO SigninRecords (TeacherID, SigninTime, IsLate, Notes) VALUES (?, ?, ?, ?)",
                        teacher_id, signin_time, is_late, notes)
            conn.commit()
            messagebox.showinfo("提示", "签到信息添加成功")
        except Exception as e:
            messagebox.showerror("错误", str(e))

    def delete_signin():
        signin_id = entry_signin_id.get().strip()
        
        if not signin_id:
            messagebox.showerror("错误", "签到ID为必填项")
            return

        try:
            cursor.execute("DELETE FROM SigninRecords WHERE SigninID = ?", signin_id)
            conn.commit()
            messagebox.showinfo("提示", "签到信息删除成功")
        except Exception as e:
            messagebox.showerror("错误", str(e))

    def update_signin():
        signin_id = entry_signin_id.get().strip()
        teacher_id = entry_signin_teacher_id.get().strip()
        signin_time = entry_signin_time.get().strip()
        is_late = entry_signin_is_late.get().strip()
        notes = entry_signin_notes.get().strip()
        
        if not all([signin_id, teacher_id, signin_time]):
            messagebox.showerror("错误", "签到ID、教职工ID和签到时间为必填")
            return

        try:
            cursor.execute("""
                UPDATE SigninRecords 
                SET TeacherID = ?, SigninTime = ?, IsLate = ?, Notes = ?
                WHERE SigninID = ?
            """, teacher_id, signin_time, is_late, notes, signin_id)
            conn.commit()
            messagebox.showinfo("提示", "签到信息更新成功")
        except Exception as e:
            messagebox.showerror("错误", str(e))

    def query_signin():
        signin_id = entry_signin_id.get().strip()
        try:
            cursor.execute("SELECT * FROM SigninRecords WHERE SigninID = ?", signin_id)
            row = cursor.fetchone()
            
            if row:
                entry_signin_id.delete(0, tk.END)
                entry_signin_id.insert(0, str(row.SigninID))
                entry_signin_teacher_id.delete(0, tk.END)
                entry_signin_teacher_id.insert(0, str(row.TeacherID))
                entry_signin_time.delete(0, tk.END)
                entry_signin_time.insert(0, str(row.SigninTime))
                entry_signin_is_late.delete(0, tk.END)
                entry_signin_is_late.insert(0, row.IsLate)
                entry_signin_notes.delete(0, tk.END)
                entry_signin_notes.insert(0, row.Notes)
            else:
                messagebox.showinfo("提示", "未找到签到信息")
        except Exception as e:
            messagebox.showerror("错误", str(e))

    # 创建教职工管理界面
    frame_teacher = tk.LabelFrame(root, text="教职工管理", padx=10, pady=10)
    frame_teacher.grid(row=0, column=0, padx=10, pady=10)

    tk.Label(frame_teacher, text="教职工ID").grid(row=0, column=0)
    tk.Label(frame_teacher, text="姓名").grid(row=0, column=1)
    tk.Label(frame_teacher, text="性别").grid(row=0, column=2)
    tk.Label(frame_teacher, text="年龄").grid(row=0, column=3)
    tk.Label(frame_teacher, text="部门ID").grid(row=0, column=4)
    tk.Label(frame_teacher, text="职位").grid(row=0, column=5)
    tk.Label(frame_teacher, text="联系方式").grid(row=0, column=6)

    entry_teacher_id = tk.Entry(frame_teacher)
    entry_name = tk.Entry(frame_teacher)
    entry_gender = tk.Entry(frame_teacher)
    entry_age = tk.Entry(frame_teacher)
    entry_teacher_dept_id = tk.Entry(frame_teacher)
    entry_position = tk.Entry(frame_teacher)
    entry_contact = tk.Entry(frame_teacher)

    entry_teacher_id.grid(row=1, column=0)
    entry_name.grid(row=1, column=1)
    entry_gender.grid(row=1, column=2)
    entry_age.grid(row=1, column=3)
    entry_teacher_dept_id.grid(row=1, column=4)
    entry_position.grid(row=1, column=5)
    entry_contact.grid(row=1, column=6)

    tk.Button(frame_teacher, text="添加", command=add_teacher).grid(row=2, column=0)
    tk.Button(frame_teacher, text="删除", command=delete_teacher).grid(row=2, column=1)
    tk.Button(frame_teacher, text="更新", command=update_teacher).grid(row=2, column=2)
    tk.Button(frame_teacher, text="查询", command=query_teacher).grid(row=2, column=3)

    # 创建部门管理界面
    frame_department = tk.LabelFrame(root, text="部门管理", padx=10, pady=10)
    frame_department.grid(row=1, column=0, padx=10, pady=10)

    tk.Label(frame_department, text="部门ID").grid(row=0, column=0)
    tk.Label(frame_department, text="部门名称").grid(row=0, column=1)
    tk.Label(frame_department, text="描述").grid(row=0, column=2)

    entry_dept_id = tk.Entry(frame_department)
    entry_dept_name = tk.Entry(frame_department)
    entry_dept_description = tk.Entry(frame_department)

    entry_dept_id.grid(row=1, column=0)
    entry_dept_name.grid(row=1, column=1)
    entry_dept_description.grid(row=1, column=2)

    tk.Button(frame_department, text="添加", command=add_department).grid(row=2, column=0)
    tk.Button(frame_department, text="删除", command=delete_department).grid(row=2, column=1)
    tk.Button(frame_department, text="更新", command=update_department).grid(row=2, column=2)
    tk.Button(frame_department, text="查询", command=query_department).grid(row=2, column=3)

    # 创建签到管理界面
    frame_signin = tk.LabelFrame(root, text="签到管理", padx=10, pady=10)
    frame_signin.grid(row=2, column=0, padx=10, pady=10)

    tk.Label(frame_signin, text="签到ID").grid(row=0, column=0)
    tk.Label(frame_signin, text="教职工ID").grid(row=0, column=1)
    tk.Label(frame_signin, text="签到时间").grid(row=0, column=2)
    tk.Label(frame_signin, text="是否迟到").grid(row=0, column=3)
    tk.Label(frame_signin, text="备注").grid(row=0, column=4)

    entry_signin_id = tk.Entry(frame_signin)
    entry_signin_teacher_id = tk.Entry(frame_signin)
    entry_signin_time = tk.Entry(frame_signin)
    entry_signin_is_late = tk.Entry(frame_signin)
    entry_signin_notes = tk.Entry(frame_signin)

    entry_signin_id.grid(row=1, column=0)
    entry_signin_teacher_id.grid(row=1, column=1)
    entry_signin_time.grid(row=1, column=2)
    entry_signin_is_late.grid(row=1, column=3)
    entry_signin_notes.grid(row=1, column=4)

    tk.Button(frame_signin, text="添加", command=add_signin).grid(row=2, column=0)
    tk.Button(frame_signin, text="删除", command=delete_signin).grid(row=2, column=1)
    tk.Button(frame_signin, text="更新", command=update_signin).grid(row=2, column=2)
    tk.Button(frame_signin, text="查询", command=query_signin).grid(row=2, column=3)

    root.mainloop()

# 创建登录窗口
login_window = tk.Tk()
login_window.title("登录")
login_window.geometry("300x200")

tk.Label(login_window, text="用户名").grid(row=0, column=0, padx=10, pady=10)
entry_username = tk.Entry(login_window)
entry_username.grid(row=0, column=1, padx=10, pady=10)

tk.Label(login_window, text="密码").grid(row=1, column=0, padx=10, pady=10)
entry_password = tk.Entry(login_window, show="*")
entry_password.grid(row=1, column=1, padx=10, pady=10)

tk.Button(login_window, text="登录", command=login).grid(row=2, column=1, padx=10, pady=10)

login_window.mainloop()