Урок 81. Практикум 20: Данные о сотрудниках. Часть 2

Решение задания 1. Список сотрудников по убыванию зарплаты

import pymysql


def print_employees(rows):
    for i, (first, last, title, salary) in enumerate(rows, start=1):
        print(f"{i}. {first} {last} — {title} — {salary}")


def main():
    config = {
        "host": "ich-db.edu.itcareerhub.de",
        "user": "ich1",
        "password": "password",
        "database": "hr",
        "charset": "utf8mb4",
    }

    with pymysql.connect(**config) as connection:
        with connection.cursor() as cursor:
            cursor.execute("SELECT department_name FROM departments")
            departments = [row[0] for row in cursor]
            for i, name in enumerate(departments, start=1):
                print(f"{i}. {name}")

            selected_department = input("Enter department: ")

            cursor.execute("""
                SELECT first_name, last_name, job_title, salary
                FROM employees
                JOIN departments ON employees.department_id = departments.department_id
                JOIN jobs ON employees.job_id = jobs.job_id
                WHERE departments.department_name = %s
                ORDER BY salary DESC
            """, (selected_department,))

            results = cursor.fetchall()
            print_employees(results)


if __name__ == "__main__":
    main()

Логика решения: сначала выводим список отделов, затем считываем название и выполняем параметризованный запрос с сортировкой salary DESC. Функция print_employees отвечает за форматированный вывод.

Решение задания 2. Выбор отдела по номеру

import pymysql


def print_employees(rows):
    for i, (first, last, title, salary) in enumerate(rows, start=1):
        print(f"{i}. {first} {last} — {title} — {salary}")


def main():
    config = {
        "host": "ich-db.edu.itcareerhub.de",
        "user": "ich1",
        "password": "password",
        "database": "hr",
        "charset": "utf8mb4",
    }

    with pymysql.connect(**config) as connection:
        with connection.cursor() as cursor:
            cursor.execute("SELECT department_name FROM departments")
            departments = [row[0] for row in cursor]
            for i, name in enumerate(departments, start=1):
                print(f"{i}. {name}")

            selected_index = int(input("Enter department number: "))
            selected_department = departments[selected_index - 1]
            print("You choose:", selected_department)

            cursor.execute("""
                SELECT first_name, last_name, job_title, salary
                FROM employees
                JOIN departments ON employees.department_id = departments.department_id
                JOIN jobs ON employees.job_id = jobs.job_id
                WHERE departments.department_name = %s
                ORDER BY salary DESC
            """, (selected_department,))

            results = cursor.fetchall()
            print_employees(results)


if __name__ == "__main__":
    main()

Логика решения: номер отдела преобразуем в индекс списка вычитанием единицы. Это самое частое место для ошибки: пользователь видит нумерацию с 1, Python хранит элементы с 0.

Решение задания 3. Пустой отдел

import pymysql


def print_employees(rows):
    for i, (first, last, title, salary) in enumerate(rows, start=1):
        print(f"{i}. {first} {last} — {title} — {salary}")


def main():
    config = {
        "host": "ich-db.edu.itcareerhub.de",
        "user": "ich1",
        "password": "password",
        "database": "hr",
        "charset": "utf8mb4",
    }

    with pymysql.connect(**config) as connection:
        with connection.cursor() as cursor:
            cursor.execute("SELECT department_name FROM departments")
            departments = [row[0] for row in cursor]
            for i, name in enumerate(departments, start=1):
                print(f"{i}. {name}")

            selected_index = int(input("Enter department number: "))
            selected_department = departments[selected_index - 1]
            print(f"You selected: {selected_department}")

            cursor.execute("""
                SELECT first_name, last_name, job_title, salary
                FROM employees
                JOIN departments ON employees.department_id = departments.department_id
                JOIN jobs ON employees.job_id = jobs.job_id
                WHERE departments.department_name = %s
                ORDER BY salary DESC
            """, (selected_department,))

            results = cursor.fetchall()

            if not results:
                print(f"No employees found in {selected_department} department.")
            else:
                print_employees(results)


if __name__ == "__main__":
    main()

Логика решения: после fetchall() проверяем, пуст ли список results. Если пуст — выводим понятное сообщение с названием отдела.

Решение задания 4. Фильтрация по зарплате

import pymysql

VALID_OPERATORS = {'>', '<', '=', '>=', '<='}


def print_employees(rows):
    for i, (first, last, title, salary) in enumerate(rows, start=1):
        print(f"{i}. {first} {last} — {title} — {salary}")


