Вывод
В ходе данной лабораторной работы приобрели навыки визуализации данных из БД на PostgreSQL с помощью Python.
Научились создавать различные графики для результатов запросов,
полученных с помощью Python.
Выполнили запросы различной сложности с применением агрегатных функций, сортировок по различным полям и группировок.
Полученные навыки расширяют возможности по взаимодействию и интеграции БД на PostgreSQL в различные проекты на Python, а так как Python
один из самых популярных языков программирования (в рейтинге TIOBE index на Март 2022 он занимает первое место (https://www.tiobe.com/tiobeindex/)) сфера применения полученных знаний огромна.
11
Список использованных источников
1)Лабораторная работа №8 «Визуализация данных из СУБД PostgreSQL в Python»
2)Официальный сайт PostgreSQL URL: https://www.enterprisedb.com/downloads/postgres-postgresql-downloads
3)Официальный сайт DBeaver URL: https://dbeaver.io/download
12
Приложение А. Код программы.
import pandas as pd import psycopg2
import matplotlib.pyplot as plt import numpy as np
# Подключение к базе данных:
connection = psycopg2.connect(database="students"
,user="postgres"
,password="12345678qwerty"
,host="127.0.0.1"
,port="5432")
cursor = connection.cursor()
# ЗАПРОС Тестовый
df = pd.read_sql_query("SELECT * FROM jobs ", connection) print(df)
# ГРАФИК Тестового Запроса plt.figure()
plt.tick_params(axis='x', rotation=90) # поворот подписи по оси х на 90 градусов
plt.bar(df['job_id'], df['min_salary']) # гистограмма фильмов по продолжительности
plt.tight_layout()
plt.show() # вывод гистограммы
# ЗАПРОС Первый
df1 = pd.read_sql_query('''
select d.department_name, count(*) from departments d
join employees e using(department_id) group by d.department_name
order by 2;''', connection)
13
print(df1)
# ГРАФИК Первого Запроса fig, ax = plt.subplots()
plt.title('Кол-во сотрудников на отделах') ax.set_xlabel('Название отдела') ax.set_ylabel('Кол-во сотрудников') plt.tick_params(axis='x',rotation=60) ax.grid()
plt.bar(df1['department_name'], df1['count']) plt.tight_layout()
plt.show()
# ЗАПРОС Второй
df2 = pd.read_sql_query(''' select
case
when j.job_title like '%Manager%' then 'Manager' when j.job_title like '%Clerk%' then 'Clerk'
when j.job_title like '%President%' then 'President' else 'Other'
end job
,count(*)
,round(avg(e.salary), 2) avg_salary from employees e
join jobs j using(job_id) group by job
order by avg_salary;''', connection)
print(df2)
# ГРАФИК Второго Запроса fig, ax = plt.subplots()
plt.title('Сотрудники по группам названий работ') ax.set_xlabel('Название группы') ax.set_ylabel('Средняя зарплата') plt.tick_params(axis='x',rotation=60)
14
ax.grid()
plt.bar(df2['job'], df2['avg_salary']) plt.tight_layout()
plt.show()
# МОДИФИКАЦИЯ ГРАФИКА Первого Запроса fig, ax = plt.subplots()
plt.title('Кол-во сотрудников на отделах') ax.set_xlabel('Кол-во сотрудников') ax.set_ylabel('Название отдела')
ax.grid()
plt.barh(df1['department_name'], df1['count'], color=np.random.rand(len(df1.index),3)) plt.tight_layout()
plt.show()
# МОДИФИКАЦИЯ ГРАФИКА Второго Запроса fig, ax = plt.subplots()
plt.title('Сотрудники по группам названий работ') ax.set_xlabel('Средняя зарплата') ax.set_ylabel('Название группы')
ax.grid()
plt.barh(df2['job'], df2['avg_salary'], color=np.random.rand(len(df2.index),3)) plt.tight_layout()
plt.show()
# СОЗДАНИЕ ФУНКЦИЙ cursor.execute("""
-- ФУНКЦИЯ fn_worker_in_cnt_range: Количество сотрудников в заданном диапозоне по отделам
create or replace function fn_worker_in_cnt_range(count_from integer, count_to integer)
returns table (department_name varchar, worker_count
15
integer) as $$
select d.department_name , count(*)
from departments d
join employees e using(department_id) group by d.department_name
having count(*) between count_from and count_to order by 2;
$$ language sql;
-- ФУНКЦИЯ fn_worker_in_salary_range: Количество сотрудников по группам должностей со средней зарплатой в заданном диапозоне
create or replace function fn_worker_in_salary_range(salary_from integer, salary_to integer)
returns table (job varchar, worker_count integer, avg_salary numeric(2))
as $$ select
case
when j.job_title like '%Manager%' then 'Manager' when j.job_title like '%Clerk%' then 'Clerk' when j.job_title like '%President%' then
'President'
else 'Other' end job
,count(*)
,round(avg(e.salary), 2) avg_salary from employees e
join jobs j using(job_id) group by job
having round(avg(e.salary), 2) between salary_from and
salary_to
order by avg_salary;
16
$$ language sql;
""")
connection.commit() # внесение изменений в БД
# ВЫЗОВ ФУНКЦИЙ
df_fn1 = pd.read_sql_query('''
-- ВЫЗОВ ФУНКЦИИ fn_worker_in_cnt_range select * from fn_worker_in_cnt_range(5, 10);
''', connection) print(df_fn1)
df_fn2 = pd.read_sql_query('''
-- ВЫЗОВ ФУНКЦИИ fn_worker_in_salary_range
select * from fn_worker_in_salary_range(10000, 20000);
''', connection) print(df_fn2)
#МОДИФИЦИРОВАННЫЙ ГРАФИК для результата
fn_worker_in_cnt_range() fig, ax = plt.subplots()
plt.title('Кол-во сотрудников на отделах') ax.set_xlabel('Кол-во сотрудников') ax.set_ylabel('Название отдела')
ax.grid()
plt.barh(df_fn1['department_name'], df_fn1['worker_count'], color=np.random.rand(len(df_fn1.index),3)) plt.tight_layout()
plt.show()
# МОДИФИЦИРОВАННЫЙ ГРАФИК для результата fn_worker_in_salary_range()
fig, ax = plt.subplots()
plt.title('Сотрудники по группам названий работ') ax.set_xlabel('Средняя зарплата') ax.set_ylabel('Название группы')
ax.grid()
plt.barh(df_fn2['job'], df_fn2['avg_salary'],
17
color=np.random.rand(len(df_fn2.index),3)) plt.tight_layout()
plt.show()
# ЗАПРОС
df_last = pd.read_sql_query('''
-- ЗАПРОС средняя зарплата сотрудников по городам select l.city, round(avg(e.salary), 2) avg_salary
from locations l
join employees e using(location_id) group by l.location_id;
''', connection) print(df_last)
#ГРАФИК Запроса
fig, ax = plt.subplots()
plt.title('Cредняя зарплата сотрудников по городам') ax.set_xlabel('Размер заработной платы') ax.set_ylabel('Название города')
ax.grid()
plt.barh(df_last['city'], df_last['avg_salary'], color=np.random.rand(len(df_last.index),3)) plt.tight_layout()
plt.show()
connection.close() # закрытие соединения cursor.close() # закрытие cursor
18