"""
Урок 80. MySQL и Python — весь код примеров одним файлом.

Источник: subjects/python-fundamentals/course/lessons/80-mysql/examples.html
Файл собран автоматически (tools/build_lesson_examples.py): правьте страницу урока.
Запуск: python lesson-80.py
"""



# ====================================================================
# 💻 Рабочие примеры
# ====================================================================
import pymysql

config = {
    "host": "localhost",
    "user": "root",
    "password": "yourpassword",
    "database": "hr",
    "charset": "utf8mb4",
}

connection = pymysql.connect(**config)

if connection.open:
    print("Соединение установлено!")

connection.close()


# ====================================================================
# 💻 Рабочие примеры
# ====================================================================
import pymysql

config = {
    "host": "localhost",
    "user": "root",
    "password": "yourpassword",
    "database": "hr",
    "charset": "utf8mb4",
}

connection = pymysql.connect(**config)
try:
    cursor = connection.cursor()
    cursor.execute("SELECT department_id, department_name FROM departments")
    for row in cursor:
        print(row)
finally:
    cursor.close()
    connection.close()


# ====================================================================
# 💻 Рабочие примеры
# ====================================================================
import pymysql

config = {
    "host": "localhost",
    "user": "root",
    "password": "yourpassword",
    "database": "hr",
    "charset": "utf8mb4",
}

department_id = 50  # в реальной программе значение приходит извне

connection = pymysql.connect(**config)
try:
    cursor = connection.cursor()
    cursor.execute(
        "SELECT first_name, last_name FROM employees WHERE department_id = %s",
        (department_id,)  # не забудьте запятую!
    )
    for first_name, last_name in cursor:
        print(f"{first_name} {last_name}")
finally:
    cursor.close()
    connection.close()


# ====================================================================
# 💻 Рабочие примеры
# ====================================================================
import pymysql

config = {
    "host": "localhost",
    "user": "root",
    "password": "yourpassword",
    "database": "hr",
    "charset": "utf8mb4",
}

params = {
    "min_salary": 10000,
    "department_id": 60,
}

connection = pymysql.connect(**config)
try:
    cursor = connection.cursor()
    cursor.execute(
        """SELECT first_name, last_name
           FROM employees
           WHERE department_id = %(department_id)s
              OR salary > %(min_salary)s""",
        params
    )
    for row in cursor:
        print(row)
finally:
    cursor.close()
    connection.close()


# ====================================================================
# 💻 Рабочие примеры
# ====================================================================
import pymysql

config = {
    "host": "localhost",
    "user": "root",
    "password": "yourpassword",
    "database": "hr",
    "charset": "utf8mb4",
}

connection = pymysql.connect(**config)
try:
    cursor = connection.cursor()
    cursor.execute(
        "INSERT INTO departments (department_name, location_id) VALUES (%s, %s)",
        ("Data Science", 1700)
    )
    connection.commit()
    print("Добавлено строк:", cursor.rowcount)
finally:
    cursor.close()
    connection.close()


# ====================================================================
# 💻 Рабочие примеры
# ====================================================================
import pymysql

config = {
    "host": "localhost",
    "user": "root",
    "password": "yourpassword",
    "database": "hr",
    "charset": "utf8mb4",
}

user_input = input("Введите ID отдела: ")

connection = pymysql.connect(**config)
try:
    cursor = connection.cursor()
    cursor.execute(
        "SELECT first_name, last_name FROM employees WHERE department_id = %s",
        (user_input,)
    )
    for row in cursor:
        print(row)
finally:
    cursor.close()
    connection.close()


# ====================================================================
# 💻 Рабочие примеры
# ====================================================================
import pymysql

config = {
    "host": "localhost",
    "user": "root",
    "password": "wrong_password",
    "database": "hr",
    "charset": "utf8mb4",
}

try:
    connection = pymysql.connect(**config)
    print("Соединение установлено")
except pymysql.OperationalError as e:
    print("Ошибка подключения:", e)
except pymysql.MySQLError as e:
    print("Общая ошибка MySQL:", e)


# ====================================================================
# 💻 Рабочие примеры
# ====================================================================
import pymysql

config = {
    "host": "localhost",
    "user": "root",
    "password": "yourpassword",
    "database": "hr",
    "charset": "utf8mb4",
}

try:
    with pymysql.connect(**config) as connection:
        with connection.cursor() as cursor:
            cursor.execute("SELECT department_id, department_name FROM departments")
            departments = cursor.fetchall()
            for department_id, department_name in departments:
                print(f"{department_id}: {department_name}")
except pymysql.MySQLError as e:
    print("Ошибка работы с базой данных:", e)