def main():
    config = {
        "host": "ich-db.edu.itcareerhub.de",
        "user": "ich1",
        "password": "password",
        "database": "hr",
        "charset": "utf8mb4",
    }

    with pymysql.connect(**config) as connection:
        with connection.cursor() as cursor:
            cursor.execute("SELECT department_name FROM departments")
            departments = [row[0] for row in cursor]
            for i, name in enumerate(departments, start=1):
                print(f"{i}. {name}")

            selected_index = int(input("Enter department number: "))
            selected_department = departments[selected_index - 1]
            print(f"You selected: {selected_department}")

            cursor.execute("""
                SELECT first_name, last_name, job_title, salary
                FROM employees
                JOIN departments ON employees.department_id = departments.department_id
                JOIN jobs ON employees.job_id = jobs.job_id
                WHERE departments.department_name = %s
            """, (selected_department,))

            results = cursor.fetchall()

            if not results:
                print("No employees found in this department.")
                return

            filter_choice = input("Would you like to filter employees by salary? (y/n) ").strip().lower()
            if filter_choice == 'y':
                condition = input("Enter condition (>, <, =, >=, <=): ").strip()
                if condition not in VALID_OPERATORS:
                    print("Invalid condition.")
                    return

                try:
                    value = float(input("Enter salary: "))
                except ValueError:
                    print("Invalid salary input.")
                    return

                query = f"""
                    SELECT first_name, last_name, job_title, salary
                    FROM employees
                    JOIN departments ON employees.department_id = departments.department_id
                    JOIN jobs ON employees.job_id = jobs.job_id
                    WHERE departments.department_name = %s AND salary {condition} %s
                    ORDER BY salary DESC
                """
                cursor.execute(query, (selected_department, value))
                filtered_results = cursor.fetchall()

                if not filtered_results:
                    print("No employees match the given condition.")
                else:
                    print_employees(filtered_results)
            else:
                print_employees(results)


if __name__ == "__main__":
    main()

Логика решения: оператор сравнения проверяется по множеству VALID_OPERATORS. Если оператор допустим, значение зарплаты всё равно передаётся через %s. Это сохраняет гибкость и безопасность.

Решение задания 5. Повторный ввод при ошибке

import pymysql

VALID_OPERATORS = {'>', '<', '=', '>=', '<='}


def print_employees(rows):
    for i, (first, last, title, salary) in enumerate(rows, start=1):
        print(f"{i}. {first} {last} — {title} — {salary}")


def read_department_number(departments):
    while True:
        try:
            selected_index = int(input("Enter department number: "))
            if 1 <= selected_index <= len(departments):
                return selected_index
            print("Invalid department number. Please try again.")
        except ValueError:
            print("Invalid input. Please enter a number.")


def filter_by_salary(cursor, selected_department):
    condition = input("Enter condition (>, <, =, >=, <=): ").strip()
    if condition not in VALID_OPERATORS:
        print("Invalid condition.")
        return

    try:
        value = float(input("Enter salary: "))
    except ValueError:
        print("Invalid salary input.")
        return

    query = f"""
        SELECT first_name, last_name, job_title, salary
        FROM employees
        JOIN departments ON employees.department_id = departments.department_id
        JOIN jobs ON employees.job_id = jobs.job_id
        WHERE departments.department_name = %s AND salary {condition} %s
        ORDER BY salary DESC
    """
    cursor.execute(query, (selected_department, value))
    return cursor.fetchall()


def main():
    config = {
        "host": "ich-db.edu.itcareerhub.de",
        "user": "ich1",
        "password": "password",
        "database": "hr",
        "charset": "utf8mb4",
    }

    with pymysql.connect(**config) as connection:
        with connection.cursor() as cursor:
            cursor.execute("SELECT department_name FROM departments")
            departments = [row[0] for row in cursor]
            for i, name in enumerate(departments, start=1):
                print(f"{i}. {name}")

            selected_index = read_department_number(departments)
            selected_department = departments[selected_index - 1]
            print(f"You selected: {selected_department}")

            cursor.execute("""
                SELECT first_name, last_name, job_title, salary
                FROM employees
                JOIN departments ON employees.department_id = departments.department_id
                JOIN jobs ON employees.job_id = jobs.job_id
                WHERE departments.department_name = %s
            """, (selected_department,))

            results = cursor.fetchall()

            if not results:
                print("No employees found in this department.")
                return

            filter_choice = input("Would you like to filter employees by salary? (y/n) ").strip().lower()
            if filter_choice == 'y':
                filtered_results = filter_by_salary(cursor, selected_department)
                if filtered_results is None:
                    return
                if not filtered_results:
                    print("No employees match the given condition.")
                else:
                    print_employees(filtered_results)
            else:
                print_employees(results)


if __name__ == "__main__":
    main()

Логика решения: ввод номера вынесен в функцию read_department_number, которая в бесконечном цикле запрашивает число и проверяет диапазон. При успехе возвращает корректный индекс. Так код главной функции остаётся коротким.