HRMS/sql1.py

import sqlite3
from PySide6.QtWidgets import QApplication, QWidget, QVBoxLayout, QHBoxLayout, QLabel, QPushButton, QLineEdit, QTableWidget, QTableWidgetItem,QMessageBox
class DatabaseManager(QWidget):
    def __init__(self):
        super().__init__()
        self.setWindowTitle("公司人员数据管理系统")

        # 创建数据库
        self.connection = sqlite3.connect('companys.db')
        self.cursor = self.connection.cursor()

        # 建表
        self.cursor.execute('''CREATE TABLE IF NOT EXISTS employees (
                                    id INTEGER PRIMARY KEY AUTOINCREMENT,
                                    name TEXT NOT NULL,
                                    age INTEGER NOT NULL,
                                    gender TEXT NOT NULL,
                                    department TEXT NOT NULL,
                                    position TEXT NOT NULL,
                                    salary INTEGER NOT NULL
                                )''')
        self.connection.commit()

        # 创建界面
        self.create_widgets()

    def create_widgets(self):
        # 姓名输入框
        self.name_line_edit = QLineEdit()
        name_layout = QHBoxLayout()
        name_label = QLabel("姓名：")
        name_layout.addWidget(name_label)
        name_layout.addWidget(self.name_line_edit)

        # 年龄输入框
        self.age_line_edit = QLineEdit()
        age_layout = QHBoxLayout()
        age_label = QLabel("年龄：")
        age_layout.addWidget(age_label)
        age_layout.addWidget(self.age_line_edit)

        # 性别输入框
        self.gender_line_edit = QLineEdit()
        gender_layout = QHBoxLayout()
        gender_label = QLabel("性别：")
        gender_layout.addWidget(gender_label)
        gender_layout.addWidget(self.gender_line_edit)

        # 部门输入框
        self.department_line_edit = QLineEdit()
        department_layout = QHBoxLayout()
        department_label = QLabel("部门：")
        department_layout.addWidget(department_label)
        department_layout.addWidget(self.department_line_edit)

        # 职位输入框
        self.position_line_edit = QLineEdit()
        position_layout = QHBoxLayout()
        position_label = QLabel("职位：")
        position_layout.addWidget(position_label)
        position_layout.addWidget(self.position_line_edit)

        # 薪资输入框
        self.salary_line_edit = QLineEdit()
        salary_layout = QHBoxLayout()
        salary_label = QLabel("薪资：")
        salary_layout.addWidget(salary_label)
        salary_layout.addWidget(self.salary_line_edit)

        # 操作按钮
        self.add_button = QPushButton("添加")
        self.add_button.clicked.connect(self.add_employee)

        self.delete_button = QPushButton("删除")
        self.delete_button.clicked.connect(self.delete_employee)

        self.update_button = QPushButton("修改")
        self.update_button.clicked.connect(self.update_employee)

        # 搜索框和按钮
        self.search_line_edit = QLineEdit()
        search_layout = QHBoxLayout()
        search_label = QLabel("搜索：")
        search_layout.addWidget(search_label)
        search_layout.addWidget(self.search_line_edit)

        self.search_button = QPushButton("搜索")
        self.search_button.clicked.connect(self.search_employee)

        # 表格
        self.table = QTableWidget()
        self.table.setColumnCount(7)
        self.table.setHorizontalHeaderLabels(["ID", "姓名", "年龄", "性别", "部门", "职位", "薪资"])

        # 布局
        input_layout = QVBoxLayout()
        input_layout.addLayout(name_layout)
        input_layout.addLayout(age_layout)
        input_layout.addLayout(gender_layout)
        input_layout.addLayout(department_layout)
        input_layout.addLayout(position_layout)
        input_layout.addLayout(salary_layout)

        button_layout = QHBoxLayout()
        button_layout.addWidget(self.add_button)
        button_layout.addWidget(self.delete_button)
        button_layout.addWidget(self.update_button)

        search_layout.addWidget(self.search_button)
        input_layout.addLayout(search_layout)

        main_layout = QVBoxLayout()
        main_layout.addLayout(input_layout)
        main_layout.addLayout(button_layout)
        main_layout.addWidget(self.table)

        self.setLayout(main_layout)

        # 显示数据
        self.show_all_data()
    #增加员工数据
    def add_employee(self):
        name = self.name_line_edit.text()
        age = self.age_line_edit.text()
        gender = self.gender_line_edit.text()
        department = self.department_line_edit.text()
        position = self.position_line_edit.text()
        salary = self.salary_line_edit.text()
        if name == '' or age == '' or department == '' or position == '' or salary == '':
            QMessageBox.warning(self, '警告', '请完整填写信息！')
        else:
            try:
                self.cursor.execute("INSERT INTO employees (name, age, gender, department, position, salary) VALUES (?, ?, ?, ?, ?, ?)", (name, age, gender, department, position, salary))
                self.connection.commit()
                self.name_line_edit.clear()
                self.age_line_edit.clear()
                self.gender_line_edit.clear()
                self.department_line_edit.clear()
                self.position_line_edit.clear()
                self.salary_line_edit.clear()
                self.show_all_data()
                QMessageBox.information(self, '提示', '添加成功！')
            except:
                QMessageBox.warning(self, '警告', '添加失败！')
    #删除员工数据
    def delete_employee(self):
        row = self.table.currentRow()
        if row == -1:
            QMessageBox.warning(self, '警告', '请选择员工！')
        else:
            try:
                selected_row_indexes = [selected.row() for selected in self.table.selectedIndexes()]
                for row in selected_row_indexes:
                    employee_id = self.table.item(row, 0).text()
                    self.cursor.execute("DELETE FROM employees WHERE id = ?", (employee_id,))
                    self.connection.commit()
                    self.show_all_data()
                    QMessageBox.information(self, '提示', '删除成功！')
            except:
                QMessageBox.warning(self, '警告', '删除失败！')
    #更新员工数据
    def update_employee(self):
        row = self.table.currentRow()
        if row == -1:
            QMessageBox.warning(self, '警告', '请选择员工！')
        else:
            selected_row_indexes = [selected.row() for selected in self.table.selectedIndexes()]

            if len(selected_row_indexes) == 1:
                row = selected_row_indexes[0]

                employee_id = self.table.item(row, 0).text()

                new_name = self.name_line_edit.text()
                new_age = self.age_line_edit.text()
                new_gender = self.gender_line_edit.text()
                new_department = self.department_line_edit.text()
                new_position = self.position_line_edit.text()
                new_salary = self.salary_line_edit.text()
                if new_name=='' or new_age=='' or new_department=='' or new_position=='' or new_salary=='':
                    QMessageBox.warning(self, '警告', '请填写完整信息!')
                else:
                    try:
                        self.cursor.execute("UPDATE employees SET name = ?, age = ?, gender = ?, department = ?, position = ?, salary = ? WHERE id = ?", (new_name, new_age, new_gender, new_department, new_position, new_salary, employee_id))
                        self.connection.commit()
                        self.name_line_edit.clear()
                        self.age_line_edit.clear()
                        self.gender_line_edit.clear()
                        self.department_line_edit.clear()
                        self.position_line_edit.clear()
                        self.salary_line_edit.clear()
                        self.show_all_data()
                        QMessageBox.information(self, '提示', '更新成功！')
                    except:
                        QMessageBox.warning(self, '警告', '更新失败！')


    def show_all_data(self):
        self.table.setRowCount(0)

        self.cursor.execute("SELECT * FROM employees")
        results = self.cursor.fetchall()

        for row_number, row_data in enumerate(results):
            self.table.insertRow(row_number)

            for column_number, data in enumerate(row_data):
                self.table.setItem(row_number, column_number, QTableWidgetItem(str(data)))
    #以姓名搜索员工
    def search_employee(self):
        try:
            search_term = self.search_line_edit.text()
            self.table.setRowCount(0)
            self.cursor.execute(f"SELECT * FROM employees WHERE name LIKE '%{search_term}%'")
            results=self.cursor.fetchall()
            if len(results)==0:
                QMessageBox.warning(self, '警告', '查无此人！')
            else:
                for row_number, row_data in enumerate(results):
                    self.table.insertRow(row_number)

                    for column_number, data in enumerate(row_data):
                        self.table.setItem(row_number, column_number, QTableWidgetItem(str(data)))
                QMessageBox.information(self, '提示', '查找成功！')
        except:
            QMessageBox.warning(self, '警告', '查找失败！')

if __name__ == "__main__":
    app = QApplication([])
    manager = DatabaseManager()
    manager.show()
    app.exec()