- •Ход работы
- •Связь Python с бд в MySql
- •Код для запроса на выборку и скриншот результата
- •Текстовое описание созданных запросов, sql-код, скриншоты полученных графиков и пояснения к ним
- •Заключение
- •Список использованных источников
- •Приложение а Полный листинг кодов
- •Приложение б Скриншоты заполненных таблиц в бд
МИНИСТЕРСТВО НАУКИ И ВЫСШЕГО ОБРАЗОВАНИЯ РОССИЙСКОЙ ФЕДЕРАЦИИ
федеральное государственное автономное образовательное учреждение высшего образования
«САНКТ-ПЕТЕРБУРГСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ АЭРОКОСМИЧЕСКОГО ПРИБОРОСТРОЕНИЯ»
ИНСТИТУТ НЕПРЕРЫВНОГО И ДИСТАНЦИОННОГО ОБРАЗОВАНИЯ
КАФЕДРА 41
|
ОЦЕНКА
ПРЕПОДАВАТЕЛЬ
канд.тех.наук, доцент |
|
|
|
Е. Л. Турнецкая |
должность, уч. степень, звание |
|
подпись, дата |
|
инициалы, фамилия |
ОТЧЕТ О ЛАБОРАТОРНОЙ РАБОТЕ №1
|
СВЯЗЬ СУБД MYSQL И PYTHON. ВИЗУАЛИЗАЦИЯ ДАННЫХ В PYTHON
|
по дисциплине: Базы данных |
РАБОТУ ВЫПОЛНИЛ
СТУДЕНТ ГР. № |
Z9411 |
|
|
|
Р. С. Кафка |
|
номер группы |
|
подпись, дата |
|
инициалы, фамилия |
Студенческий билет № |
2019/3603 |
|
|
|
Санкт-Петербург 2023
СОДЕРЖАНИЕ
1. Цель работы 3
2. Вариант задания 3
2.1. Тема работы 3
2.2. Описание предметной области 3
2.3. Схема данных 6
3. Ход работы 7
3.1. Связь Python с БД в MySQL 7
3.2. Код для запроса на выборку и скриншот результата 7
3.3. Текстовое описание созданных запросов, SQL-код, скриншоты полученных графиков и пояснения к ним 9
ЗАКЛЮЧЕНИЕ 13
СПИСОК ИСПОЛЬЗОВАННЫХ ИСТОЧНИКОВ 14
ПРИЛОЖЕНИЕ А 15
ПРИЛОЖЕНИЕ Б 18
Цель работы
Произвести связь базы данных в MySQL и Python, извлечь данные из таблиц базы данных и выполнить анализ данных в БД с помощью визуализации в Python.
Вариант задания
Тема работы
Обслуживание рейсов в аэропорту.
Описание предметной области
Аэропорт обслуживает рейсы разных авиакомпаний. У каждой авиакомпании есть несколько рейсов. Авиакомпании предоставляют самолеты различного типа (вместимости). Самолеты характеризуются типом, годом выпуска, количеством мест и фирмой–производителем. В один город могут быть несколько рейсов в разное время, осуществляемых различными авиакомпаниями. Самолеты обслуживаются экипажем: командир корабля, второй пилот, штурман, бортинженер и стюардессы. О сотрудниках хранится следующая информация: Ф.И.О., должность, квалификация, экипаж. Рейс имеет свой номер, пункт отправления, пункт прибытия, время вылета, время в пути, тип самолета, название авиакомпании, обслуживающей данный рейс. Билет на самолет имеет свой номер и № рейса, ФИО пассажира, № места, стоимость, дата продажи, дата бронирования. Пассажир, приобретая билет на самолет, сообщает о себе паспортные данные. Клиент может забронировать билет по Интернету и выкупить его за 3 часа до вылета.
В результате проведённого анализа предметной области базы данных «Обслуживание рейсов в аэропорту» легко перечислить основные сущности этой БД. В реляционную модель проектированной БД будут входить следующие таблицы: Авиакомпании, Самолёты, Рейсы, Сотрудники, Билеты, Пассажиры, Экипажи. Список сущностей представлен в таблице №1.
Таблица №1 – Список сущностей
№ |
Название |
Назначение |
1 |
Авиакомпании |
Информация об авиакомпаниях, которые обслуживает аэропорт |
2 |
Самолёты |
Информация о самолётах, предоставляемых авиакомпаниями |
3 |
Сотрудники |
Информация о сотрудниках самолёта |
4 |
Рейсы |
Информация о рейсах |
5 |
Билеты |
Данные о рейсе для пассажиров и аэропорта |
6 |
Пассажиры |
Данные о пассажирах |
7 |
Экипажи |
Данные об экипажах |
Для каждой таблицы (сущности) были приведены описания её атрибутов. Атрибуты – колонки таблицы, которые выражают определённое свойство. Списки атрибутов для сущностей представлены в таблице №2, таблице №3, таблице №4, таблице №5, таблице №6, таблице №7, таблице №8 соответственно.
Таблица №2 – Описание атрибутов сущности Авиакомпании
Название |
Назначение |
Тип данных |
Может быть NaN |
Код авиакомпании |
Идентификатор авиакомпании |
Текстовый |
Нет |
Название авиакомпании |
Название авиакомпании |
Текстовый |
Нет |
Дата основания |
Дата основания авиакомпании |
Дата |
Нет |
Штаб-квартира |
Штаб-квартира авиакомпании |
Текстовый |
Нет |
Таблица №3 – Описание атрибутов сущности Самолёты
Название |
Назначение |
Тип данных |
Может быть NaN |
ID самолёта |
Идентификация самолёта |
Числовой |
Нет |
Тип самолёта |
Тип самолёта |
Текстовый |
Нет |
Год выпуска |
Год выпуска самолёта |
Текстовый |
Нет |
Количество мест |
Количество мест в самолёте для пассажиров |
Числовой |
Нет |
Фирма-производитель |
Фирма-производитель, которая построила самолёт |
Текстовый |
Нет |
Таблица №4 – Описание атрибутов сущности Сотрудники
Название |
Назначение |
Тип данных |
Может быть NaN |
ID сотрудника |
Идентификационный номер сотрудника |
Числовой |
Нет |
ФИО сотрудника |
Ф.И.О. сотрудника аэропорта |
Текстовый |
Нет |
Должность |
Должность сотрудника |
Текстовый |
Нет |
Квалификация |
Квалификация сотрудника |
Текстовый |
Нет |
Номер экипажа |
К какому экипажу принадлежит сотрудник |
Числовой |
Нет |
Таблица №5 – Описание атрибутов сущности Рейсы
Название |
Назначение |
Тип данных |
Может быть NaN |
Номер рейса |
Номер рейса для идентификации |
Числовой |
Нет |
Пункт отправления |
Пункт отправления рейса |
Текстовый |
Нет |
Пункт прибытия |
Пункт прибытия рейса |
Текстовый |
Нет |
Время вылета |
Время вылета самолёта |
Дата |
Нет |
Время в пути |
Время в пути рейса, указано в количестве минут |
Числовой |
Нет |
ID самолёта |
ID самолёта, обслуживающий рейс |
Текстовый |
Нет |
Код авиакомпании |
Код авиакомпании, обслуживающий рейс |
Текстовый |
Нет |
Номер экипажа |
Номер экипажа, который обслуживает самолёт |
Числовой |
Нет |
Таблица №6 – Описание атрибутов сущности Билеты
Название |
Назначение |
Тип данных |
Может быть NaN |
Номер билета |
Номер билета для идентификации |
Числовой |
Нет |
Номер рейса |
Номер рейса для идентификации |
Числовой |
Нет |
ID пассажира |
Идентификация номера пассажира |
Текстовый |
Нет |
Номер места |
Номер рейса на самолёт |
Текстовый |
Нет |
Стоимость |
Стоимость билета |
Числовой |
Нет |
Дата продажи |
Дата продажи билета |
Дата |
Нет |
Дата бронирования |
Дата бронирования билета |
Дата |
Да |
Таблица №7 – Описание атрибутов сущности Пассажиры
Название |
Назначение |
Тип данных |
Может быть NaN |
ID пассажира |
Идентификация номера пассажира |
Числовой |
Нет |
ФИО пассажира |
ФИО пассажира по паспорту |
Текстовый |
Нет |
Пол |
Пол пассажира |
Текстовый |
Нет |
Дата рождения |
Дата рождения пассажира |
Дата |
Нет |
Серия/номер паспорта |
Серия/номер паспорта пассажира |
Текстовый |
Нет |
Гражданство |
Гражданство пассажира |
Текстовый |
Нет |
Таблица №8 – Описание атрибутов сущности Экипажи
Название |
Назначение |
Тип данных |
Может быть NaN |
Номер экипажа |
Идентификация номера экипажа |
Числовой |
Нет |
Схема данных
Схема данных базы данных представлена на рисунке 1.
Рисунок 1 – Схема данных
Скриншоты заполненных таблиц в БД представлены в приложении Б.
Ход работы
Связь Python с бд в MySql
Для подключения к базе данных MySQL из Python можно использовать библиотеку mysql-connector-python. Пример того, как установить соединение с базой данных "airport" представлен в листинге 1.
Листинг 1 – Подключение к базе данных MySQL из Python
-
import mysql.connector
# установка соединения
cnx = mysql.connector.connect(
host='127.0.0.1',
user='root',
password='admin',
database='airport'
)
# создание курсора
cursor = cnx.cursor()
Код для запроса на выборку и скриншот результата
Пример того, как можно выполнить запрос в Python для получения всех данных из таблицы "passengers" и вывода результата в виде таблицы представлен в листинге 2. Результат работы кода представлен на рисунке 2.
Листинг 2 – Запрос в Python на выборку всех данных из таблицы «passangers»
-
import mysql.connector
# Подключение к базе данных
cnx = mysql.connector.connect(
host='127.0.0.1',
user='root',
password='admin',
database='airport'
)
# Создание объекта курсора
cursor = cnx.cursor()
# Выполнить запрос
query = 'SELECT * FROM passengers'
cursor.execute(query)
# Получить все результаты
results = cursor.fetchall()
# Вывод результата в виде таблицы
for row in results:
print(row)
# Закрытие курсора и соединения
cursor.close()
cnx.close()
Рисунок 2 – Вывод таблицы результатов выборки
Текстовое описание созданных запросов, sql-код, скриншоты полученных графиков и пояснения к ним
Коды ниже отображают только выборку запроса и вывод данных. Полный листинг внесен в приложение А.
Был написан код для обработки данных и визуализации городов назначения (des_city) по популярности среди пассажиров, сколько людей отправились в ту или иную страну. Код обработки и визуализации данных популярности туров представлен в листинге 3. График популярности представлен на рисунке 3.
Листинг 3 – Код обработки и визуализации данных популярности городов прилёта среди пассажиров
# execute a query query = ''' SELECT des_city, COUNT(*) as popularity FROM flights GROUP BY des_city ''' cursor.execute(query)
# fetch the results results = cursor.fetchall()
# print the results print(results)
# extract the data from results cities = [r[0] for r in results] popularity = [r[1] for r in results]
# create a bar chart plt.bar(cities, popularity) plt.grid() plt.xlabel('Города прибытия') plt.ylabel('Популярность') plt.show() |
Рисунок 3 – Визуализация городов назначения (des_city) по популярности среди пассажиров
Как видим, графи показывает нам популярность города Душанбе. Возможно, людей привлекают низкие цены на билеты, а возможно город стал популярным из-за каких-то внешних факторов. Билетов в аэропорту немного, но Душанбе явный лидер.
Был написан код для обработки данных и визуализации количества пассажиров мужского и женского пола. Код обработки и визуализации данных представлен в листинге 4. График представлен на рисунке 4.
Листинг 4 – Код обработки и визуализации данных количества пассажиров разного пола
# Execute the query query = ''' SELECT gender, COUNT(*) as count FROM passangers GROUP BY gender ''' cursor.execute(query)
# Fetch the results results = cursor.fetchall()
# Create two lists to hold the values labels = [] sizes = []
# Iterate through the results for result in results: labels.append(result[0]) sizes.append(result[1])
# Plot the pie chart plt.pie(sizes, labels=labels, autopct='%1.1f%%') plt.axis('equal') plt.show() |
Этот код подключается к базе данных "airport" с помощью библиотеки "mysql.connector" и выполняет запрос для подсчета количества людей с полом "м" и "ж". Затем результаты извлекаются и сохраняются в двух списках: "labels" и "sizes". Затем используется библиотека "matplotlib" для создания круговой диаграммы с данными из этих списков, с метками пола на срезах круга и процентом людей с этим полом, отображаемым в центре каждого среза. Наконец, диаграмма отображается с помощью функции "plt.show()".
Рисунок 4 – Диаграмма о количестве пассажиров мужского и женского пола.
Как видим из результатов, преимущественно пассажиры состоят из людей женского пола. Это говорит о том, что в данном аэропорту чаще всего летают женщины.
Был написан код для обработки данных и визуализации данных о количестве проданных билетов. Код подключения к MySQL Workbench с помощью библиотеки mysql-connector-python, запроса необходимых данных о количестве проданных билетов по месяцам, и визуализации их с помощью библиотеки matplotlib представлен в листинге 5.
Листинг 5 – Код обработки и визуализации данных о количестве проданных билетов по месяцам
cursor = cnx.cursor() query = "SELECT DATE_FORMAT(sale_date, '%Y-%m') as month, COUNT(ticket_id) as tickets_sold FROM tikets GROUP BY month ORDER BY month" cursor.execute(query)
# Fetch and store results months = [] tickets_sold = [] for (month, count) in cursor: months.append(month) tickets_sold.append(count)
# Create subplot fig, axs = plt.subplots(1) axs.plot(months, tickets_sold) axs.grid() axs.set_title("Количество проданных билетов в месяц") axs.set_xlabel("Месяц") axs.set_ylabel("Билетов Продано")
# Show plot plt.show() |
Этот код подключится к серверу MySQL, выполнит запрос для получения количества проданных билетов по месяцам, сохранит результаты в двух отдельных списках (months и tickets_sold), а затем использует метод plot() из matplotlib для создания графика данных и отображения ее в виде вложенного графика. Результат представлен на рисунке 5.
Рисунок 5 – Диаграмма о количестве проданных билетов в месяце
Из графика видно, что 10 и 11 месяц 2022 года был довольно хорошим для аэропорта, но к новому году и сейчас популярность покупки билетов падает. Конечно, данных в таблицах не особо много, но даже с такими результатами можно делать анализ.