6 СЕМЕСТР / ЛР / ЛР7 / LR7

import pandas as pd
import psycopg2


# Подключение к базе данных:
connection = psycopg2.connect(database="students"
, user="postgres"
, password="12345678qwerty"
, host="127.0.0.1"
, port="5432")


# курсор для выполнения операций с БД
cursor = connection.cursor()
# вывод свойства соединения
print(connection.get_dsn_parameters(), "\n")
# выполнение запроса к БД
cursor.execute("SELECT version();")
# получение результата запроса
version_ps = cursor.fetchone()
print("Вы подключены к - ", version_ps, "\n")

df = pd.read_sql_query("SELECT * FROM jobs ", connection)
print(df)


# Создание новой таблицы locations в БД sudents
cursor.execute('''CREATE TABLE if not exists locations
(location_id int PRIMARY KEY,
city varchar(30),
postal_code varchar(12)
); ''')

# Заполнение данными тблицы locations
cursor.execute(
'''INSERT INTO locations
VALUES
(1, 'Roma', '00989'),
(2, 'Venice', '10934'),
(3, 'Tokyo', '1689'),
(4, 'Hiroshima', '6823'),
(5, 'Southlake', '26192'),
(6, 'South San Francisco', '99236'),
(7, 'South Brunswick', '50090'),
(8, 'Seattle', '98199'),
(9, 'Toronto', 'M5V 2L7'),
(10, 'Whitehorse', 'YSW 9T2') ON CONFLICT DO NOTHING;'''
)
connection.commit()

# Вывод данных из таблицы locations
table_locations = pd.read_sql_query("SELECT * FROM locations ", connection)
print(table_locations)


cursor.execute(
'''-- Добавление связи теблиц employees и locations по полю location_id
ALTER TABLE IF EXISTS employees ADD COLUMN IF NOT EXISTS location_id INT
constraint empl_loc_fkey references locations(location_id);

-- Заполнение поля location_id таблицы employees случайными значениями из поля location_id таблицы locations
UPDATE employees SET location_id = (
SELECT location_id FROM locations where employees.employee_id = employees.employee_id
ORDER BY RANDOM() LIMIT 1
)
WHERE location_id IS NULL;

-- Делаем поле внешнего ключа обязательным к заполнению
alter table employees alter column location_id set not null;'''
)
connection.commit()


# ЗАПРОС 1
zapros = pd.read_sql_query('''
-- ЗАПРОС Найти профессию, диапазон которой между минимальной и
-- максимальной зарплатой меньше, чем у остальных профессий.
-- с огригатной функцией в подзапросе
select
j.*
, j.max_salary - j.min_salary salary_diff
from jobs j
where j.max_salary - j.min_salary = (
select min(jj.max_salary - jj.min_salary) from jobs jj)
union all
-- с сортировкой
(select
j.*
, j.max_salary - j.min_salary salary_diff
from jobs j
order by salary_diff
limit 1);
''', connection)
print(zapros)

# ЗАПРОС 2
zapros = pd.read_sql_query('''
-- ЗАПРОС Вывести названия профессий(job_title) и среднюю зарплату
-- (в диапазоне от 2000 до 5000) сотрудников этих профессий.
select *
from
(select
j.job_title
, avg(e.salary) avg_salary
from jobs j
join employees e using(job_id)
group by j.job_title) q
where q.avg_salary between 2000 and 5000;
''', connection)
print(zapros)

# ЗАПРОС 3
zapros = pd.read_sql_query('''
select city, EMP, CNT_WORKER, AVG_SALARY
from
((select
'Dpt_managers' EMP
, l.city
, count(distinct d.manager_id) CNT_WORKER
, round(avg(e.salary)) AVG_SALARY
from departments d
join employees e on d.manager_id = e.employee_id
join locations l using(location_id)
group by l.city
order by AVG_SALARY)
union all
(select
'Managers' EMP
, l.city
, count(distinct e.employee_id) CNT_WORKER
, round(avg(e.salary)) AVG_SALARY
from employees e
join locations l using(location_id)
where e.employee_id not in (select distinct d.manager_id from departments d)
and e.employee_id in (select distinct ee.manager_id from employees ee)
group by l.city
order by AVG_SALARY)
union all
(select
'Workers' EMP
, l.city
, count(distinct e.employee_id) CNT_WORKER
, round(avg(e.salary)) AVG_SALARY
from employees e
join locations l using(location_id)
where e.employee_id not in
(select distinct d.manager_id id from departments d where d.manager_id is not null
union
select distinct e.manager_id id from employees e where e.manager_id is not null)
group by l.city
order by AVG_SALARY)) q
order by city, AVG_SALARY;
''', connection)
print(zapros)


cursor.callproc('select_data',[30,]) # вызов функции (название из PostgreSQL)
result = cursor.fetchall() # получение результатов
result_proc = pd.DataFrame(result) # создание датафрейма с результатом
print('\n\nРезультат вызова функции select_data:\n', result_proc)


# создаине функции select_data1
cursor.execute("""
CREATE OR REPLACE FUNCTION select_data1(id_dept int) RETURNS
SETOF departments AS $$
SELECT * FROM departments WHERE departments.department_id >
id_dept;
$$ LANGUAGE SQL;
""") # выполнение запроса
connection.commit() #внесение изменений в БД

cursor.callproc('select_data1',[30,]) # вызов функции (название из PostgreSQL)
result = cursor.fetchall() # получение результатов
result_proc = pd.DataFrame(result) # создание датафрейма с результатом
print('\n\nРезультат вызова функции select_data1:\n', result_proc)


# моя функция
cursor.execute("""
CREATE OR REPLACE FUNCTION my_func(min_salaryy int) RETURNS
table (department_name character
, WORKER_COUNT integer
, AVG_SALARY integer) AS $$
select
d.department_name
, count(*) WORKER_COUNT
, round(avg(e.salary)) AVG_SALARY
from departments d
join employees e using(department_id)
where
e.salary > min_salaryy
group by d.department_name;
$$ LANGUAGE SQL;
""") # выполнение запроса
connection.commit() #внесение изменений в БД

cursor.callproc('my_func',[5000,]) # вызов функции (название из PostgreSQL)
result = cursor.fetchall() # получение результатов
result_proc = pd.DataFrame(result) # создание датафрейма с результатом
print('\n\nРезультат вызова функции my_func:\n', result_proc)


connection.close() # закрытие соединения
cursor.close() # закрытие cursor