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

💻 Рабочие примеры

Все примеры подключаются к учебной базе hr. Замените параметры config на свои, если используете локальный сервер.

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

Файл: 01_employees_by_salary.py

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()

Что происходит:

1. Получаем список отделов и показываем его пользователю.
2. Считываем название отдела.
3. Соединяем три таблицы и сортируем результат по salary DESC.
4. Нумеруем строки через enumerate(..., start=1).

Пример 2. Выбор отдела по номеру

Файл: 02_select_by_number.py

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. Поэтому используем departments[selected_index - 1].

Пример 3. Пустой отдел

Файл: 03_empty_department.py

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()

Проверка if not results позволяет корректно сообщить, что в отделе нет сотрудников, вместо пустого вывода.

🚀 Как запустить в VS Code