ГУАП
КАФЕДРА № 41
ОТЧЕТ ЗАЩИЩЕН С ОЦЕНКОЙ
ПРЕПОДАВАТЕЛЬ
ассистент |
|
|
|
В.В. Боженко |
|
|
|
|
|
|
|
|
|
|
должность, уч. степень, звание |
|
подпись, дата |
|
инициалы, фамилия |
ОТЧЕТ О ЛАБОРАТОРНОЙ РАБОТЕ №1
Связь СУБД MySQL и Python. Визуализация данных в Python
по курсу: БАЗЫ ДАННЫХ
РАБОТУ ВЫПОЛНИЛ |
|
|
|
|
|
СТУДЕНТ ГР. № |
19.02.2022 |
|
|
||
|
|
|
|
|
|
|
|
|
подпись, дата |
|
инициалы, фамилия |
Санкт-Петербург 2022
Цель работы
Произвести связь базы данных в MySQL и Python, извлечь данные из таблиц базы данных и выполнить анализ данных в БД с помощью визуализации в Python.
Описание предметной области
Описание предметной области в соответствии с индивидуальным вариантом №10.
Музыкальные группы дают концерты, которые проводятся в концертных залах. О музыкальной группе должна храниться следующая информация: название группы, количество альбомов, сайт группы, стиль исполнения, год создания группы. О предстоящем концерте должна присутствовать информация: название концерта, название выступающей группы, дата концерта, место проведения концерта. Билеты на концерт можно заказать через Интернет. При выступлении в концертном зале цена билета зависит от места расположения (фанзона, VIP-места, сидячие). Концертный зал характеризуется номером концертного зала, адресом концертного зала, номером телефона, количеством мест в зале.
Схема данных
Схема данных в соответствии с рисунком 1.
Рисунок 1 – Схема данных
2
Данные таблиц
Данные таблицы «musicalgroup» в соответствии с рисунком 2.
Рисунок 2 – Таблица «musicalgroup»
Данные таблицы «concerthall» в соответствии с рисунком 3.
Рисунок 3 – Таблица «concerthall»
Данные таблицы «location» в соответствии с рисунком 4.
Рисунок 4 – Таблица «location»
Данные таблицы «concert» в соответствии с рисунком 5.
Рисунок 5 – Таблица «concert»
Данные таблицы «contract» в соответствии с рисунком 6.
Рисунок 6 – Таблица «contract»
Данные таблицы «ticket» в соответствии с рисунком 7.
3
Рисунок 7 – Таблица «ticket»
Данные таблицы «orders» в соответствии с рисунком 8.
Рисунок 8 – Таблица «orders»
4
Ход работы
1) Осуществили связь Python с БД в PostgreSQL. Выполнили один запрос на выборку всех данных из таблицы «musicalgroup». Код в соответствии с листингом 1 и результат в соответствии с рисунком 9.
Листинг 1 – Код подключения и запроса на вывод данных таблицы
import psycopg2 as psy import pandas as pd
import matplotlib.pyplot as plt
if __name__ == '__main__':
#Подключение к БД db_connect_kwargs = {
'dbname': 'musicalgroup', 'user': 'postgres', 'password': '12345678qwerty', 'host': 'localhost',
'port': '5432'
}
#Вывод таблицы
try:
connection = psy.connect(**db_connect_kwargs) connection.autocommit = True
cursor = connection.cursor()
table = 'SELECT * FROM musicalgroup' cursor.execute(table)
rows = cursor.fetchall()
df_musicalgroup = pd.DataFrame(rows) print(df_musicalgroup)
except Exception as _ex:
print('[INFO] Error while working with PostgreSQL', _ex)
finally:
if connection: connection.close() cursor.close()
print("[INFO] PostgreSQL connection
closed")
5
Рисунок 9 – Результат подключения к БД и запроса на вывод данных из таблицы
2) Выполнили 3 запроса в скрипте Python с использованием агрегатных функций, сортировки, группировки для вашей БД, результаты которых можно представить в виде графика или гистограммы.
Первый запрос подсчитывает количество билетов проданных на каждый из концертов, отсортировали данные по количеству билетов в порядке убывания. Код запроса в Python в соответствии с листингом 2, результат в соответствии с рисунком 10, график запроса в соответствии с рисунком 11.
Листинг 2 – Код запроса 1
cursor.execute('''SELECT concertname, count(ticketid) FROM ticket
JOIN concert USING(concertid) GROUP BY concertname
ORDER BY 2 DESC''') rows = cursor.fetchall()
df_zap_1 = pd.DataFrame(rows) print(df_zap_1)
Рисунок 10 – Результат запроса 1
6
Рисунок 11 – График запроса 1
Второй запрос подсчитывает количество, сумму и среднюю цену проданных билетов по каждому дню и сортирует данные по убыванию средней цены. Код запроса в Python в соответствии с листингом 2, результат в соответствии с рисунком 12, график запроса в соответствии с рисунком 13.
Листинг 3 – Код запроса 2
cursor.execute('''SELECT OrderDate, count(OrderDate) ticket_count, sum(Price) price_sum, ROUND(avg(Price), 2) avg_price
FROM Orders
JOIN ticket USING(TicketID) JOIN location
USING(LocationID)
GROUP BY OrderDate
ORDER BY avg_price DESC''') rows = cursor.fetchall()
df_zap_2 = pd.DataFrame(rows) print(df_zap_2)
Рисунок 12 – Результат запроса 2
7
Рисунок 13 – График запроса 2
Третий запрос подсчитывает сколько концертов группа провела за заданный промежуток времени. Код запроса в Python в соответствии с листингом 2, результат в соответствии с рисунком 14, график запроса в соответствии с рисунком 15.
Листинг 4 – Код запроса 3
cursor.execute('''SELECT BandName,
count(*) as concert_count
FROM MusicalGroup |
|
JOIN |
Concert |
USING(MusicalGroupID) |
|
JOIN Contract USING(ConcertID) WHERE ConcertDate BETWEEN '2021-10-
15' AND '2021-12-15'
GROUP BY BandName''') rows = cursor.fetchall()
df_zap_3 = pd.DataFrame(rows) print(df_zap_3)
Рисунок 14 – Результат запроса 3
8
Рисунок 15 – График запроса 3
9
Вывод
Выполнив лабораторную работу, мы научились устанавливать соединение с БД с помощью Python, извлекать из нее данные, обрабатывать их c помощью Pandas, проводить анализ и визуализировать с применением библиотек Matplotlib. Полученные в ходе выполнения лабораторной работы знания позволяют работать с данными из БД при помощи сторонних библиотек для Python, что значительно расширяет возможности для анализа больших данных, с которыми приходится сталкиваться при выполнении задач реального мира.
10