МИНИСТЕРСТВО НАУКИ И ВЫСШЕГО ОБРАЗОВАНИЯ РОССИЙСКОЙ ФЕДЕРАЦИИ

федеральное государственное автономное образовательное учреждение высшего образования

«САНКТ-ПЕТЕРБУРГСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ АЭРОКОСМИЧЕСКОГО ПРИБОРОСТРОЕНИЯ»

ИНСТИТУТ НЕПРЕРЫВНОГО И ДИСТАНЦИОННОГО ОБРАЗОВАНИЯ

КАФЕДРА 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

1. Цель работы

Произвести связь базы данных в MySQL и Python, извлечь данные из таблиц базы данных и выполнить анализ данных в БД с помощью визуализации в Python.

2. Вариант задания

   1. Тема работы

Обслуживание рейсов в аэропорту.

2. Описание предметной области

Аэропорт обслуживает рейсы разных авиакомпаний. У каждой авиакомпании есть несколько рейсов. Авиакомпании предоставляют самолеты различного типа (вместимости). Самолеты характеризуются типом, годом выпуска, количеством мест и фирмой–производителем. В один город могут быть несколько рейсов в разное время, осуществляемых различными авиакомпаниями. Самолеты обслуживаются экипажем: командир корабля, второй пилот, штурман, бортинженер и стюардессы. О сотрудниках хранится следующая информация: Ф.И.О., должность, квалификация, экипаж. Рейс имеет свой номер, пункт отправления, пункт прибытия, время вылета, время в пути, тип самолета, название авиакомпании, обслуживающей данный рейс. Билет на самолет имеет свой номер и № рейса, ФИО пассажира, № места, стоимость, дата продажи, дата бронирования. Пассажир, приобретая билет на самолет, сообщает о себе паспортные данные. Клиент может забронировать билет по Интернету и выкупить его за 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
Номер экипажа	Идентификация номера экипажа	Числовой	Нет

3. Схема данных

Схема данных базы данных представлена на рисунке 1.

Рисунок 1 – Схема данных

Скриншоты заполненных таблиц в БД представлены в приложении Б.

3. Ход работы

   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()

2. Код для запроса на выборку и скриншот результата

Пример того, как можно выполнить запрос в 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 – Вывод таблицы результатов выборки

3. Текстовое описание созданных запросов, 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 года был довольно хорошим для аэропорта, но к новому году и сейчас популярность покупки билетов падает. Конечно, данных в таблицах не особо много, но даже с такими результатами можно делать анализ.