办公考勤系统Python案例

以下是一个简单的办公考勤系统Python案例：

1. 创建MySQL数据库，包含两张表：用户表和考勤记录表。

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) UNIQUE NOT NULL,
  password VARCHAR(50) NOT NULL,
  role VARCHAR(10) NOT NULL
);

CREATE TABLE attendance (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  date DATE NOT NULL,
  check_in DATETIME,
  check_out DATETIME,
  status VARCHAR(10)
);

2. 创建Python程序，包含登录、打卡、请假和查询考勤记录等功能。

import mysql.connector
import datetime

# 连接数据库
db = mysql.connector.connect(
  host="localhost",
  user="root",
  password="password",
  database="attendance"
)

# 登录功能
def login():
    username = input("请输入用户名：")
    password = input("请输入密码：")
    cursor = db.cursor()
    sql = "SELECT * FROM users WHERE username = %s AND password = %s"
    values = (username, password)
    cursor.execute(sql, values)
    result = cursor.fetchone()
    if result:
        print("登录成功！")
        if result[3] == 'admin':
            admin_menu()
        else:
            user_menu(result[0])
    else:
        print("用户名或密码错误！")

# 管理员菜单
def admin_menu():
    while True:
        print("请选择操作：")
        print("1. 查看考勤记录")
        print("2. 导出考勤报表")
        print("3. 返回登录界面")
        choice = input()
        if choice == '1':
            view_attendance()
        elif choice == '2':
            export_report()
        elif choice == '3':
            return
        else:
            print("无效操作！")

# 用户菜单
def user_menu(user_id):
    while True:
        print("请选择操作：")
        print("1. 上班打卡")
        print("2. 下班打卡")
        print("3. 请假申请")
        print("4. 返回登录界面")
        choice = input()
        if choice == '1':
            check_in(user_id)
        elif choice == '2':
            check_out(user_id)
        elif choice == '3':
            apply_leave(user_id)
        elif choice == '4':
            return
        else:
            print("无效操作！")

# 上班打卡
def check_in(user_id):
    today = datetime.date.today()
    cursor = db.cursor()
    sql = "SELECT * FROM attendance WHERE user_id = %s AND date = %s"
    values = (user_id, today)
    cursor.execute(sql, values)
    result = cursor.fetchone()
    if result:
        print("您已经打过卡了！")
    else:
        now = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
        sql = "INSERT INTO attendance (user_id, date, check_in, status) VALUES (%s, %s, %s, %s)"
        values = (user_id, today, now, "正常")
        cursor.execute(sql, values)
        db.commit()
        print("打卡成功！")

# 下班打卡
def check_out(user_id):
    today = datetime.date.today()
    cursor = db.cursor()
    sql = "SELECT * FROM attendance WHERE user_id = %s AND date = %s"
    values = (user_id, today)
    cursor.execute(sql, values)
    result = cursor.fetchone()
    if result:
        if result[3]:
            print("您已经打过卡了！")
        else:
            now = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            sql = "UPDATE attendance SET check_out = %s, status = %s WHERE id = %s"
            if now > today.strftime("%Y-%m-%d 18:00:00"):
                status = "加班"
            else:
                status = "正常"
            values = (now, status, result[0])
            cursor.execute(sql, values)
            db.commit()
            print("打卡成功！")
    else:
        print("您还没有上班打卡！")

# 请假申请
def apply_leave(user_id):
    reason = input("请输入请假原因：")
    start_date = input("请输入开始日期（格式：YYYY-MM-DD）：")
    end_date = input("请输入结束日期（格式：YYYY-MM-DD）：")
    cursor = db.cursor()
    sql = "INSERT INTO attendance (user_id, date, status) VALUES (%s, %s, %s)"
    values = (user_id, start_date, "请假")
    cursor.execute(sql, values)
    db.commit()
    print("申请已提交，请等待审核！")

# 查看考勤记录
def view_attendance():
    cursor = db.cursor()
    sql = "SELECT users.username, attendance.date, attendance.check_in, attendance.check_out, attendance.status FROM attendance JOIN users ON attendance.user_id = users.id ORDER BY attendance.date DESC"
    cursor.execute(sql)
    results = cursor.fetchall()
    for result in results:
        print(result)

# 导出考勤报表
def export_report():
    cursor = db.cursor()
    sql = "SELECT users.username, attendance.date, attendance.check_in, attendance.check_out, attendance.status FROM attendance JOIN users ON attendance.user_id = users.id ORDER BY attendance.date DESC"
    cursor.execute(sql)
    results = cursor.fetchall()
    with open("report.csv", "w") as f:
        f.write("用户名,日期,上班打卡时间,下班打卡时间,考勤状态\n")
        for result in results:
            f.write(",".join([str(x) for x in result]) + "\n")
    print("考勤报表已导出！")

# 创建管理员账号和普通用户账号
cursor = db.cursor()
sql = "INSERT INTO users (username, password, role) VALUES (%s, %s, %s)"
values = [("admin", "admin", "admin"), ("user1", "user1", "user"), ("user2", "user2", "user")]
cursor.executemany(sql, values)
db.commit()

# 运行程序
while True:
    print("欢迎使用考勤系统！")
    print("请选择操作：")
    print("1. 登录")
    print("2. 退出")
    choice = input()
    if choice == '1':
        login()
    elif choice == '2':
        break
    else:
        print("无效操作！")

# 关闭数据库连接
db.close()

以上程序仅提供参考，具体实现可根据需求进行调整。